sql - rails : How to Select Records Which Don't Have a Specific Related (associated) Object (SQL EXISTS brief how-to)

Question

假设我们有用户:

class User < ActiveRecord::Base
  has_many :connections
  has_many :groups, through: :connections
end

和组:

class Group < ActiveRecord::Base
  has_many :connections
  has_many :users, through: :connections
end

基本上，标准的多对多连接:

class Connection
  belongs_to :user
  belongs_to :group
end

我打算做的是:

• 仅选择不属于给定组(ID 为 [4,5,6] 的组)的用户
• 仅选择属于一组组 ([1,2,3]) 且不属于另一组 ([4,5,6] >)
• 仅选择不属于组的用户

此外，我不想:

• 从数据库中获取大量数据，然后使用 Ruby 代码对其进行操作。我知道这在 CPU 和内存方面效率低下(Ruby 比任何常用的数据库引擎都慢得多，通常我想依靠数据库引擎来完成繁重的工作)
• 我尝试了像 User.joins(:group).where(group_id: [1,2,3]).where.not(group_id: [4,5,6]) 和他们返回错误的结果(结果集中的一些用户属于组 4、5、6 以及 1、2、3)
• 我不想仅仅为了检查是否存在而执行 join，因为我知道这对于 DB 来说是一个非常复杂的(即 CPU/内存密集型)操作<

Answer 1

此类问题在 Rails 初级到中级开发人员中很常见。你知道 ActiveRecord 接口(interface)和基本的 SQL 操作，但你无意中遇到了问题中概述的这类任务。 (此类问题的几个示例:1、2)。

答案很简单:使用 SQL EXISTS condition .来自给定 URL 的快速引用:

Syntax

The syntax for the SQL EXISTS condition is:

WHERE EXISTS ( subquery );

Parameters or Arguments

subquery

The subquery is a SELECT statement. If the subquery returns at least one record in its result set, the EXISTS clause will evaluate to true and the EXISTS condition will be met. If the subquery does not return any records, the EXISTS clause will evaluate to false and the EXISTS condition will not be met.

还提到 EXISTS 可能比 JOIN 慢，但这通常不是真的。来自Exists v. Join关于 SO 的问题:

EXISTS is only used to test if a subquery returns results, and short circuits as soon as it does. JOIN is used to extend a result set by combining it with additional fields from another table to which there is a relation. [...] If you have proper indexes, most of the time the EXISTS will perform identically to the JOIN. The exception is on very complicated subqueries, where it is normally quicker to use EXISTS.

因此，数据库不需要查看所有连接(一旦找到正确的连接，它就会停止“连接”记录与“存在”)，也不需要返回所有字段连接表(只需检查相应的行是否存在)。

回答具体问题:

Select only such users, who don't belong to given set of Groups (groups with ids [4,5,6])

not_four_to_six = User.where("NOT EXISTS (
   SELECT 1 FROM connections
   WHERE connections.user_id = users.id
   AND connections.group_id IN (?)
  )", [4,5,6])

Select only such users, who belong to one set of Groups ([1,2,3]) and don't belong to another ([4,5,6])

one_two_three = not_four_to_six.where("EXISTS (
   SELECT 1 FROM connections
   WHERE connections.user_id = users.id
   AND connections.group_id IN (?)
  )", [1,2,3])

Select only such users, who doesn't belong to a Group

User.where("NOT EXISTS (
   SELECT 1 FROM connections
   WHERE connections.user_id = users.id
  )")