With comments this time, and substituting "submit_id" for "id".
Code: Select all
SELECT tn3.*
/* tn1 will find the most recent note for every submit_id */
FROM tracker_notes tn1
/* tn2 looks up notes from the same submit_id that are more recent. we'll only keep the results where there aren't any */
LEFT JOIN tracker_notes tn2 ON tn1.submit_id = tn2.submit_id AND (tn1.timestamp < tn2.timestamp OR tn1.timestamp = tn2.timestamp AND tn1.submit_id < tn2.submit_id)
/* tn3 finds all notes for that submit_id */
JOIN tracker_notes tn3 ON tn1.submit_id = tn3.submit_id
/* only include results where the tn1 note is the most recent */
WHERE tn2.submit_id IS NULL
/* sort by the most recent note's timestamp, then by the timestamp of each note individually */
ORDER BY tn1.timestamp DESC, tn3.timestamp DESC
I'm unsure of the performance on this though. Can you post an EXPLAIN of this (same query but with "EXPLAIN" at the beginning) as well as a "SHOW CREATE TABLE tracker_notes"?
For your query:
What you've posted isn't valid: there's a stray WHERE in the middle of everything. Did you mean this?
Code: Select all
SELECT tn1.*
FROM tracker_notes tn1
LEFT JOIN tracker_notes tn2 ON tn1.submit_id = tn2.submit_id AND tn1.timestamp < tn2.timestamp
WHERE tn1.recipient = 1 AND tn2.submit_id IS NULL
ORDER BY tn1.timestamp DESC
That query won't return results when the recipient does not have the most recent note. In that case,
1. The query will find everything from each submit_id (as tn1)
2. Grab the most recent note (by using tn2 to filter out the less recent tn1-s)
3. See that the recipient of that note (tn1) is not #1
4. Discard the entire result
We need to adjust the JOIN a bit more. Currently it says to "find the most recent note in the conversation" but now we actually need it to say "find the most recent note in the conversation
that the user received". In general, what you have in the WHERE regarding tn1 also needs to be applied to tn2 in the JOIN. Specifically, you want to show only what the user has received so you should only consider recent-ness for notes that the user has received.
Code: Select all
/* find a note: from the conversation, that is more recent than what we've seen so far, that the user has received */
LEFT JOIN tracker_notes tn2 ON tn1.submit_id = tn2.submit_id AND tn1.timestamp < tn2.timestamp AND tn2.recipient = 1
Updating the query at the top of this post,
Code: Select all
LEFT JOIN tracker_notes tn2 ON tn1.submit_id = tn2.submit_id AND (tn1.timestamp < tn2.timestamp OR tn1.timestamp = tn2.timestamp AND tn1.submit_id < tn2.submit_id) AND tn2.recipient = 1