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?

Answer

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]