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

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;

UPD

Результат, который я получаю сейчас согласно моему запросу, однако в поле title находится имя текущего пользователя, а нужно имя собеседника. Например, если у пользователя user_1 есть чат с user_2 и чат с user_3, то в поле title мне нужно получить именно user_2 и user_3, т.е имена моих собеседников а не мое имя. Надеюсь я достаточно подробно описал мою проблему

"chat_rooms": [
        {
            "id": 5,
            "title": "user_1",
            "message_id": 24,
            "last_message": "Hello",
            "updated_at": null,
            "deleted_at": null
        },
        {
            "id": 1,
            "title": "user_1",
            "message_id": 21,
            "last_message": "Голосовое сообщение",
            "updated_at": "2022-07-27 15:50:03",
            "deleted_at": null
        }
    ]

Answer 1

Если пользователей всегда два, то можно его выводить вместо текущего

в подзапросе получаем список чатов для текущего пользователя chat_rooms.id IN
поскольку в чате всего 2 пользователя, то исключаем текущего и останется второй пользователь чата, делаем через <> users.id <> $user_id

Отредактированный запрос:

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;

БЛОГ НА HUSL

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

Ответы (1 шт):