Rails - Active Record:查找所有与特定属性有count on has_many关联的记录

Question

一个用户有多个身份。

class User < ActiveRecord::Base
    has_many :identities
end
class Identity < ActiveRecord::Base
    belongs_to :user
end

一个单位有一个confirmed:boolean列。我想查询只有一个身份的所有用户。这个身份也必须被确认为假。

我试过了

User.joins(:identities).group("users.id").having( 'count(user_id) = 1').where(identities: { confirmed: false })

但是这会返回一个身份confirmed:false 的用户，但是如果确认为真，他们也可以有其他身份。我只希望用户只有一个身份确认:false和没有其他身份确认属性为true

我也尝试过这个，但显然它很慢，我正在寻找正确的SQL，只是在一个查询中做到这一点。

  def self.new_users
    users = User.joins(:identities).where(identities: { confirmed: false })
    users.select { |user| user.identities.count == 1 }
  end

如果这个帖子已经有人回答了，我很抱歉，但是我找不到类似的帖子。

Answer 1

一个解决方案是使用rails嵌套查询

User.joins(:identities).where(id: Identity.select(:user_id).unconfirmed).group("users.id").having( 'count(user_id) = 1')

下面是查询 生成的SQL

SELECT "users".* FROM "users"
INNER JOIN "identities" ON "identities"."user_id" = "users"."id"
WHERE "users"."id" IN (SELECT "identities"."user_id" FROM "identities"  WHERE "identities"."confirmed" = 'f')
GROUP BY users.id HAVING count(user_id) = 1

我仍然认为这不是最有效的方法。虽然我只能生成一个SQL查询(意味着只有一个对数据库的网络调用)，但我仍然必须进行两次扫描:一次扫描USERS表，一次扫描identity表。这可以通过索引identities.confirmed列来优化，但这仍然不能解决两次完全扫描的问题。

对于那些理解查询计划的人来说，它是:

     QUERY PLAN
-------------------------------------------------------------------------------------------
 HashAggregate  (cost=32.96..33.09 rows=10 width=3149)
   Filter: (count(identities.user_id) = 1)
   ->  Hash Semi Join  (cost=21.59..32.91 rows=10 width=3149)
         Hash Cond: (identities.user_id = identities_1.user_id)
         ->  Hash Join  (cost=10.45..21.61 rows=20 width=3149)
               Hash Cond: (identities.user_id = users.id)
               ->  Seq Scan on identities  (cost=0.00..10.70 rows=70 width=4)
               ->  Hash  (cost=10.20..10.20 rows=20 width=3145)
                     ->  Seq Scan on users  (cost=0.00..10.20 rows=20 width=3145)
         ->  Hash  (cost=10.70..10.70 rows=35 width=4)
               ->  Seq Scan on identities identities_1  (cost=0.00..10.70 rows=35 width=4)
                     Filter: (NOT confirmed)
(12 rows)

Answer 2

  def self.new_users
    joins(:identities).group("identities.user_id").having("count(identities.user_id) = 1").where(identities: {confirmed: false}).uniq
  end

Answer 3

我认为group_concat可能是这里的答案，如果你有你的DBMS功能。(如果没有，可能有一个等效的)。这将把组中字段的所有值收集到一个逗号分隔的字符串中。我们想要一个这个字符串等于"假":即，只有一个，它是假的(我认为这是你的要求，这有点不清楚)。我认为这应该工作，如果我们让Rails处理false的翻译，但DB存储它。

User.joins(:identities).group("identities.user_id").having("group_concat(identities.confirmed) = ?", false)

EDIT -如果您的数据库将false存储为0，那么上面将生成类似having group_concat(identities.confirmed) = 0的sql。因为group_concat的结果是一个字符串，所以它可能(在某些DBMS中)在将结果与0进行比较之前对其进行字符串到整数的强制转换，如果所有其他字符串都强制转换为0，则会返回许多误报。在这种情况下，你可以试试:

User.joins(:identities).group("identities.user_id").having("group_concat(identities.confirmed) = '?'", false)

postgres version.

我没有尝试过，但看起来最近版本的postgres有一个函数array_agg()，它与mysql的group_concat()相同。因为postgres将true/false存储为't'/'f'，所以我们不需要包装?在报价。试试这个:

User.joins(:identities).group("identities.user_id").having("array_agg(identities.confirmed) = ?", false)