Retrieve last message between multiple users and display it in inbox
Hello friends today I am going to tell you how to display last conversation in Inbox. For example: In whatsapp chat list we can see the last conversation of each user. Here also we will display last conversation from the chat list.
Below you can see the chat list of A with multiple users. This is the message table below.
id
from_user
to_user
message
date_time
1
A
B
Hello
2016-08-06 11:55
2
B
A
Hi
2016-08-06 11:56
3
A
B
How are you
2016-08-06 12:00
4
B
A
I am good thanks
2016-08-06 12:05
5
C
A
I am coming in 5 minutes
2016-08-06 12:15
6
A
C
Ok
2016-08-06 12:16
Instead of writing code for it and to make it much more easier I have added one more column message_token, which will help to group last conversation by time.
id
from_user
to_user
message
message_token
date_time
1
A
B
Hello
dsfdf343242435sdfsdfsd
2016-08-06 11:55
2
B
A
Hi
dsfdf343242435sdfsdfsd
2016-08-06 11:56
3
A
B
How are you
dsfdf343242435sdfsdfsd
2016-08-06 12:00
4
B
A
I am good thanks
dsfdf343242435sdfsdfsd
2016-08-06 12:05
5
C
A
I am coming in 5 min.
nbfgiuyewq6gf4534sdgh
2016-08-06 12:15
6
A
C
Ok
nbfgiuyewq6gf4534sdgh
2016-08-06 12:16
Now you can get the last conversation easily by writing the simple query below:
SELECT * FROM (SELECT message_token, MAX(created) AS created FROM messages GROUP BY message_token) AS x JOIN messages USING (message_token, created) where (from_user ='A' OR to_user = 'A') order by date_time desc
0 Comment(s)