MySQL:仅返回平面/会话消息表中的最后一条消息 [英] MySQL: Return only last message in flat/conversation message table

问题描述

我正在编写一个像Facebook的新Messages系统这样的消息传递系统，其中两个用户之间的整个来回交互被视为一个对话.(与传统的电子邮件相反，传统的电子邮件中的每个答复都是单独的消息，或者与gMail的对话(答复中的所有答复都包含在一个对话中，但您仍然可以在人与人之间进行多次对话)).MySQL版本是5.0.92.

I'm writing a messaging system like Facebook's new Messages system, where the entire back and forth between two users is considered one conversation. (As opposed to traditional email where each reply is a separate message or gMail's conversations where replies are all together in a conversation but you can still have multiple conversations between people). MySQL version is 5.0.92.

我一辈子都无法弄清楚如何为收件箱"类型视图编写查询.我只需要两个人之间的最后一条消息，会很容易，但我不知道如何同时考虑"from_id"和"to_id"文件.

I can't for the life of me figure out how to write the query for the "inbox" type view. All I would need is the very last message between two people, which would be easy except I don't know how to take into account both the "from_id" and "to_id" fileds.

我的消息表如下所示:

My messages table looks like this:

突出显示的行是我想要返回的行(例如，由于用户42和43之间的最后一条消息是#8，因此不会返回#2).是否有可能做到这一点?还是我最好使用两个查询(一个用于to_id，一个用于from_id)，然后在PHP中对其进行处理?

The highlighted rows are the ones I'd like returned (#2 wouldn't be returned because the last message between users 42 and 43 is #8, for instance). Is it possible to do this? Or would I be better off using two queries (one for to_id and one for from_id) then working them out in PHP?

感谢您的帮助

SQL复制表:

CREATE TABLE `messages` (
  `message_id` bigint(20) NOT NULL auto_increment,
  `to_id` int(11) NOT NULL,
  `from_id` int(11) NOT NULL,
  `message_sent` datetime NOT NULL,
  `message_body` text NOT NULL,
  `is_read` tinyint(1) NOT NULL default '0' COMMENT '0 = no, 1 = yes',
  PRIMARY KEY  (`message_id`),
  KEY `to` (`to_id`),
  KEY `is_read` (`is_read`),
  KEY `sent` (`message_sent`),
  KEY `from` (`from_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

INSERT INTO `messages` (`message_id`, `to_id`, `from_id`, `message_sent`, `message_body`, `is_read`) VALUES
(1, 42, 43, '2011-04-01 11:54:05', 'message 1', 0),
(2, 43, 42, '2011-04-01 11:54:05', 'message 1.2', 0),
(3, 42, 44, '2011-04-01 11:55:05', 'message 2', 1),
(4, 44, 42, '2011-04-01 11:55:02', 'message 2.1', 0),
(5, 43, 44, '2011-04-01 15:05:42', 'Message 3', 0),
(6, 44, 43, '2011-04-01 15:05:58', 'Message 3.1', 0),
(7, 42, 43, '2011-04-02 11:54:05', 'message x', 0),
(8, 43, 42, '2011-04-02 11:54:05', 'message x.2', 0);

针对那些感兴趣的人:

select `m`.`message_id` AS `message_id`,`m`.`to_id` AS `to_id`,`ut`.`name` AS `to_name`,`m`.`from_id` AS `from_id`,`uf`.`name` AS `from_name`,`m`.`message_sent` AS `message_sent`,`m`.`message_body` AS `message_body`,`m`.`is_read` AS `is_read` from ((`messages` `m` join `users` `ut` on((`m`.`to_id` = `ut`.`id`))) join `users` `uf` on((`m`.`from_id` = `uf`.`id`))) where `m`.`message_id` in (select max(`messages`.`message_id`) AS `MAX(message_id)` from `messages` group by greatest(`messages`.`to_id`,`messages`.`from_id`),least(`messages`.`to_id`,`messages`.`from_id`));

推荐答案

SELECT MAX(message_id) FROM messages GROUP BY GREATEST(to_id, from_id), LEAST(to_id, from_id);

如果您想要消息本身，可以将其放在子选择中，或将其变成视图并与消息一起加入.

If you want the messages themselves you can put this in a subselect, or turn it into a view and join it with messages.

这篇关于MySQL:仅返回平面/会话消息表中的最后一条消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！