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
) 

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