Rails has_and_belongs_to_many查找共同的唯一对象 [英] Rails has_and_belongs_to_many find unique objects in common

问题描述

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

  class Conversation< ActiveRecord :: Base 
 has_and_belongs_to_many：phones 
 end 
 
 class Phone< ActiveRecord :: Base 
 has_and_belongs_to_many：conversations 
 end 
  

当然，

如果我有两个或多个电话对象，如何找到他们共享的所有对话的列表？问题在于：对话中不能包含任何其他电话（即电话ID的数量等于我们搜索的电话的数量）。

我已经能够做到使用纯Rails，但它涉及循环每个对话并依靠db。不好。

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

我最近来的是：

 选择对话。*从对话
内加入sessions_phones AS t0_r0 ON对话.id = t0_r0.conversation_id 
内参sessions_phones AS对话t0_r1 ON对话.id = t0_r1.conversation_id 
在（t0_r0.phone_id =？AND t0_r1.phone_id =？），@ phone_from.id，@ phone_to.id 
  

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

解决方案

我认为您是差不多好了。只需排除与外部人的对话，再加上不存在反半加入即可：

 选择c。* 
从对话中c 
加入sessions_phones作为cp1在cp1.conversation_id = c.id 
和cp1.phone_id =吗？ 
加入sessions_phones AS cp2在cp2.conversation_id = c.id 
和cp2.phone_id =吗？ 
 ... 
不存在（
选择1 
从sessions_phones cp 
那里cp.conversation_id = c.id 
和cp.phone_id不存在（cp1.phone_id，cp2.phone_id，...）-或重复参数
）
，@ phone1.id，@ phone2.id，... 
  

为简单起见，我将条件放入JOIN子句中，不会更改查询计划。

不用说你在 conversations（id）和 conversations_phones（conversation_id，phone_id）上需要索引。 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倍

为完整起见，（简化）建议的 @BroiSatse在评论中的替代方法在类似的快速测试中的执行速度比慢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查找共同的唯一对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！