postgresql 查找 join 不存在的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14251180/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find records where join doesn't exist
提问by Marc
I have a scope to limit all questionsby whether or not a user has voted on them. In the model:
我有一个范围来限制所有questions用户是否对它们进行了投票。在模型中：
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)
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?
unanswered_by 范围不正确。我基本上想找到没有投票的地方。相反，它试图寻找是否存在不等于当前用户的投票。任何想法如何返回不存在连接的所有记录？
回答by Erwin Brandstetter
Use an EXISTSexpression:
使用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 = ?
)
The difference
区别
... between NOT EXISTS()(?) and NOT IN()(?) is twofold:
... NOT EXISTS()(?) 和NOT IN()(?) 之间是双重的：
Performance
? 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.
? can also be optimized by the query planner, but to a lesser extent since
NULLhandling makes it more complex.Correctness
If one of the resulting values in the subquery expression is
NULL, the result of ? isNULL, while common logic would expectTRUE- and ? will returnTRUE. The manual:If all the per-row results are either unequal or null, with at least one null, then the result of
NOT INis null.
表现
? 一般更快。一旦找到第一个匹配项，它就会停止处理子查询。手册：
子查询通常只会执行足够长的时间来确定是否至少返回一行，而不是一直执行到完成。
? 也可以由查询规划器优化，但程度较小，因为
NULL处理使其更加复杂。正确性
如果子查询表达式中的结果值之一是
NULL， ? 的结果。是NULL，而常见的逻辑会期望TRUE- 和？会回来TRUE。手册：如果每行的所有结果不相等或为空，且至少有一个为空，则结果
NOT IN为空。
Essentially, (NOT) EXISTSis the better choice in most cases.
本质上，(NOT) EXISTS在大多数情况下是更好的选择。
Example
例子
Your query can look like this:
您的查询可能如下所示：
SELECT *
FROM questions q
WHERE NOT EXISTS (
SELECT FROM votes v
WHERE v.question_id = q.id
AND v.user_id = ?
);
Do notjoin to votesin the base query. That would void the effort.
千万不能加入到votes在基本查询。那将使努力无效。
Besides NOT EXISTSand NOT INthere are additional syntax options with LEFT JOIN / IS NULLand EXCEPT. See:
除了NOT EXISTS和NOT IN有额外的语法选项LEFT JOIN / IS NULL和EXCEPT。看：
回答by shweta
try this and let me know if it works
试试这个，让我知道它是否有效
EDIT-1
编辑-1
scope :unanswered_questions, lambda { joins('LEFT OUTER JOIN votes ON questions.id = votes.question_id').where('votes.question_id IS NULL') }
EDIT-2
编辑-2
scope :unanswered_by, lambda {|u| where("questions.id NOT IN (SELECT votes.question_id from votes where votes.user_id = ?)",u.id) }
回答by EugZol
And if you want to do EXISTSquery in elegant and Rails-ish manner, you can use Where Existsgem I've written:
如果你想以EXISTS优雅和 Rails 风格的方式进行查询，你可以使用我写的Where Existsgem：
Question.where_not_exists(:votes, user_id: current_user.id)
Of course, you can made scope of it as well:
当然，您也可以将其设为范围：
scope :unanswered_by, ->(user){ where_not_exists(:votes, user_id: user.id) }
