Determine if 2 users already have a private chat

I have the following database schema for a simple chat app (simplified):

## users table
id

## chats table
id, type

## chat_user
chat_id, user_id

The issue I want to solve is that given I have 2 users, how can I get a chat record for a given type given I know that this specific chat type should only exist once if it already exists?

For example, we have bob (id: 1) and james (id: 2). bob initiates a chat with james:

insert into chats (type) values ('private') returning id;
-- returned ID is: 1234

insert into chat_user (chat_id, user_id) values (1234, 1);
insert into chat_user (chat_id, user_id) values (1234, 2);

Now I want a query that will yield the chat row with the ID 1234 if I give it the 2 users.

one solution I found for that is finding the intersecting chat_id for those 2 users and since I know that there is only ever 1 private chat for those 2 users, I simply take the first result:

select * from chats where id in (
  select chat_id from chat_user where user_id = 1 and chat_id in (
    select chat_id from chat_user where user_id = 2
  )
) and type = 'private' limit 1;

Now, while this works, I wonder if this is the best solution for this? Will this query get slower and slower as users have more chats?

If it matters, I’m using Postgres for this chat app.

Answer

You can use INTERSECT:

select * from chats 
where type = 'private'
and id in (
  select chat_id from chat_user where user_id = 1 
  intersect
  select chat_id from chat_user where user_id = 2
)
limit 1;

I don’t think that LIMIT 1 is needed if there can only be at most 1 result returned.
Or join the tables, filter, group by id and set a condition in the having clause:

select c.id 
from chats c inner join chat_user u
on u.chat_id = c.id
where c.type = 'private' and u.user_id in (1, 2) 
group by c.id
having count(*) = 2

Advertisement

Answer