I have a simple chat module that allows accounts to create multi-user rooms.
Given the below schema - is it possible to create a query that will check if room with given users already exist?
If this check could lead to performance issues - can you propose other design or approach?
Example data:
- chat_id: 1, users: [1, 2, 3]
- chat_id: 2, users: [2, 3]
- chat_id: 3, users: [1]
- chat_id: 4, users: [5, 6]
Desired queries:
Check if chat room for users [2, 3] exists => true, id: 2
Check if chat room for users [1, 2, 3] exists => true, id: 1
Check if chat room for users [2, 6] exists => false
Check if chat room for users [1, 2, 6] exists => false
I'm using postgres 11.2
EDIT: I should also be able to get the chat id if given combination exists.
Based on the other answers, I ended up writing my own query:
SELECT chat_id FROM chat_account
WHERE chat_id IN (
SELECT c2.chat_id
FROM chat_account c2
WHERE c2.account_id IN (2, 3)
)
GROUP BY chat_account.chat_id
HAVING array_agg(chat_account.account_id) = ARRAY[2, 3]