问题描述
这是我的问题,在我的收件箱中显示的是我仅从一个用户收到的多条消息.我需要像Facebook一样显示我的收件箱.每一位用户仅显示一条消息.很像对话视图.其中,我和用户之间的最后一条消息显示在收件箱中(他的最后一条消息或我最后给他的回复).
Here's my problem, in my inbox it shows multiple messages that I received from one user only. I need to to display my inbox just like facebook. Showing only one message per one user. Much like a conversation view. Wherein, the last message between me and a user is showed in the inbox (either his last message, or my last reply to him).
我尝试了GROUP BY,但结果不准确.一些最近的消息不会显示,也不会按上次对话的日期进行排序.
I have tried GROUP BY, but the results are not accurate. Some recent messages are not displayed and it is not sorted by date of last conversation.
这是我的数据库结构:
+-----------------------------------------------------------------------------------+
| users_messages |
+-----------------------------------------------------------------------------------+
| message_ID | sender |receiver| date | subject | body | unread | delete1 | delete2 |
+-----------------------------------------------------------------------------------+
+---------------------+
| members |
+---------------------+
| id | username | ...
+----+----------+-----+
这是我当前的查询:
$result = $DB->query("SELECT p.*, p.sender as sender, m.*
FROM " . DB_PREFIX . "messages p
LEFT JOIN " . DB_PREFIX . "members m ON p.sender=m.member_id
WHERE p.receiver='" . $SESSION->conf['member_id'] . "' AND delete2=0
GROUP BY p.sender ORDER BY p.senddate DESC
LIMIT " . (($page - 1) * $PREFS->conf['per_page']) . ", " . $PREFS->conf['per_page']);
推荐答案
SELECT t1.*, t2.*
FROM
users_messages AS t1
LEFT JOIN members AS t2
ON t1.sender = t2.id
LEFT JOIN users_messages AS t3
ON t1.sender = t3.sender AND t1.receiver = t3.receiver
AND t1.message_ID < t3.message_ID
WHERE (t1.sender = @userid OR t1.receiver = @userid) AND t3.message_ID IS NULL
我尚未测试,因此可能已进行了疏忽,但一个选择是您可以对发件人和收件人以及t1.message_ID<进行user_messages自加入. t3.message_ID.由于来自t1的一对消息之间的最新消息在t3中没有联接候选,而它是左联接,因此t3列将为空,您可以在where子句中选择该消息以获取最新消息.
I haven't tested so may have made an oversight but one option is you can do a user_messages self join on sender and reciver and t1.message_ID < t3.message_ID. Because the most recent message between a pair from t1 has no join candidates in t3 and it is a left join then the t3 columns will be null and you can select for this in the where clause to get the most recent.
这篇关于MySQL:在INBOX中每位用户显示最后一条消息的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!