Pick row that has multiple other rows in adjacent table

user_427542 0
Viewed 29

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.

postgresql sql
2 Answers

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 */));
user_64643080

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
user_11440350
Related