mysql - 私有(private)聊天系统 - 获取每次聊天的最后一条消息

Question

我正在制作两个用户之间的聊天系统。我的聊天表看起来像这样:(这是架构: SQL Fiddle )

        --------------------------------------------------------------------------------
        | message_id | sender_id | receiver_id  | message_text  |       date           |
        --------------------------------------------------------------------------------
        |     1      |     20    |      100     |  Hello mate   |  2018-04-29 12:15:33 | 
        --------------------------------------------------------------------------------
        |     2      |    100    |      20      |   Hi there    |  2018-04-29 13:20:05 | 
        --------------------------------------------------------------------------------
        |     3      |     13    |      44      |   Alright     |  2018-04-29 14:35:15 | 
        --------------------------------------------------------------------------------
        |     4      |     20    |      57      |   Hi user 57  |  2018-04-29 16:44:34 | 
        --------------------------------------------------------------------------------

我想获取用户参与的每个聊天中最后一条消息的行，因此在上面的示例中，用户 20 应该获取 2 行: row of message_id 2 (因为他正在与用户 100 进行聊天，虽然他没有发送最后一条消息)，并且 message_id 4 行(因为他向用户 57 发送了消息)它基本上就像 WhatsApp，您总是可以在顶部看到任何聊天的第一条消息。

问题是，如果用户是发送者，我只能收到最后一条消息，而如果他是接收者，我只能收到最后一条消息:

 SELECT * FROM chat 
 WHERE message_id IN 
(SELECT MAX(message_id) FROM chat WHERE sender_id=:sender_id 
 GROUP BY receiver_id) ORDER BY date DESC

我认为我应该添加另一列“chat_id”，该列对于同一聊天中的每 2 个用户来说都是相同的数字(因此用户 100 和 20 无论是发送者还是接收者都将具有相同的 chat_id，并且在此示例中，用户 13 和 44 将有不同)，

或者，还有更好的选择吗？

Answer 1

您可以使用发送者和接收者之间的联合来获取两者的结果

  You could use 
  select * 
  from chat 
  inner join  (
    select max(message_id) max_id, user from (
    select message_id, sender_id as  user
    from chat
    union 
    select message_id, receiver_id 
    from chat) t 
    ) t1 on t1.max_id = chat.message_id
          and chat.sender_id=:sender_id