私人聊天系统-获取每次聊天的最后一条消息

s5a0g9ez 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(314)

我正在两个用户之间建立一个聊天系统。我有一张这样的聊天桌：（这是schema: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行：消息行\u id 2（因为他参与了与用户100的聊天，尽管他没有发送最后一条消息），第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\u id”，这将是一个数字，对于处于同一个chat中的每2个用户都是相同的（因此用户100和20将具有相同的chat\u id，无论他们是发送者还是接收者，在这个示例中，用户13和44将具有不同的chat\u id），
或者，有更好的选择吗？

sql mysql

来源：https://stackoverflow.com/questions/50086092/private-chat-system-get-last-message-of-each-chat

2条答案

按热度按时间

mkshixfv1#

要有效地处理此问题，请从以下两个查询开始：

select message_id, sender_id as other_id
from messages
where receiver_id = :user
order by message_id desc;

select message_id, receiver_id as other_id
from messages
where sender_id = :user
order by message_id desc;

可以使用两个索引对每个索引进行优化： messages(receiver_id, sender_id, message_id) 以及 messages(sender_id, receiver_id, message_id) .
你可以 union all 将它们放在一起并进行排序或聚合以获得最大值：

select m.*
from messages m
where m.message_id in (select max(message_id)
                       from ((select message_id, sender_id as other_id
                              from messages
                              where receiver_id = :user
                              order by message_id desc
                              limit 1
                             ) union all   
                             (select message_id, receiver_id as other_id
                              from messages
                              where sender_id = :user
                              order by message_id desc
                              limit 1
                             )
                            ) mm
                      );

子查询应该能够使用索引（实际上，不需要“otherid”）。这个 max() 然后在两排 in 应该能够利用上的索引 (message_id) .
编辑：
对于修改后的问题，我想不出一个真正有效的方法。这里有一种方法：

select m.*
from messages m
where m.message_id in (select max(m2.message_id)
                       from messages m2
                       where :user in (sender_id, receiver_id)
                       group by (case when sender_id = :user then receiver_id else sender_id end)
                      );

赞(0）回复(0）举报 2021-06-21

qgelzfjb2#

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

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

赞(0）回复(0）举报 2021-06-21

我来回答

私人聊天系统-获取每次聊天的最后一条消息

2条答案

相关问题

热门标签

最新问答