0

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?

3 Answers 3

0

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
1
  • It works wonderfully. Thank you so much for your help. This query was driving me crazy!
    – detective6
    Jun 26, 2014 at 18:31
0

You are using MIN(M.Message_Sent_Time), that's the first message, hence only the root messages ....

try MAX(M.Message_Sent_Time)

1
  • Oops, that was a typo. My code has Max, not Min.
    – detective6
    Jun 26, 2014 at 18:30
0

Please try the following query:

SELECT
  M.*,
  R.*
FROM messages M INNER JOIN recipients R
  ON M.Message_ID = R.Recipient_Message_ID
WHERE (R.Recipient_User_ID = 3 OR M.Message_Sender_ID = 3) 
AND M.Message_ID NOT IN (SELECT Message_Root_ID FROM messages)
GROUP BY M.Message_Root_ID 
HAVING MAX(M.Message_Sent_Time)
ORDER BY M.Message_Sent_Time DESC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.