Get Last Message from Each Conversation 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.

Answer 2

I think if you want to identify a conversation for a particular user, you will need to select rows where they are either the sender or the receiver.

Then to get the most recent message from the conversation, you can group by whichever one of sender/receiver the current user is not, then select the maximum ID.

SELECT * FROM messages
WHERE id IN (
    SELECT MAX(id) AS last_msg_id 
    FROM messages WHERE receiver = ? OR sender = ? 
    GROUP BY IF(sender = ?, receiver, sender)
)

I don't think this query will perform very well, though. I agree with the other answer that it would be easier to query for conversations if conversations were defined in your database.

Answer 3

E.g.:

SELECT x.* 
  FROM my_table x
  JOIN 
     ( SELECT LEAST(sender,receiver) user1
            , GREATEST(sender,receiver) user2
            , MAX(date) date 
         FROM my_table 
        GROUP 
           BY user1
            , user2
     ) y
    ON LEAST(sender,receiver) = user1
   AND GREATEST(sender,receiver) = user2
   AND y.date = x.date;

Answer 4

SELECT m.id, m.added_date, m.message, u.username, u.image, m.from_id, m.to_id
FROM tbp_registration AS u 
LEFT JOIN tbp_chats AS m ON m.from_id = u.id 
WHERE m.id IN ( 
  SELECT MAX(id) 
  FROM tbp_chats 
  WHERE from_id = '$user_id' OR to_id = '$user_id' 
  GROUP BY LEAST(from_id, to_id), GREATEST(from_id, to_id)
)