Aniket Singh Aniket Singh - 6 months ago 13
SQL Question

How to get last message from every conversation

I have messaging system where i want to get the last message from every conversation so i can show it in message notification panel, I have two tables
convo id are unique & so does username

convo_id|from_user | to_user | updated
========================================
1 | user1 | user6 | 1463311906
2 | user2 | user7 | 1463311906
3 | user8 | user1 | 1463311906
4 | user4 | user9 | 1463311906
5 | user5 | user10 | 1463311906


here is the chat table, it stores all messages * details of message

id |convo_id|from_user | to_user | updated | message
=========================================================
1 | 1 | user1 | user6 | 1463311906 | hello
2 | 2 | user1 | user6 | 1463311906 | hi
3 | 3 | user1 | user6 | 1463311906 | howdy
4 | 3 | user8 | user1 | 1463311906 | great
5 | 2 | user8 | user1 | 1463311906 | good


Now all i want is to get the last message of every conversation, for e.g
my username is "user1" and i have messaged "user6" so in chat table as you can see i have messaged user6 three times so i want to get the last message from those three message

and as you can see in conversation table user8 has message user1 two times which is me, so i want to get that last message also.
how can i achieve this?

here is what I've tried

SELECT
*
FROM
test_convo
WHERE
to_user = 'user1'
OR from_user = 'user1'


this is the result i got from convo table

'1', 'user1', 'user6', '1463311906', '0'
'2', 'user8', 'user1', '1463311906', '0'


As I'm using while loop how can i get last message of every conversation from chat table

Answer

You find the max ID per conversation ID which is the last message and then join that derived table with the chat table to get the message and any other info:

  select c.maxid as chatID,d.from_user,d.to_user,c.convo_id,d.message 
  from tblchat d 
  inner join  (select max(b.id) as maxID,b.convo_id  
  from tblconv a 
  inner join tblchat b 
  on a.convo_id=b.convo_id 
  group by b.convo_id) c 
  on c.maxID=d.id