CODEWITHSUNDEEP
mysqlsql
mcv
mcv

Reputation: 1460

Query to display the only the most recent message of each thread

Environment PHP 5.3.5 phpMyAdmin 3.3.9

Here's an link of the issue in sqlfiddle

Trying to create a query which filters through a user's message activity. The activity will display the most recent threads based upon message recently added. So I have to consider if the user is the recipient or sender.

I have a query structured but I am having great difficulty on grouping the threads. When I group the threads, the order of the most recent activity is lost.

Here is the query and results which captures the correct order of the last 10 messages but there are duplicate thread_ids. I want to only display the most recent thread based upon the message sent date.

SQL query:

SELECT m.date_sent, m.thread_id, m.message_id, m.sender_id,
  upub2.firstname as sender_name, mr.recipient_id,
  upub1.firstname as recipient_name
FROM message AS m
  JOIN message_recipient AS mr ON mr.message_id = m.message_id
  JOIN user_public_info AS upub1 ON upub1.user_public_info_id = mr.recipient_id
  join user_public_info AS upub2 ON upub2.user_public_info_id = m.sender_id
WHERE ((m.senderDelete IS NULL OR m.senderDelete = 0) AND m.sender_id = 2 ) OR
  ((mr.is_delete IS NULL OR mr.is_delete = 0 ) AND mr.recipient_id = 2)
ORDER BY m.message_id;

Results:

date_sent           thread_id   message_id  sender_id   sender_name     recipient_id    recipient_name
2013-10-09 14:31:50     106         113             1               John             2          Mark
2013-10-09 14:30:50     107         112             2           Mark             1          John
2013-10-09 14:30:31     106         111             2           Mark             1          John
2013-10-09 09:49:58     112         110             1           John             2          Mark
2013-10-09 09:20:24     108         106             1           John             2          Mark
2013-10-07 15:46:15     107         105             1           John             2          Mark
2013-10-07 14:40:25     103         104             1           John             2          Mark
2013-10-07 14:39:37     103         103             1           John             2          Mark
2013-10-07 14:36:34     107         102             2           Mark             1          John
2013-10-07 14:36:07     106         101             2           Mark             1          John
2013-10-07 14:35:29     105         100             2           Mark             1          John
2013-10-07 12:32:50     104         99          2           Mark             1          John
2013-10-07 12:15:43     104         98          2           Mark             1          John
2013-10-07 11:46:36     104         97          2       Mark             1          John
2013-10-07 11:43:32     104         96          1           John             2          Mark
2013-10-07 11:43:17     104         95          1           John             2          Mark
2013-10-07 11:27:14     103         94          1           John             2          Mark

What I want is this:

date_sent           thread_id   message_id  sender_id   sender_name     recipient_id    recipient_name
2013-10-09 14:31:50     106         113             1       John             2          Mark
2013-10-09 14:30:50     107         112             2           Mark             1          John
2013-10-09 09:49:58     112         110             1           John             2          Mark
2013-10-09 09:20:24     108         106             1           John             2          Mark
2013-10-07 14:40:25     103         104             1           John             2          Mark
2013-10-07 14:35:29     105         100             2           Mark             1          John
2013-10-07 12:32:50     104         99          2           Mark             1          John

Can this be done in one single query or should I create another query based upon the results of the first query?

Thank you for anyone that can help me solve this query....

Upvotes: 0

Views: 209

Answers (1)

Strawberry
Strawberry

Reputation: 33945

Your various data sets, queries, and results do not appear to correspod with one another so it's a little difficult to follow, but I suspect you're after something along these lines...

 SELECT m.message_id m_id
      , m.sender_id s_id
      , m.thread_id t_id
      , m.subject
      , m.message
      , m.date_sent
      , s.firstname sender
      , r.firstname recipient
   FROM message m
   JOIN message_recipient n
     ON n.message_id = m.message_id
   JOIN user_public_info s
     ON s.user_public_info_id = m.sender_id
   JOIN user_public_info r
     ON r.user_public_info_id = n.recipient_id
   JOIN (SELECT thread_id, MAX(max_message_id) max_message_id FROM message GROUP BY thread_id)x
     ON x.thread_id = m.thread_id AND x.max_message_id = m.message_id;

Upvotes: 1

Related Questions