Skyey Skyey - 4 months ago 19
MySQL Question

Get the last message from each conversation

I have table for messages which contains

id, sender_id, receiver_id, message and conversation_id


(I connected them by conversation_id, which they make by sending first message and if someone is replying, first he search conversation_id from messages where he is receiver and guy where is he replying is sender and by that send message with same conversation_id)

Now in messages list I want to output one just one last row per different
conversation_id
where
sender_id='$my_id' OR receiver_id='$my_id'


I am using
DISTINCT
but I get all rows always as output:

SELECT DISTINCT conversation_id, sender_id, message
FROM messages
WHERE receiver_id='$my_id'
ORDER BY id DESC

Answer

Please give the following query a try:

SELECT 
M.*
FROM messages M
INNER JOIN 
(
    SELECT 
    MAX(id) AS last_id_of_conversation,
    conversation_id
    FROM messages
    GROUP BY conversation_id
) AS t
ON M.id = last_id_of_conversation

Explanation:

SELECT 
 MAX(id) AS last_id_of_conversation,
 conversation_id
FROM messages
GROUP BY conversation_id;

This inner query will generate an output where there will be one row for each conversation_id along with the last id value (or max id) of the conversation.

Later make an inner join between your main table (messages) and the result returned by the inner query on matching id. I hope id is primary key. If so, then the above query ensures to bring a single row (more specifically the row having the last message) for each conversation.

EDIT:

SELECT 
M.*
FROM messages M
INNER JOIN 
(
    SELECT 
    MAX(id) AS last_id_of_conversation,
    conversation_id
    FROM messages
    WHERE sender_id = ? OR receiver_id = ?
    GROUP BY conversation_id
) AS t
ON M.id = last_id_of_conversation