Rails - Active Record：查找所有具有特定属性的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的用户，但是如果他们有确认为真的附加身份验证，则它也可能有附加身份。我只想要那些确认为假且没有已确认属性为真的附加身份的用户。

我也尝试过这个方法，但很明显速度很慢，我正在寻找正确的SQL以便在一个查询中完成这个任务。

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

提前道歉，如果已经有类似的帖子，我没有找到。

- Derrick Mar

---

布尔列可以为空吗？或者，是否有默认设置，使它始终为 false 或 true？此外，您使用的是哪种DBMS（MySQL，PostgreSQL等），布尔值在数据库中如何存储？（0/1，“f” /“t”等） - Max Williams

嗨Max。让我们假设该列不能为空，因此只能为false或true。 - Derrick Mar

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表上，一次在IDENTITIES表上。通过对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)

- Derrick Mar

Answer 2

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

- Nermin

---

嘿@Nermin。这似乎并没有解决问题。我认为调用uniq是对用户的唯一性调用，但所有返回的用户应该已经是唯一的了。 - Derrick Mar

Answer 3

我认为在这里，group_concat可能是答案，如果你的数据库管理系统中有这个函数的话（如果没有可能有一个等效的函数）。这个函数将会把组中字段的所有值收集到一个逗号分隔的字符串中。我们要找的是那些字符串等于"false"的记录：也就是说，只有一个，并且它的值是false（我认为这是你的要求，但有点不太清楚）。我认为如果我们让Rails处理将false转换为DB存储方式的话，这应该能够工作。

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

编辑 - 如果您的数据库将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)

（注意引号周围的问号）

编辑2 - postgres版本。

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

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

- Max Williams

---

嘿，Max。问题在于 User.joins(:identities).group("identities.user_id") 会导致 PG::GroupingError: ERROR: column "users.id" must appear in the GROUP BY clause or be used in an aggregate function 的错误。 - Derrick Mar

你能回答一下我上面关于你使用的数据库管理系统是哪种吗？ - Max Williams

我觉得我们离目标越来越近了，但是我遇到了一个错误PG::InvalidTextRepresentation: ERROR: array value must start with "{" or dimension information，因为identities.confirmed不是一个数组类型。 - Derrick Mar