将消息分组为对话的 MySQL 查询

Question

我需要在用户的收件箱中将消息显示为对话。按发件人分组并显示最后一条消息(从发件人收到的最后一条消息或我对该发件人的最新回复)。

到目前为止，我当前的 MySQL 查询只显示所有收到的消息，没有任何分组。

这是我的数据库结构:

+-----------------------------------------------------------------------------------+
|                                  users_messages                                   |
+-----------------------------------------------------------------------------------+
| message_ID | from_id | to_id | date | subject | body | unread | delete1 | delete2 |
+-----------------------------------------------------------------------------------+

+---------------------+
|        users        |
+---------------------+
| 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
ORDER BY p.senddate DESC
LIMIT " . (($page - 1) * $PREFS->conf['per_page']) . ", " . $PREFS->conf['per_page']);

Answer 1

有点乱 - 但应该适合你:

SELECT 
    * 
FROM 
    user_messages 
WHERE 
    message_ID IN ( 
    SELECT 
        MAX(message_ID) 
    FROM 
        (SELECT 
            IF(m.from_id = <THE USER ID>, m.to_id, m.from_id) as other_user_id, 
            m.message_id 
        FROM 
            users_messages m 
        WHERE 
            m.from_id = <THE USER ID> OR m.to_id = <THE USER ID>) me 
    GROUP BY 
        other_user_id 
    ) 
ORDER BY 
    message_id DESC