MySQL – Querying for Latest Messages in Flat Reply System

MySQL

I've created a message system schema in MySQL as follows. It consists of two tables (messages, recipients). 

`Message_ID` int(11) NOT NULL AUTO_INCREMENT,
`Message_Sender_ID` int(11) NOT NULL,
`Message_Sent_Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Message_Subject` varchar(255) NOT NULL,
`Message_Text` text NOT NULL,
`Message_Sender_Keep` boolean DEFAULT TRUE,
`Message_Root_ID` int(11),
PRIMARY KEY (`Message_ID`)

`Recipient_Message_ID` int(11) NOT NULL,
`Recipient_User_ID` int(11) NOT NULL,
`Recipient_Keep` boolean DEFAULT TRUE,
`Recipient_Read_Date` timestamp DEFAULT NULL,
PRIMARY KEY (`Recipient_Message_ID`, `Recipient_User_ID`)

Here's how it works. When a user creates a new message, the message's root_id is set to it's own id. When someone replies, the root id is set to the root message of that thread. The recipient information is also stored in the recipients table.

What I'm stuck on is querying the latest message of each unique thread. Instead, it's returning all of the unique threads, but only the root messages (I want the newest ones).

SELECT
  M.*,
  R.*
FROM messages M INNER JOIN recipients R
  ON M.Message_ID = R.Recipient_Message_ID
WHERE (R.Recipient_User_ID = $user_id OR M.Message_Sender_ID = $user_id) 
GROUP BY M.Message_Root_ID HAVING MIN(M.Message_Sent_Time)
ORDER BY M.Message_Sent_Time DESC

I've been trying to figure this out for myself, but I've just looked at it for way too long. Anyone able to help me on this?

Best Answer

You can find the latest sent message in each thread and then join that with your tables:

select M.*, R.*
FROM messages M 
JOIN recipients R
    ON M.Message_ID = R.Recipient_Message_ID
JOIN (
    select Message_Root_ID, max(Message_Sent_Time) as Message_Sent_Time
    from messages
    group by Message_Root_ID
) as X
    ON M.Message_root_id = X.Message_root_id
   AND M.Message_Sent_Time = X.Message_Sent_Time
Related Question