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;
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments