php - 按照他们发送或接收的最新消息的顺序选择人员

Question

我正在尝试制作一个聊天系统。它有 2 个表(聊天，用户)，具有以下架构。聊天 table

用户表

我想根据任意两个用户之间的最新消息选择一个人与之聊天的所有用户。
例子:
假设我有一个名为 X 的用户，他/她已经与 A、B、C、D 聊天。我必须首先从聊天表中找到 A、B、C、D(即与 X 聊天的所有用户)。然后对于每个用户A、B、C、D，找到他们从X发送或接收的最新消息的ChatDateTime，并根据它对A、B、C、D进行排序。

聊天表如下所示:

+--------+------------+----------+----------+--------------+
| ChatId | FromUserId | ToUserId | ChatText | ChatDateTime |
+--------+------------+----------+----------+--------------+
| 1      | 2          | 3        | hai      | 12:30        |
| 2      | 3          | 2        | hello    | 12:34        |
| 3      | 3          | 2        | I am X   | 12:38        |
| 4      | 1          | 3        | I am A   | 12:40        |
| 5      | 2          | 3        | I am B   | 12:41        |
| 6      | 4          | 3        | I am C   | 12:42        |
| 7      | 5          | 3        | I am D   | 12:44        |
| 8      | 3          | 4        | Hai 'C'  | 12:50        |
+--------+------------+----------+----------+--------------+

这里的 UserId 是 A=1,B=2,X=3,C=4,D=5。从表中，我们了解到X(UserId=3)已经与A、B、C、D(应该先选择)聊天。最近的对话发生在 C 和 X (ChatId-8) 之间。所以第一个结果应该是 C。然后是 D(ChatId-7)，然后是 B(ChatId-5)，最后是 A(ChatId-4)。所以选择的顺序应该是C、D、B、A。

Answer 1

请尝试以下...

SELECT latestChatDateTimeFinder.UserID AS UserID,
       latestChatDateTimeFinder.UserName AS UserName,
       latestChatDateTime AS latestChatDateTime,
       otherUserID AS otherUserID,
       User.UserName AS otherUserName
FROM ( SELECT User.UserID AS UserID,
              User.UserName AS UserName,
              CASE
                  WHEN User.UserID = Chat.FromUserID THEN
                      Chat.ToUserID
                  ELSE
                      Chat.FromUserID
              END AS otherUserID,
              MAX( ChatDateTime ) AS latestChatDateTime
       FROM User
       JOIN Chat ON User.UserID = Chat.FromUserID
                 OR User.UserID = Chat.ToUserID
       GROUP BY User.UserID,
                otherUserID
     ) AS latestChatDateTimeFinder
JOIN User ON latestChatDateTimeFinder.otherUserID = User.UserID
ORDER BY latestChatDateTimeFinder.UserID,
         otherUserID;

这条语句以下面的子查询开始...

SELECT User.UserID AS UserID,
       User.UserName AS UserName,
       CASE
           WHEN User.UserID = Chat.FromUserID THEN
               Chat.ToUserID
           ELSE
               Chat.FromUserID
       END AS otherUserID,
       MAX( ChatDateTime ) AS latestChatDateTime
FROM User
JOIN Chat ON User.UserID = Chat.FromUserID
          OR User.UserID = Chat.ToUserID
GROUP BY User.UserID,
         otherUserID

此子查询根据 FromUserID 或 在 User 和 Chat 之间执行 INNER JOIN ToUserID 与 UserID 共享一个值。

然后选择字段。 CASE 语句将根据 JOIN 建立的共享值选择另一个 UserID。

然后，生成的数据集按两个 UserID 值分组，并通过 MAX() 函数选择对应于这两个值组合的最近日期。

这将为我们提供每个用户和他们与之通信的每个其他用户的列表，以及最近通信的日期时间。

然后根据现在识别的另一个用户的 UserID 值将此列表加入主语句中的 User 以允许将该用户的名称附加到数据集。

然后从数据集中选择字段，给定别名并排序。

如果您有任何问题或意见，请随时发表相应的评论。

附录

要将结果限制为一个 User 和他们与之对应的所有用户，请尝试...

SELECT latestChatDateTimeFinder.UserID AS UserID,
       latestChatDateTimeFinder.UserName AS UserName,
       latestChatDateTime AS latestChatDateTime,
       otherUserID AS otherUserID,
       User.UserName AS otherUserName
FROM ( SELECT User.UserID AS UserID,
              User.UserName AS UserName,
              CASE
                  WHEN User.UserID = Chat.FromUserID THEN
                      Chat.ToUserID
                  ELSE
                      Chat.FromUserID
              END AS otherUserID,
              MAX( ChatDateTime ) AS latestChatDateTime
       FROM User
       JOIN Chat ON ( User.UserID = Chat.FromUserID OR
                      User.UserID = Chat.ToUserID )
                AND User.UserID = targetUser
       GROUP BY otherUserID
     ) AS latestChatDateTimeFinder
JOIN User ON latestChatDateTimeFinder.otherUserID = User.UserID
ORDER BY otherUserID;

……或者……

SELECT latestChatDateTimeFinder.UserID AS UserID,
       latestChatDateTimeFinder.UserName AS UserName,
       latestChatDateTime AS latestChatDateTime,
       otherUserID AS otherUserID,
       User.UserName AS otherUserName
FROM ( SELECT User.UserID AS UserID,
              User.UserName AS UserName,
              CASE
                  WHEN User.UserID = Chat.FromUserID THEN
                      Chat.ToUserID
                  ELSE
                      Chat.FromUserID
              END AS otherUserID,
              MAX( ChatDateTime ) AS latestChatDateTime
       FROM User
       JOIN Chat ON User.UserID = targetUser
                AND User.UserID = targetUser
       GROUP BY otherUserID
     ) AS latestChatDateTimeFinder
JOIN User ON latestChatDateTimeFinder.otherUserID = User.UserID
ORDER BY otherUserID;

请注意，targetUser(用于指代相关User 的值)可以是显式值或变量。