mysql - 从消息表中查询对话

Question

我的消息表格式如下:

+------------------------------------------------------------------+
| id|sender_id| recipient_id | message_text |created_at            |
+------------------------------------------------------------------+
| 1 | 2       |  10          | "test1"      |"2017-04-10 09:05:45" |
| 2 | 10      |  2           | "test2"      |"2017-04-10 09:05:47" |
| 3 | 2       |  4           | "test3"      |"2017-04-10 09:05:49" |
| 4 | 10      |  4           | "test4"      |"2017-04-10 09:05:51" |
| 5 | 4       |  2           | "test5"      |"2017-04-10 09:05:53" |
| 6 | 2       |  10          | "test6"      |"2017-04-10 09:05:58" |
+------------------------------------------------------------------+

我想做的是获取登录用户(比如 ID 为 2 的用户)的所有“对话”，以及该对话的最后一条消息。我设法使用此查询提取用户 user2 的 id 有消息:

select distinct users.id 
from messages, users where 
(recipient_id = 2 and users.id = sender_id) 
or 
(sender_id = 2 and users.id = recipient_id);

结果是

4
10

因为 user2 已经发送和/或接收了来自这两个人的消息(test1、test2、test6 用于 10，以及 test3、test5 为 4)。

我不能做的是修改这个查询，所以它也产生发送到或由产生的 id 接收的最后一条消息 - 例如

4  | test5
10 | test6

Answer 1

如果我对您的要求理解正确，您希望获取涉及特定用户的每个对话的最新消息日期。在这种情况下，我们可以汇总给定用户的对话并保留最近的消息日期。

SELECT m1.*
FROM messages m1
INNER JOIN
(
    SELECT LEAST(sender_id, recipient_id)    AS first,
           GREATEST(sender_id, recipient_id) AS second,
           MAX(created_at) AS recent_date
    FROM messages
    WHERE sender_id = 2 OR recipient_id = 2
    GROUP BY LEAST(sender_id, recipient_id),
             GREATEST(sender_id, recipient_id)
) m2
    ON LEAST(m1.sender_id, m1.recipient_id)    = m2.first AND
       GREATEST(m1.sender_id, m1.recipient_id) = m2.second AND
       m1.created_at = m2.recent_date

输出:

解释:

此查询中的挑战是找到一种方法将两个用户之间的对话分组在一起。我使用了 LEAST/GREATEST 技巧，这是一种我们可以将 2 -> 4 和 4 -> 2 对话视为逻辑上是一样的。然后，使用 GROUP BY，我们可以确定那对对话用户的最近对话日期。因此，我上面的答案中的子查询会为每一对用户找到该对及其最近的对话日期，而不考虑任何顺序。然后，我们将此结果加入到 messages 表中，以引入实际的最新消息文本。

此处演示:

Rextester