Как получить список чатов для разных пользователей?

Question

[Шлёпа Коммунистка]

Я разрабатываю приложения чата. У меня имеются следующие таблицы в моей базе данных MySQL:

USERS:
[ id, username, email ]

CHAT_ROOMS:
[id, created_at, updated_at ]

CHAT_ROOM_USER:
[ id, user_id, chat_room_id ]

MESSAGES:
[ id, sender_id, message, created_at, updated_at ]

Как мне вывести список чатов для каждого пользователя, где в качестве заголовка чата будет имя собеседника?
Я делаю такой запрос, он мне возращает последние сообщения из чатов каждого пользователя, но как мне выводить еще и username собеседника? Мне кажется это должен быть какой-то подзапрос?

SELECT chat_rooms.id,messages.message AS 'last_message', messages.created_at FROM chat_room_user JOIN chat_rooms ON chat_room_user.chat_room_id = chat_rooms.id JOIN users ON chat_room_user.user_id = users.id JOIN messages ON chat_room_user.chat_room_id = messages.chat_room_id WHERE users.id = $user_id AND messages.id IN (SELECT MAX(messages.id) FROM messages GROUP BY messages.chat_room_id) ORDER BY messages.created_at DESC;

Answer 1

[Шлёпа Коммунистка]

Кажется, этот запрос помог решить мою проблему

SELECT chat_rooms.id,
       messages.message AS 'last_message',
       messages.created_at,
       users.username --тут будет собеседник
FROM chat_room_user
         JOIN chat_rooms ON chat_room_user.chat_room_id = chat_rooms.id
         JOIN users ON chat_room_user.user_id = users.id
         JOIN messages ON chat_room_user.chat_room_id = messages.chat_room_id
WHERE users.id <> $user_id
  AND chat_rooms.id IN (SELECT chat_room_id FROM chat_room_user WHERE user_id = $user_id)
  AND messages.id IN (SELECT MAX(messages.id) FROM messages GROUP BY messages.chat_room_id)
ORDER BY messages.created_at DESC;

Answer 2

[101-s]

попробуй добавить после SELECT: users.username

Comments

[Шлёпа Коммунистка]

Не помогло, он мне выводит мой же username. А нужно выводить username тех, с кем у меня чат

[101-s]

тогда видимо я не понял твой вопрос: "вывести список чатов для каждого пользователя, где в качестве заголовка чата будет имя собеседника?"
если это список чатов для каждого пользователя, то значит для каждого пользователя должен быть отдельный массив с чатами. Так? И непонятно еще, какие поля из таблиц должны быть в результате.
chat_rooms.id, 'last_message', messages.created_at - вот эти поля нужно вывести или что-то еще?

[Шлёпа Коммунистка]

101-s, все верно, у каждого пользователя будет отдельный массив с чатами, отличающийся лишь названием чата (название чата это ИМЯ СОБЕСЕДНИКА).
chat_rooms.id, 'last_message', messages.created_at - да, эти поля нужны, еще нужно вывести имя собеседника, т.е нужен еще (наверное) подзапрос типа select users.username where chat_room_user.user_id <> current_user_id (id залогиненного пользователя)

[Шлёпа Коммунистка]

101-s, надеюсь я понятно выразился. Если же нет, то в кратце: как мне выводить список чатов для каждого пользователя, где в качестве имени чата будет имя собеседника? Имея такую структуру БД как я представил выше или же как-то ее изменить нужно?

[101-s]

вроде разобрался, если тебе нужно выводить тех юзеров, с кем у тебя чат, то нужно в таблицу messages добавить поля: id получателя сообщения и id чата
и далее, для каждого пользователя свой sql - запрос.
Возможно это будет два запроса, програмно потом получаешь нужный тебе массив