PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to select where two rows in same column has same value

Note: Using Postgres
I tried for hours and read many posts but still can not figure out how to make this query.

I have two tables

chat table

id is_group_chat
1 false
2 false

user_chat table (linked with user table)

chat_id user_id
1 1
1 2
2 1
2 3
3 4
3 2

I need to select chat which is between two user only.

I have achieved this 

select c.id as c_id, c.chat_name as chat_name, c.created_at as created_at
from chats c 
left join user_chats uc on uc.chat_id = c.id 
where c.is_group_chat = false 
and uc.user_id = '39438219-1488-4e6f-820a-2ba16ba12e01'
intersect 
select c.id as c_id, c.chat_name as chat_name, c.created_at as created_at
from chats c 
left join user_chats uc on uc.chat_id = c.id 
where c.is_group_chat = false 
and uc.user_id = '16c1beb9-687a-45b3-8d2b-88c71a681168';

As you can see I needed to write almost same query two times is there any better way to do this.
Thanks in advance.

Tags:

3

Answers


  1. 0

    You could simply join the table, group the rows by the chat id, and then count them.

    WITH TwoUserChats AS (
    SELECT c.id as chat_id
    FROM chats c INNER JOIN
        user_chats uc ON uc.chat_id = c.id 
    WHERE c.is_group_chat = false 
        AND c.id IN 
          (
            '39438219-1488-4e6f-820a-2ba16ba12e01', 
            '16c1beb9-687a-45b3-8d2b-88c71a681168'
          )
    GROUP BY c.id
    HAVING COUNT(*) = 2
)

SELECT DISTINCT
     c.id AS c_id  
    ,c.chat_name AS chat_name 
    ,c.created_at AS created_at
FROM chats c INNER JOIN 
    TwoPersonChats ON c.id = TwoPersonChats.ID
    Login or Signup to reply.
  2. 0

    If you want to include chats that involved other users then just remove count() filter:

    select * from chats where is_group_chat = 'false' and chat_id in (
    select chat_id
    from user_chat
    group by chat_id
    having min(chat_id) = 'X' and max(chat_id) = 'Y' and count(chat_id) = 2
);

    If you possibly need to check more than two user ids (or if you just want to use this version with two ids):

    select * from chats where is_group_chat = 'false' and chat_id in (
    select chat_id
    from user_chat
    group by chat_id
    having count(case when chat_id in ('X', 'Y', ...) then 1 end) = N
);
    Login or Signup to reply.
  3. 0

    Based on your sample query, you appear to be only interested in specific users. This is based on that appearance.

    Use HAVING clause. Also, since you want to query user ids, then you INNER JOIN (not left join) to user_chat.

    create table chats (
  id integer, 
  chat_name varchar(20), 
  is_group_chat varchar(10), 
  create_at date
  );
  
insert into chats values 
(1, 'fruit', 'false', '2023-10-01'), 
(2, 'fruit', 'false', '2023-10-02'), 
(3, 'cars', 'true', '2023-10-03');

create table user_chats (
  chat_id integer, 
  user_id integer
  );
  
insert into user_chats values 
(1, 1), 
(1, 2), 
(2, 1), 
(2, 3), 
(3, 4), 
(3, 2);

    select 
 c.id as chat_id, 
 c.chat_name, 
 c.create_at
from chats c
join user_chats uc
  on c.id = uc.chat_id
where c.is_group_chat = 'false'
  and uc.user_id in (1, 2)
group by 1,2,3
having count(distinct uc.user_id) = 2;
    chat_id chat_name create_at
    1 fruit 2023-10-01T00:00:00.000Z

    View on DB Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search