CODEWITHSUNDEEP
mysqlsql
Sangit
Sangit

Reputation: 159

MYSQL Many-to-many select (chats, latest message, username) in one statement

I'm trying to select a list of the user's chats in one statement, together with each chats latest message and the massage's sender's name.

The abstract problem is: Ever chat can have multiple participating users. A message is sent to a "chat", not a specific user.

These are the tables I came up with

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

And lastly a many-to-many relation table between chats and participants

TABLE chats_users (chat_id,user_id)

I want to list all the chats a certain user participates in, together with the latest message and it's sender's username. How would I go about that in one statement?

Im running Mysql 5.0.9 and php 7.

Upvotes: 0

Views: 236

Answers (1)

forpas
forpas

Reputation: 164139

This query:

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

returns the last messages of all the chats that a certain user participates.
Use it in this query:

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
)

to get the details of these messages.

Upvotes: 1

Related Questions