查找不存在连接的记录

Question

我有一个作用域来限制所有 问题 是否由用户投票决定。在模型中：

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) }

在控制器中，我这样调用它们：

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

unanswered_by 范围不正确。我实际上想找到没有投票的地方。相反，它正在尝试查找是否存在一个不等于当前用户的投票。有什么想法可以返回所有不存在联接的记录吗？

- Marc

Answer 1

使用 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() (Ⓘ) 的区别有两个：

1. 性能

   Ⓔ 通常更快。它在找到第一个匹配项后就停止处理子查询。 手册：

   子查询通常只会执行足够长的时间来确定是否返回了至少一行，而不是全部执行完毕。

   Ⓘ 也可以通过查询规划器进行优化，但由于需要处理 NULL，因此优化程度较低。

2. 正确性

   如果子查询表达式中的任何结果值是 NULL，则 Ⓘ 的结果为 NULL，而通常逻辑预期为 TRUE - 而 Ⓔ 将返回 TRUE。 手册：

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

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

示例

您的查询可以像这样：

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

在基本查询中不要加入到votes中。那样会使努力无效。

除了NOT EXISTS和NOT IN之外，还有一些其他的语法选项，如LEFT JOIN / IS NULL和EXCEPT。请参见：

• 选择不在另一张表中出现的行

- Erwin Brandstetter

---

不确定你的解决方案和 Shweta 的解决方案是否有区别，但也可以工作。 - Marc

@Marc：我在我的回答中添加了一个应该可以工作的查询版本。我还澄清了原始代码示例，以反映您想要在基本表中每行得到一个答案。 - Erwin Brandstetter

太好了！NOT IN比起对应的NOT EXISTS永远不会更正确，也不会更快。本质上，NOT IN是有害的。（我只是不喜欢这个“反模式”一词） - wildplasser

但是，@wildplasser船长！有些情况下您确实需要NULL。只不过这种情况很少见。 - Erwin Brandstetter

还有一个额外的性能问题：NOT IN必须删除重复项（即排序），包括可怕的NULLS。（聪明的规划者（==简单计划）可能知道何时不进行排序，但是）我仍然需要找到实际需要IN的情况。 NOT IN甚至更糟...（顺便说一句：我认为我的数独求解器中有几个NOT IN。是时候重写了...） - wildplasser

Answer 2

如果你想以优雅而符合Rails方式的方式执行EXISTS查询，可以使用我编写的Where Exists gem：

Question.where_not_exists(:votes, user_id: current_user.id)

当然，你也可以制定它的范围：

scope :unanswered_by, ->(user){ where_not_exists(:votes, user_id: user.id) }

- EugZol

Answer 3

请尝试这个，然后告诉我它是否有效。

编辑-1

scope :unanswered_questions, lambda { joins('LEFT OUTER JOIN votes ON questions.id = votes.question_id').where('votes.question_id IS NULL') }

编辑-2

scope :unanswered_by, lambda {|u| where("questions.id NOT IN (SELECT votes.question_id from votes where votes.user_id = ?)",u.id) }

- shweta

---

我已经更新了答案，现在它将返回所有没有答案的问题。 - shweta

似乎更接近了。我正在尝试获取特定用户的未回答问题，即变量u。有什么想法如何检查某个用户不存在投票？ - Marc