CODEWITHSUNDEEP
sqlpostgresql
Sam Leurs
Sam Leurs

Reputation: 2000

select all conversations and last message of each conversation (+ user data)

I have the following tables

table: users

user_id | username
------------------
1       | john
2       | jane
3       | jack

table: conversations

conversation_id | sender_id | receiver_id
-----------------------------------------
1               | 1         | 2
2               | 1         | 3

table: messages

message_id | conversation_id | user_id | message         | timestamp
--------------------------------------------------------------------
1          | 1               | 1       | hi jane         | 12345
2          | 1               | 2       | hi john         | 12345
3          | 1               | 1       | how are you?    | 12345
4          | 1               | 2       | i'm fine!       | 12345
5          | 1               | 1       | i'm okay too!   | 12345

What I want to achieve, in one query if possible: fetching all the conversations where a specific user (let's say user ID: 1) is the sender OR receiver, and the latest message of that conversation.

First query: I'm selecting all conversation_id's where user ID = 1 is the sender or receiver, and I'm fetching the other user ID as 'user_id' in the results:

SELECT c.conversation_id,
CASE
    WHEN
        c.sender_id = 1
    THEN
        c.receiver_id
    ELSE
        c.sender_id
END as user_id
FROM conversations AS c

I also want the username of the OTHER user in the conversation. I can do this with this query:

SELECT c.conversation_id, u.username,
CASE
    WHEN
        c.sender_id = 1
    THEN
        c.receiver_id
    ELSE
        c.sender_id
END as user_id
FROM conversations AS c
JOIN users AS u ON
CASE
    WHEN
        c.sender_id = 1
    THEN
        c.receiver_id = u.user_id
    ELSE
        c.sender_id = u.user_id
END

This also works! But now I'm stuck. In the same query, I also want the last message in every conversation. In my example, there is a conversation between user ID's 1 and 2. It doesn't matter if the last message is from the sender or the receiver, I just want the last message AND timestamp.

How can I achieve this? I'm trying with MAX(), GROUP BY, ... but I'm getting really stuck.

Upvotes: 1

Views: 446

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270683

I think a join and distinct on do what you want:

select distinct on (c.conversation_id) c.*, m.*
from conversations c join
     messages m
     on m.conversation_id = c.conversation_id
where 1 in (c.sender_id, c.receiver_id)
order by c.conversation_id, m.id desc;

I don't know if you prefer ordering by messages or by ids.

The above query selects the records. If you then want to order them by conversations . . . you can use a subquery:

select cm.*
from (select distinct on (c.conversation_id) c.*, m.*  -- list out the columns you want here so there are no duplicates
      from conversations c join
           messages m
           on m.conversation_id = c.conversation_id
      where 1 in (c.sender_id, c.receiver_id)
      order by c.conversation_id, m.id desc
     ) cm
order by m.id desc

Upvotes: 1

Related Questions