从每个对话sql获取最后一条消息

Question

I have a sql table which include conversation between users. I need to retrieve the last message from every conversation in order to preview it.

id | sender   | receiver     | message      | date
 1 |        1 |            2 | Hello        | 2015-12-08 20:00
 2 |        2 |            1 | Hey          | 2015-12-08 20:10
 3 |        2 |            1 | You there?   | 2015-12-08 21:00
 4 |        1 |            3 | Yes          | 2015-12-08 21:15
 5 |        4 |            1 | Hey buddy    | 2015-12-08 22:00

I know many similar question on site but i couldn't fix this.

I tried this code but not working properly:

SELECT *
FROM   messages
WHERE  receiver = '{$id}'
GROUP BY sender
ORDER BY id DESC
LIMIT 10;

Answer 1

Just to define what is a conversation in your table is a pain, i suppose a conversation is all the rows where

(sender=@senderId && receiver=@receiverId) || (sender=@receiverId && receiver=@senderId)

Group by this concept, i don't even want to think it

For me you are missing a concept, the "conversation"

If you have a table conversation like this

ConversationId |  Users1  |  User2

And Message like

Id | ConversationId | UserSendingId | Message | Date

Now you can Group by ConversationId and take the last message like

SELECT *  <-- avoid * better use all row names
FROM Message 
Where id in (
 select max(id) from message group by ConversationId
)

The representation of the conversation table is just a fast approach you can do a better solution with a relation from 1 to many of conversation and users in conversation to avoid modified conversation table when you want to have more than 2 users per conversation.