查找不存在联接的记录 [英] Find records where join doesn't exist

问题描述

我可以根据用户是否对其进行投票来限制所有questions.在模型中:

I have a scope to limit all questions by whether or not a user has voted on them. In the model:

scope :answered_by, lambda {|u| joins(:votes).where("votes.user_id = ?", u.id) }
scope :unanswered_by, lambda {|u| joins(:votes).where("votes.user_id != ?", u.id) }

在控制器中，我这样称呼他们:

In the controller, I call them like this:

@answered = Question.answered_by(current_user)
@unanswered = Question.unanswered_by(current_user)

unanswered_by范围不正确.我本质上是想找到没有表决的地方.相反，它正在尝试查找是否存在不等于当前用户的投票.有什么想法如何返回不存在联接的所有记录吗?

The unanswered_by scope is incorrect. I essentially want to find where there is no vote. Instead, it is trying to look for if there is a vote that doesn't equal the current user. Any ideas how to return all records where a join doesn't exist?

推荐答案

使用 EXISTS 表达式:

WHERE NOT EXISTS (
   SELECT FROM votes v  -- SELECT list can be empty
   WHERE  v.some_id = base_table.some_id
   AND    v.user_id = ?
   )

差异

在NOT EXISTS()(Ⓔ)和NOT IN()(Ⓘ)之间的

...是双重的:

The difference

... between NOT EXISTS() (Ⓔ) and NOT IN() (Ⓘ) is twofold:

1. 性能

Ⓔ通常更快.一旦找到第一个匹配项，它将停止处理子查询. 手册:

Ⓔ is generally faster. It stops processing the subquery as soon as the first match is found. The manual:

子查询通常只会执行足够长的时间以确定 是否至少返回一行，而不是一直返回.

The subquery will generally only be executed long enough to determine whether at least one row is returned, not all the way to completion.

可以通过查询计划程序优化

Ⓘ，但程度要小一些，因为NULL处理会使其更复杂.

Ⓘ can also be optimized by the query planner, but to a lesser extent since NULL handling makes it more complex.

正确性

如果子查询表达式中的结果值之一为NULL，则Ⓘ的结果为NULL，而通用逻辑将期望TRUE-且Ⓔ将返回TRUE. 手册:

If one of the resulting values in the subquery expression is NULL, the result of Ⓘ is NULL, while common logic would expect TRUE - and Ⓔ will return TRUE. The manual:

如果每行的所有结果都不相等或为null，且至少 一个null，则NOT IN的结果为null.

If all the per-row results are either unequal or null, with at least one null, then the result of NOT IN is null.

基本上，(NOT) EXISTS在大多数情况下是更好的选择.

Essentially, (NOT) EXISTS is the better choice in most cases.

您的查询如下所示:

SELECT *
FROM   questions q
WHERE  NOT EXISTS (
    SELECT FROM votes v 
    WHERE  v.question_id = q.id
    AND    v.user_id = ?
    );

不要不加入基本查询中的votes.这将使工作无效.

Do not join to votes in the base query. That would void the effort.

除了NOT EXISTS和NOT IN之外，还有LEFT JOIN / IS NULL和EXCEPT的其他语法选项.参见:

Besides NOT EXISTS and NOT IN there are additional syntax options with LEFT JOIN / IS NULL and EXCEPT. See:

• 选择在其他表格

这篇关于查找不存在联接的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！