chat table summary list pgsql

Question

I have chat history table as below. where i will store from and to userIds.

i am using Postgresql

CREATE TABLE chat_history (
    chat_id serial,
    sender_id serial REFERENCES users(user_id) ON DELETE CASCADE,
    recipient_id serial REFERENCES users(user_id) ON DELETE CASCADE,
    message text NOT NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    channel_id VARCHAR(100),
    PRIMARY key (chat_id),
    read_flag boolean
);

now i want to get the summary list of the user chat. For ex: user1 - senderId = 1, user2 - recipientId = 2.

1. if these two users are chatting i need to pull the history. How to pull the history of the user

2. If an user has unread messages from multiple users how to get the summary list Like:

3. user1 to user2 => 5 unread message

4. user3 to user2 => 2 unread message

5. user5 to user2 => 2 unread message

I have tried the below query like

select  sender_id, recipient_id, count(*) from chat_history ch 
where (sender_id = 99 or recipient_id = 99) and (sender_id = 98 or recipient_id = 98)
and read_flag = false or read_flag = null
group by (sender_id, recipient_id, read_flag);

OutPut:

but it should return count = 4 irrespective of the sender and recipient Id

EDIT: Tried below query

select distinct sender_id, recipient_id, count(*) from chat_history ch 
where (sender_id = 99 or recipient_id = 99) 
and read_flag = false or read_flag = null
group by (read_flag, sender_id, recipient_id);

but same out out

Adding live play ground: https://www.db-fiddle.com/f/4xvD4ZTPW2npKcy3Au92Y9/4

Answer 1

Your db-fiddle and your problem description do not match. You are asking for a couple of different things here.

Query #1 addresses the example in your question

    with all_ids as (
      select sender_id as user_id from chat_history
      union
      select recipient_id from chat_history
    )
    select i.user_id, count(*) as unread_sent_or_rcvd
      from chat_history h
      join all_ids i 
        on (i.user_id = h.sender_id or i.user_id = h.recipient_id)
     where coalesce(h.read_flag, false) = false
     group by i.user_id
     order by i.user_id;

| user_id | unread_sent_or_rcvd |
| ------- | ------------------- |
| 97      | 1                   |
| 98      | 48                  |
| 99      | 46                  |

Query #2 addresses your problem statement

I checked, and there is a record of 98 with an unread message to himself in his data. He takes equal opportunity stalking to the next level.

    with all_ids as (
      select sender_id as user_id from chat_history
      union
      select recipient_id from chat_history
    )
    select i.user_id,
           case
              when i.user_id = h.sender_id then h.recipient_id
              else h.sender_id
           end as correspondent_id,
           count(*) filter (where i.user_id = h.sender_id) as unread_sent,
           count(*) filter (where i.user_id = h.recipient_id) as unread_rcvd
      from chat_history h
      join all_ids i 
        on (i.user_id = h.sender_id or i.user_id = h.recipient_id)
     where coalesce(h.read_flag, false) = false
     group by i.user_id, 
              case
                when i.user_id = h.sender_id then h.recipient_id
                else h.sender_id
              end
     order by i.user_id;

| user_id | correspondent_id | unread_sent | unread_rcvd |
| ------- | ---------------- | ----------- | ----------- |
| 97      | 98               | 1           | 0           |
| 98      | 97               | 0           | 1           |
| 98      | 98               | 1           | 1           |
| 98      | 99               | 44          | 2           |
| 99      | 98               | 2           | 44          |

View on DB Fiddle