mysql - 从对话中选择最新消息

Question

我有两个表，conversation_tbl和conversation_reply_tbl

conversation_reply_tbl
-------------------------
|  conversation_reply_id|   //auto increment id      
|  reply_message        |   //message text
|  user_id              |   //the user who sent the message
|  datetime_sent        |   //date and time of message sent
|  status               |   //values '0' or '1' (read or not)
|  conversation_id      |   //relation to the conversation
-------------------------

conversation_tbl
-------------------------
|  conversation_id      |    //auto increment id     
|  from_user            |    //the user who sent the message
|  to_user              |    //the recipient
|  datetime_created     |    //date of the message created
|  gallery_id           |//the gallery where the message started from
-------------------------

现在，我使用 conversation_reply_tbl 来存储对话中进行的所有消息，并使用 conversation_tbl 来存储对话主要数据。

现在，我想选择用户参与的所有对话，但我想显示每个对话的最新消息，就像在“WhatsApp”或“Facebook”等著名网站和应用程序中所做的那样。

这可以在一个查询中完成吗？还是我必须使用 PHP 的函数？

Answer 1

以下是如何通过一次查询获取该信息:

select     t.*,
           last.*
from       conversation_tbl t
inner join (select   conversation_id, max(conversation_reply_id) conversation_reply_id
            from     conversation_reply_tbl
            group by conversation_id) all_lasts
        on all_lasts.conversation_id = t.conversation_id
inner join conversation_reply_tbl last
        on last.conversation_reply_id = all_lasts.conversation_reply_id
left join  (select   conversation_id 
            from     conversation_reply_tbl
            where    user_id = :the_user_id
            group by conversation_id) user_conv
        on user_conv.conversation_id = t.conversation_id
where      (     user_conv.conversation_id is not null
           or    t.to_user = :the_user_id )

请注意左连接，因为目标用户可能尚未回复消息，但它是起始消息的to_user。 where 子句对两种情况进行 or:如果消息线程有用户的消息，或者如果用户是发起消息的目标，则查询应该返回该对话。