CODEWITHSUNDEEP
phpsql
Pradeep Mittal
Pradeep Mittal

Reputation: 593

Get only last chat message of whole friend list of a user from database

I need a query in sql which can get me the details of the last message of all the friends of the user .The fields in my sql database are :

message_id      (primary key)
sender          (username of the sender)
message_content (text of the message)
user_id         (user id of the app user)
friend_id       (user id of the app user's friends)
message_time    (time of the message receive on server)
receiver        (user id of the receiver)

Edit : So far I think my closest try was 

Select * from user_chat_messages where message_id on (Select distinct message_id from user_chat_messages order by message_time limit 1,0);

Rest all other queries that I tried were a total failure :(

Upvotes: 0

Views: 2775

Answers (1)

Jared Green
Jared Green

Reputation: 316

This is a very common mysql problem where you need to join a table on itself to get a result set with the min / max of each unique user.

Try something like this:

SELECT t1.* FROM table t1 
JOIN (
  SELECT MAX(message_time) AS 'time', friend_id, user_id 
  FROM table GROUP BY friend_id, user_id
) t2 ON (t1.message_time = t2.time AND t1.friend_id = t2.friend_id AND t1.user_id = t2.user_id)

Essentially your subquery finds the latest time grouped by friend_id and then joins it back to the main table so that it only pulls the record with the latest time from the first table. From there you can add a WHERE statement to only show the latest messages from a specific user - adding the condition in the subquery will actually improve the performance

SELECT t1.* FROM table t1 
JOIN (
  SELECT MAX(message_time) AS 'time', friend_id, user_id 
  FROM table 
  WHERE user_id=? GROUP BY friend_id
) t2 ON (t1.message_time = t2.time AND t1.friend_id = t2.friend_id  AND t1.user_id = t2.user_id)

Upvotes: 3

Related Questions