Get list of user who are involved in conversation

Question

My "users" table is like this

id   name 
1    UserA
2    UserB
3    UserC
4    UserD

And my "chats" table is like this

id   text        sentBy    sentTo    created 
1    Hi UserB    1         2         2019-01-11
2    Hi          2         1         2019-01-12
3    Hi UserB    3         2         2019-01-13
4    Hello UserC 2         3         2019-01-14
5    Hello       3         2         2019-01-15
6    What u do   2         1         2019-01-16
7    Nothing     1         2         2019-01-17
8    Okay        2         1         2019-01-18
8    Hi UserA    3         1         2019-01-19

I want to show user list who is involved in a conversation with logged in user based on the last msg. Like if UserA logged in the system then the list should be like

userId    userName      text           created
3         UserC         Hi UserA       2019-01-19
2         UserB         Okay           2019-01-19

I tried by using a join query and group by but not succeed. I am using PostgreSQL with koa js.

Essential details are missing: Postgres version (`SELECT version();`, table definition (`CREATE TABLE` statement). And it looks like you have an error in the result: `2019-01-19` <> `2019-01-18` — Erwin Brandstetter, Mar 02 '19 at 05:26

score 1 · Accepted Answer · answered Mar 02 '19 at 05:26

You may use DISTINCT ON to filter out all apart from the most recent record for each involved in the conversation.

SELECT DISTINCT ON( involved) involved AS userid, 
                              u.NAME   AS username, 
                              text, 
                              created 
FROM   (SELECT c.*, 
               CASE sentby 
                 WHEN 1 THEN sentto 
                 ELSE sentby 
               END AS involved 
        FROM   chats c 
        WHERE  c.sentby = 1 
                OR c.sentto = 1) s 
       JOIN users u 
         ON s.involved = u.id 
ORDER  BY involved, 
          created DESC

Demo

If you want a generic case, you may convert this to a SQL type Postgres function and pass userid as an argument and use it in place of 1.

score 1 · Answer 2 · answered Mar 02 '19 at 05:56

To get a complete list of users involved in the last conversation of the $current_user, with the last message (sent or received) and its date (created):

WITH u1 AS (SELECT id FROM users WHERE name = $current_user)
SELECT DISTINCT ON (userId) *
FROM  (
   SELECT u.id AS userId, u.name AS userName, c.text, c.created  -- received
   FROM   u1
   JOIN   chats c ON c.sentBy = u1.id
   JOIN   users u ON u.id = c.sentTo

   UNION  ALL
   SELECT u.id, u.name, c.text, c.created -- sent
   FROM   u1
   JOIN   chats c ON c.sentTo = u1.id
   JOIN   users u ON u.id = c.sentBy
   ) sub
ORDER  BY userId, created DESC;

I separated into two UNIONed SELECTs, to make the most of two separate indexes on chats - one with leading sentTo, one with leading sentBy. Just updated a closely related answer yesterday:

Finding all rows with unique combination of two columns

About DISTINCT ON:

Select first row in each GROUP BY group?

Might be optimized in various ways, depending on undisclosed information.

Thanks for your help. This way is also working. I had tried the same way by using groupBy but PostgreSQL is throwing an error until is add all select field in groupBy. — NiRmaL, Mar 03 '19 at 14:43
@ProCylon: `GROUP BY` is no good for the purpose. Follow the link I provided for `DISTNCT ON` for a detailed explanation. — Erwin Brandstetter, Mar 03 '19 at 16:01

Get list of user who are involved in conversation

2 Answers2