I have a table called conversations. Beside that, I have a table called conversation_participants.
When users create a conversation, they pick which other users they want to make it with. I do, tough, want to check before creating a new conversation, that it doesn't already exists with that batch of participants - if so I just want to reuse the existing one.
So my question is. How do I find a row in conversation that has the exact relations in conversation_participants.
My table looks like this (simplified).
| conversations |
| ------------------- |
| id | int (pk) |
| created | timestamp |
| ------------------- |
| conversation_participants |
| ---------------------------------------- |
| id | int (pk) |
| created | timestamp |
| conversation | int (fk to conversations) |
| user | int (fk to users) |
| read | timestamp |
| ---------------------------------------- |
Now the question is. How do I find the row in conversation that has the exact set of users in conversation_participants? It must be the exact set – not just a subset.
Find all conversations that do not have a conversation_participants that is not in the list:
SELECT c.id
FROM conversations AS c
WHERE NOT EXISTS (SELECT 1 FROM conversation_participants AS cp
WHERE cp.conversation = c.id
AND cp."user" NOT IN (/* list of users */));
You can use aggregation. If you construct the list of participants in order in a string or array, you can use:
select cp.conversation
from conversation_participants cp
group by cp.conversation
having string_agg(user order by user, ',') = '1,2,3,4';
You can also do this without strings or arrays:
select cp.conversation
from conversation_participants cp
where user in (1, 2, 3, 4)
group by cp.conversation
having count(*) = 4; -- all four users
If you pass the values in as an array:
select cp.conversation
from conversation_participants cp
where user i= any :ar
group by cp.conversation
having count(*) = cardinality(:ar) -- all four users