Tengo una tabla llamada conversations . Además de eso, tengo una tabla llamada conversation_participants .
Cuando los usuarios crean una conversación, eligen con qué otros usuarios quieren hacerlo. Quiero verificar, antes de crear una nueva conversación, que aún no existe con ese lote de participantes; si es así, solo quiero reutilizar la existente.
Entonces mi pregunta es. ¿Cómo encuentro una fila en una conversation que tenga las relaciones exactas en conversation_participants ?
Mi tabla se ve así (simplificado).
| 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 | | ---------------------------------------- | Ahora la pregunta es. ¿Cómo encuentro la fila en la conversation que tiene el conjunto exacto de usuarios en conversation_participants ? Debe ser el conjunto exacto, no solo un subconjunto.
Encuentre todas las conversations que no tengan una conversation_participants que no esté en la lista:
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 */));Puede utilizar la agregación. Si construye la lista de participantes en orden en una cadena o matriz, puede usar:
select cp.conversation from conversation_participants cp group by cp.conversation having string_agg(user order by user, ',') = '1,2,3,4';También puede hacer esto sin cadenas o matrices:
select cp.conversation from conversation_participants cp where user in (1, 2, 3, 4) group by cp.conversation having count(*) = 4; -- all four usersSi pasa los valores como una matriz:
select cp.conversation from conversation_participants cp where user i= any :ar group by cp.conversation having count(*) = cardinality(:ar) -- all four users