MYSQL 在一条语句中进行多对多选择(聊天、最新消息、用户名)

Question

我试图在一个语句中选择用户的聊天列表，以及每个聊天的最新消息和消息的发件人姓名。

抽象问题是:一次聊天可以有多个参与用户。消息被发送到“聊天”，而不是特定用户。

这些是我想出的表格

TABLE users (id, username)
TABLE chats (id)
TABLE messages (id, message, chat_id, user_id)

最后是聊天和参与者之间的多对多关系表

TABLE chats_users (chat_id,user_id)

我想列出某个用户参与的所有聊天，以及最新消息及其发件人的用户名。我该如何在一份声明中解决这个问题？

我正在运行 Mysql 5.0.9 和 php 7。

Answer 1

此查询:

select max(m.id) id
from chats_users cu
inner join users u on cu.user_id = u.id
inner join messages m on m.chat_id = cu.chat_id
where cu.chat_id in (select chat_id from chats_users where user_id = ?) 
group by cu.chat_id

返回特定用户参与的所有聊天的最后一条消息。
在此查询中使用它:

select m.chat_id, m.message, u.username
from messages m inner join users u
on u.id = m.user_id
where m.id in ( 
  select max(m.id) id
  from chats_users cu
  inner join users u on cu.user_id = u.id
  inner join messages m on m.chat_id = cu.chat_id
  where cu.chat_id in (select chat_id from chats_users where user_id = ?) 
  group by cu.chat_id
) 

获取这些消息的详细信息。