sqlpostgresqldatabase-designchatjunction-table

Postgres query to find if combination already exist in junction table


I have a simple chat module that allows accounts to create multi-user rooms.

enter image description here

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.


Solution

  • 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]