Postgres many to many: how to select record with exactly counted links

Question

With that schema (there's more fields, sure) I wonder to select exactly chats where clients.id are for example 344,345 and they are exactly two.

But I find all chats where they are with others clients, not only they two.

_{(source: zimagez.com)}

Big picture http://www.zimagez.com/zimage/107410861087108810861089-10871086-habtm0.php

SELECT 
  chat_rooms.id, 
  chat_rooms.token
FROM 
  chat_rooms, 
  clients, 
  chat_rooms_clients
WHERE 
  chat_rooms_clients.chat_room_id = chat_rooms.id AND
  chat_rooms_clients.client_id = clients.id AND
  clients.id IN (344,354) AND 
  chat_rooms.meeting_id IS NULL
group by chat_rooms.id
having count(clients.id) = 2
ORDER BY
  chat_rooms.created_at DESC;

My app is written with rails.

Answer 1

Using the supplied test data:

set search_path = testschema, pg_catalog;
select cr.id, array_agg(crc.client_id order by crc.client_id), cr.token
from
    chat_rooms_clients crc
    inner join
    chat_rooms cr on cr.id = crc.chat_room_id
where cr.meeting_id is null
group by 1
having array_agg(crc.client_id order by crc.client_id) = array[2467,2471]::int[]
order by cr.created_at desc;
 id  |  array_agg  |                token
-----+-------------+--------------------------------------
 234 | {2467,2471} | bd36db10-3a89-40fe-a3e3-524b4278b718

Notice that for this query it is not necessary to join the clients table. I think it is wise to create a primary key in the chat_rooms_clients table:

create table chat_rooms_clients (
    chat_room_id integer not null,
    client_id integer not null,
    primary key (chat_room_id, client_id)
);

Otherwise, to be on the safe side, it is necessary to add distinct in the array_agg in the above query:

having array_agg(distinct crc.client_id order by crc.client_id) = ...

If the chat room creator must be one of the chat participants then filter it to improve performance:

where cr.meeting_id is null and cr.creator_id in (2467,2471)