sql - Rails has_and_belongs_to_many find unique objects in common -

i have 2 models, conversation , phones, both of has_and_belongs_to_many each other. phones can have lot of conversations, , conversations can have 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 conversations_phones join table well.

if have 2 or more phone objects, how find list of conversations share? catch: conversations can't include other phones (ie number of phone ids equals number search with).

i've been able pure rails, involves looping every conversation , counting on db. not good.

i don't mind doing pure sql; using model ids should stop injection attacks.

the closest i've come is:

select conversations.* conversations  inner join conversations_phones t0_r0 on conversations.id = t0_r0.conversation_id  inner join conversations_phones t0_r1 on conversations.id = t0_r1.conversation_id  (t0_r0.phone_id = ? , t0_r1.phone_id = ?), @phone_from.id, @phone_to.id 

but includes conversations outside phones. have feeling group , having count help, i'm new sql.

i think there. exclude conversations outsiders additional not exists anti-semi-join:

select c.*   conversations c join   conversations_phones cp1 on cp1.conversation_id = c.id                                   , cp1.phone_id = ? join   conversations_phones cp2 on cp2.conversation_id = c.id                                   , cp2.phone_id = ? ... not exists (    select 1      conversations_phones cp     cp.conversation_id = c.id    ,    cp.phone_id not in (cp1.phone_id, cp2.phone_id, ...) -- or repeat param    ) , @phone1.id, @phone2.id, ... 

i pulled conditions join clause simplicity, doesn't change query plan.
goes without saying need indices on conversations(id) , conversations_phones(conversation_id, phone_id).

alternatives (much slower):

very simple, slow:

select cp.conversation_id  (    select conversation_id, phone_id      conversations_phones    order  1,2    ) cp group  1 having array_agg(phone_id) = ? 

.. ? sorted array of ids '{559,12801}'::int[]

30x slower in quick test.

for completeness, (simplified) proposed alternative @broisatse in comments performs around 20x slower in similar quick test:

... join (    select conversation_id, count(*) phone_count      conversations_phones    group  prod_id    ) pc on pc.conversation_id = c.id , phone_count = 2 

or, simpler , faster:

... join (    select conversation_id      conversations_phones    group  prod_id    having count(*) = 2    ) pc on pc.conversation_id = c.id