sql - Rails has_and_belongs_to_many 找到共同的唯一对象

Question

我有两个模型，Conversation 和 Phones，它们彼此有_and_belongs_to_many 个。电话可以有很多对话，对话可以有很多电话(两个或更多)。

class Conversation < ActiveRecord::Base
  has_and_belongs_to_many :phones
end

class Phone < ActiveRecord::Base
  has_and_belongs_to_many :conversations
end

当然，还有一个 conversations_phones 连接表。

如果我有两个或更多电话对象，我如何找到他们共享的所有对话的列表？问题:对话不能包含任何其他手机(即手机 ID 的数量等于我们搜索的数量)。

我已经能够使用纯 Rails 做到这一点，但它涉及循环每个对话并计算数据库。不好。

我不介意做纯 SQL；使用模型 ID 应该有助于阻止注入(inject)攻击。

我最接近的是:

SELECT conversations.* FROM conversations 
INNER JOIN conversations_phones AS t0_r0 ON conversations.id = t0_r0.conversation_id 
INNER JOIN conversations_phones AS t0_r1 ON conversations.id = t0_r1.conversation_id 
WHERE (t0_r0.phone_id = ? AND t0_r1.phone_id = ?), @phone_from.id, @phone_to.id

但它包括与外部电话的对话。我觉得 GROUP BY 和 HAVING COUNT 会有所帮助，我对 SQL 太陌生了。

Answer 1

我想你快到了。只需使用额外的 NOT EXISTS 反半连接排除与外人的对话:

SELECT c.*
FROM   conversations c
JOIN   conversations_phones AS cp1 ON cp1.conversation_id = c.id
                                  AND cp1.phone_id = ?
JOIN   conversations_phones AS cp2 ON cp2.conversation_id = c.id
                                  AND cp2.phone_id = ?
...
WHERE NOT EXISTS (
   SELECT 1
   FROM   conversations_phones cp
   WHERE  cp.conversation_id = c.id
   AND    cp.phone_id NOT IN (cp1.phone_id, cp2.phone_id, ...) -- or repeat param
   )
, @phone1.id, @phone2.id, ...

为了简单起见，我将条件放入 JOIN 子句中，不会更改查询计划。
不用说，您需要 conversations(id) 和 conversations_phones(conversation_id, phone_id) 的索引。

备选方案(慢得多):

非常简单，但是很慢:

SELECT cp.conversation_id
FROM  (
   SELECT conversation_id, phone_id
   FROM   conversations_phones
   ORDER  BY 1,2
   ) cp
GROUP  BY 1
HAVING array_agg(phone_id) = ?

.. 其中 ? 是一个排序的 ID 数组，例如 '{559,12801}'::int[]

在快速测试中慢了 30 倍。

为了完整起见，(简化)建议 alternative by @BroiSatse in the comments在类似的快速测试中执行大约 20 倍:

...
JOIN (
   SELECT conversation_id, COUNT(*) AS phone_count
   FROM   conversations_phones
   GROUP  BY prod_id
   ) AS pc ON pc.conversation_id = c.id AND phone_count = 2

或者，更简单、更快:

...
JOIN (
   SELECT conversation_id
   FROM   conversations_phones
   GROUP  BY prod_id
   HAVING COUNT(*) = 2
   ) AS pc ON pc.conversation_id = c.id