StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL: Return only last message in flat/conversation message table
text
Body
copied!<p>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.</p> <p>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 <a href="https://stackoverflow.com/questions/3811750/get-the-last-entries-using-group-by">would be easy</a> except I don't know how to take into account both the "from_id" and "to_id" fileds.</p> <p>My messages table looks like this: <img src="https://i.stack.imgur.com/hq7cw.png" alt="messages table"></p> <p>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?</p> <p>Thanks for your help</p> <p>SQL to replicate the table:</p> <pre><code>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); </code></pre> <p>Edit: for those interested:</p> <pre><code>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`)); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload