Rails:如何选择没有特定相关(关联)对象的记录(SQL EXISTS简要介绍) [英] Rails: How to Select Records Which Don't Have a Specific Related (associated) Object (SQL EXISTS brief how-to)

问题描述

假设我们有用户:

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

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

Basically, standard many-to-many connection:

class Connection
  belongs_to :user
  belongs_to :group
end

我打算做的是:

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

• Select only users who don't belong to given set of Groups (groups with ids [4,5,6])
• Select only users who belong to one set of Groups ([1,2,3]) and don't belong to another ([4,5,6])
• Select only users who don't belong to a Group

我也不想:

• 从数据库中获取大量数据，以使用Ruby代码对其进行操作.我知道这会降低CPU和内存的效率(Ruby比任何常用的DB引擎都要慢得多，通常我想依靠DB引擎来完成繁重的工作)
• 我尝试了类似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/内存密集型)操作

• Fetch a lot of data from database to manipulate it with Ruby code. I know that will be inefficient in terms of CPU and memory (Ruby is much slower than any commonly used DB engine, and typically I want to rely on DB engine to do the heavy lifting)
• I tried queries like User.joins(:group).where(group_id: [1,2,3]).where.not(group_id: [4,5,6]) and they return wrong results (some users from the result set belong to groups 4,5,6 as well as 1,2,3)
• I don't want to do join merely for the sake of only checking for existence, because I know that that is a pretty complex (i.e. CPU/memory-intensive) operation for DB

推荐答案

在初学者到中级Rails开发人员中，这样的问题都很常见.您知道ActiveRecord界面和基本的SQL操作，但是您迷失了在问题中概述的这类任务. (此类问题的示例: 2 ).

Such questions are pretty common amongst beginner to middle-level Rails developers. You know ActiveRecord interface and basic SQL operations, but you are stumbled on such kind of tasks as outlined in the question. (Couple of examples of such questions: 1, 2).

答案很简单:使用SQL EXISTS条件.给定URL的快速参考:

The answer is simple: use SQL EXISTS condition. Quick reference from the given URL:

语法

SQL EXISTS条件的语法为:

The syntax for the SQL EXISTS condition is:

WHERE EXISTS ( subquery );

参数或参数

子查询

子查询是SELECT语句.如果子查询在其结果集中返回至少一条记录，则EXISTS子句的计算结果为true，并且将满足EXISTS条件.如果子查询不返回任何记录，则EXISTS子句的计算结果为false，并且不满足EXISTS条件.

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的问题:

It is also mentioned that EXISTS might be slower than JOIN, but that is usually not true. From the Exists v. Join question on SO:

EXISTS仅用于测试子查询是否返回结果，并尽快将其短路. JOIN用于通过将结果集与另一个有关系的表中的其他字段组合来扩展结果集. [...]如果索引正确，大多数情况下EXISTS的性能将与JOIN相同.例外是非常复杂的子查询，通常使用EXISTS更快.

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.

因此，数据库不需要浏览所有连接(一旦找到正确的连接，数据库就会停止以"exists"存在的联接"记录)，并且不需要从数据库中返回所有字段.表已联接(只需检查相应行的确存在).

So, the database doesn't need to look through all the connections (it stops 'joining' records with 'exists' as soon as it founds the right one), and doesn't need to return all the fields from the table joined (just check that the corresponding row, well, does exist).

回答具体问题:

仅选择不属于给定组(ID为[4,5,6]的组)的此类用户

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

仅选择属于一组组([1,2,3])但不属于另一组([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
  )")

这篇关于Rails:如何选择没有特定相关(关联)对象的记录(SQL EXISTS简要介绍)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！