查询仅显示每个线程的最新消息 [英] Query to display the only the most recent message of each thread
问题描述
环境 PHP 5.3.5 phpMyAdmin 3.3.9
Environment PHP 5.3.5 phpMyAdmin 3.3.9
尝试创建过滤用户消息活动的查询.该活动将根据最近添加的消息显示最新的线程.所以我必须考虑用户是收件人还是发件人.
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.
这是捕获最后 10 条消息的正确顺序的查询和结果，但存在重复的 thread_id.我只想根据消息发送日期显示最近的线程.
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 查询:
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;
结果:
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
我想要的是这个:
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....
推荐答案
您的各种数据集、查询和结果似乎并不相互对应，因此有点难以理解，但我怀疑您在追求某些东西沿着这些路线...
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;
这篇关于查询仅显示每个线程的最新消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
