mysql - 从对话中获取最后一条消息回复

Question

所以我有两个表chats和 chats_reply 。结构如下。

聊天

--------------------------------------------------
| chat_id  |  user_one | user_two  | created_at
--------------------------------------------------
| 1        | 1         | 2         | something here
--------------------------------------------------

聊天回复

-------------------------------------------------------------------------
| chatReply_id |  chat_id | user_id    | reply          | created_at
-------------------------------------------------------------------------
| 1            | 1         | 1         | Message 1      | something here
-------------------------------------------------------------------------
| 2            | 1         | 2         | Message 2      | something here
-------------------------------------------------------------------------

我的查询遇到了一些问题。假设我的 user_id 是 1。我想返回包含最后发送的消息的所有聊天列表。我已经有列出所有聊天的查询，但它不返回最后一条消息，而是返回对话的第一条消息。这是我的查询:

SELECT 
       chats.chat_id,
       chats.created_at AS ChatTime,
       chats_reply.reply,
       chats_reply.created_at AS ReplyTime,
       chats_reply.status,
       users.name,
       users.last_name,
       users.email

FROM chats

INNER JOIN chats_reply
ON chats.chat_id = chats_reply.chat_id

INNER JOIN users
ON users.user_id = 
    CASE
        WHEN chats.user_one = '1'
            THEN chats.user_two 
        WHEN chats.user_two = '1'
            THEN chats.user_one   
     END

WHERE chats.user_one = '1' OR chats.user_two = '1'

GROUP BY chats_reply.chat_id

ORDER BY chats_reply.chatReply_id DESC

此查询返回我期望的所有内容，问题是它返回 Message 1来自chats_reply当我希望它返回 Message 2 时。非常感谢任何帮助。

Answer 1

使用WHERE进行过滤。不是GROUP BY:

SELECT c.chat_id, c.created_at AS ChatTime,
       cr.reply, cr.created_at AS ReplyTime, cr.status,
       u.name, u.last_name, u.email
FROM chats c INNER JOIN
     chats_reply cr
     ON c.chat_id = cr.chat_id INNER JOIN
     users u
     ON (u.user_id = c.user_two AND c.user_one = 1) OR
        (u.user_id = c.user_one AND c.user_two = 1)
WHERE 1 IN (c.user_one, c.user_two) AND
      cr.chatReply_id = (SELECT MAX(cr2.chatReply_id)
                         FROM chat_reply cr2
                         WHERE cr2.chat_id = cr.chat_id
                        );