Mysql Query для комнаты чата. Составьте список всех комнат
Мне может понадобиться помощь с запросом mysql для комнатного чата.
Запрос должен найти все комнаты, к которым принадлежит пользователь, и упорядочить их по дате последнего сообщения, отправленного в комнату.
Только пользователи, которые являются членами комнаты, могут читать сообщения для комнаты.
Вот «не работающий» код, но, возможно, он помогает понять, что должен делать запрос:
Что должен делать запрос:
а) создать список всех комнат, в которые входит пользователь, по дате последнего сообщения
б) без дублирующих комнат (запрос ниже перечисляет комнаты несколько раз, если есть несколько сообщений с разными датами …)
c) результат должен содержать номер комнаты, имя комнаты и дату последнего сообщения из этой комнаты.
Примечание / подсказка: всегда будет существовать сообщение. После создания комнаты система добавит первое сообщение самостоятельно. (Сообщение 1 для каждой комнаты: «Комната создана на« ДАТА »).
Багги запрос:
SELECT DISTINCT cf.room_id, cf.date, cr.room_name
FROM cf_rooms_messages cf
INNER JOIN cf_rooms cr
ON cr.id = cf.room_id
WHERE EXISTS(
SELECT NULL
FROM cf_rooms_users
WHERE user_id = ?
AND room_id = cf.room_id
)
ORDER BY cf.date
DESC LIMIT 0,10
Tablelayouts:
/* this table keeps the room_id and the room_name */
CREATE TABLE IF NOT EXISTS `cf_rooms` (
`id` int(12) NOT NULL,
`room_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
/* this table keeps the messages submitted into the rooms */
CREATE TABLE IF NOT EXISTS `cf_rooms_messages` (
`id` int(12) NOT NULL,
`room_id` int(12) NOT NULL,
`sender_uid` int(12) NOT NULL,
`msg_text` text NOT NULL,
`date` int(12) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=12 ;
/* this table contains the members of the rooms */
CREATE TABLE IF NOT EXISTS `cf_rooms_users` (
`room_id` int(12) NOT NULL,
`user_id` int(12) NOT NULL,
`added_by_uid` int(12) NOT NULL,
`added_on` int(12) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Результат должен выглядеть так:
номер_ид | номер_имя | last_message_date
Спасибо за вашу помощь.
Решение
Вы можете сгруппировать результаты по пользователю и комнате и получить только последнюю (максимальную) дату из сообщений в «комнате». Затем вы просто заказываете результаты, чтобы показать номера по дате последнего сообщения.
Не проверено, но должно быть что-то вроде:
SELECT
a.`user_id` as `user_id`,
b.`room_name` as `room_name`,
MAX(c.`date`) as `last_msg_date`
FROM `cf_rooms_users` a
JOIN `cf_rooms_rooms` b
ON a.`room_id` = b.`id`
JOIN `cf_rooms_messages` c
ON a.`user_id` = c.`sender_uid`
WHERE a.`id` = this_user_id
GROUP BY a.`user_id`, b.`room_name`
ORDER BY MAX(c.`date`) DESC, b.`room_name`;
замещать this_user_id с идентификатором пользователя, которого вы хотите создать список.
Другие решения
Других решений пока нет …