CODEWITHSUNDEEP
sqlpostgresql
Will
Will

Reputation: 408

Check if a chat between 2 people exists

I have a chat system that handles group chats. A conversation can have many participants and the only distinction between group and non group chats is a non group chat will only have 2 participants.

I want a query to check if a non group chat exists between 2 users.

Conversations table

| id |

Participants table

| id | conversation (FK) | user (FK to a users table) |

To check if a single conversation exists I have come up with this query

select participants.conversation, CAST ( count(participants.user) AS INTEGER ) 
as members
from participants
where participants.user = 3 or participants.user = 18
group by participants.conversation
having CAST ( count(participants.user) AS INTEGER ) = 2

I Have created a single chat between users 3 and 18, and also a group chat between 3, 18 and 17. But when I run this query, both conversations are retuning 2 members, when the group chat has 3 participants.

What am I doing wrong?

Upvotes: 1

Views: 499

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You can get all such non-groups with an aggregation query:

select conversation
from participants p
group by conversation
having count(*) = 2 and
       count(*) filter (where p.user = 3) = 1 and
       count(*) filter (where p.user = 18) = 1;

Or a simpler having clause:

having array_agg(p.user order by p.user) = array[3,18]

Upvotes: 1

Iłya Bursov
Iłya Bursov

Reputation: 24156

the main problem is that query filters all users (except 2) first, before grouping, this is why all conversations looks like 2-user groups, I suggest to rewrite query like this:

with chats as (
  select distinct conversation
  from participants
  where "user" in (3, 18)
),
nongroup as (
  select conversation
  from participants
  where conversation in (select conversation from chats)
  group by conversation
  having count(*) = 2
)
select * from nongroup

first CTE will pick all conversations for given 2 users, second CTE will pick only 2 user rooms

fiddle: http://sqlfiddle.com/#!15/b5e25/3

Upvotes: 0

Related Questions