php - 基于房间的聊天系统的 Mysql 查询。建立所有房间的列表

Question

我可能需要一些关于基于房间的聊天应用程序的 mysql 查询的帮助。

查询需要查找用户所属的所有房间，并按最后一条消息提交到房间的日期对它们进行排序。

只有属于聊天室成员的用户才能阅读该聊天室的消息。

这是一个“不起作用”的代码，但也许它有助于了解查询应该做什么:

查询应该做什么:a) 创建用户所属的所有房间的列表，按最后一条消息的日期排序b) 没有重复的房间(如果有多条不同日期的消息，下面的查询会多次列出房间...)c) 结果应包含 room_id、room_name 以及来自该房间的最后一条消息的日期。

注意/提示:总会存在一条消息。创建房间后，系统将自行添加第一条消息。 (每个房间的消息 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

表格布局:

/* 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;

结果应如下所示:

房间ID |房间名称 |最后消息日期

感谢您的帮助。

Answer 1

您可以按用户和房间对结果进行分组，并仅从“房间”中的消息中获取最后(最大)日期。然后，您只需对结果进行排序，即可按最后一条消息日期显示房间。

未经测试，但应该是这样的:

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 替换为您要为其创建列表的用户的 ID。