Rails has_and_belongs_to_many查找共同的唯一对象

问题描述

我有两个模型，即对话和电话，两者彼此都有。电话可以有很多对话，而对话可以有很多电话（两个或更多）。

  p> 
 
 
替代方法（慢得多）：
 
 
 
很简单，但是很慢：
  SELECT cp.conversation_id 
 FROM（
选择对话ID，电话ID 
从对话电话
按1,2 $ b $订购b）cp 
 GROUP BY 1 
 HAVING array_agg（phone_id）=？ 
  
 ..其中？是一个排序数组'{559,12801}':: int []  
 
 
 
 慢30倍 
 
 
为完整起见，（简化）建议的在类似的快速测试中的执行速度比慢20倍：
  ... 
 JOIN（
选择session_id，COUNT（*）AS phone_count 
从对话_电话
 GROUP BY prod_id 
）AS pc on pc.conversation_id = c .id AND phone_count = 2 
  
或者，更简单，更快捷：
  ... 
 JOIN（
从sessions_phones中选择session_id 
 
 prod_id 
有（*）= 2 
）AS pc在pc.conversation_id = c.id 
  
 
I have two models, Conversation and Phones, both of which has_and_belongs_to_many each other. Phones can have a lot of conversations, and conversations can have a lot of phones (two or more).
class Conversation < ActiveRecord::Base
  has_and_belongs_to_many :phones
end

class Phone < ActiveRecord::Base
  has_and_belongs_to_many :conversations
end
Of course, there's a conversations_phones join table as well.
If I have two or more phone objects, how do I find a list of all the conversations they share? The catch: the conversations can't include any other phones (IE the number of phone IDs equals the number we search with).
I've been able to do it with pure Rails, but it involves looping every conversation and counting on the db. Not good.
I don't mind doing pure SQL; using the model IDs should help stop injection attacks.
The closest I've come is:
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
But it includes conversations with outside phones. I have a feeling GROUP BY and HAVING COUNT would help, I'm just too new to SQL.
 解决方案 
I think you were almost there. Just exclude conversations with outsiders with an additional NOT EXISTS anti-semi-join:
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, ...
I pulled conditions into the JOIN clause for simplicity, doesn't change the query plan.
Goes without saying that you need indices on conversations(id) and conversations_phones(conversation_id, phone_id).
Alternatives (much slower):
Very simple, but slow:
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) = ?
.. where ? is a sorted array of ids like '{559,12801}'::int[]
30x slower in a quick test.
For completeness, the (simplified) proposed alternative by @BroiSatse in the comments performs around 20x slower in a similar quick test:
...
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
Or, slightly simpler and faster:
...
JOIN (
   SELECT conversation_id
   FROM   conversations_phones
   GROUP  BY prod_id
   HAVING COUNT(*) = 2
   ) AS pc ON pc.conversation_id = c.id
                        
这篇关于Rails has_and_belongs_to_many查找共同的唯一对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！