Кажется, этот запрос помог решить мою проблему
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;