0

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?

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.

milo
  • 427
  • 5
  • 14

3 Answers3

1

I think this does what you want:

select ca.chat_id
from chat_account ca
group by ca.chat_id
having count(*) filter (where account_id in (2, 3)) = 2 and
       count(*) = 2;

You can also use:

having array_agg(account_id order by account_id) = array[2, 3]
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Looks like it works after your last fix. Could you explain why? I'm also curious about performance of this because there isn't any where clause. Wouldn't it query all rows inside the table? – milo Mar 05 '20 at 23:25
  • @milo . . . Well, yes. It needs to query all the rows. Whether that is good or bad depends on the size of your data and what proportion have the list of values that you want. – Gordon Linoff Mar 06 '20 at 02:20
0

you need something like this

with selected_users as (select * from accounts where id in (2,3)),
users_chat_rooms as (
select chat_id,
array_agg(account_id order by account_id asc) users_in_room
from chat_account
group by chat_id
)
select * from users_chat_rooms
where chat_id in (select chat_id from chat_account where account_id in (select id from selected_users))
and users_in_room = (select array_agg(id order by id asc) from selected_users)

see fiddle https://dbfiddle.uk/?rdbms=postgres_11&fiddle=f360a05c57a33f5c130fcfa0d55429ff

probably you will be showing this to a specific user, so you can filter for the "logged in" user's chats.

select * from users_chat_rooms
where chat_id in (select chat_id from chat_account where account_id = <logged_in_user>)
and users_in_room = (select array_agg(id order by id asc) from selected_users)
Frederic
  • 1,018
  • 6
  • 11
0

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]
milo
  • 427
  • 5
  • 14