Only show latest message from each conversation

Question

I have a messaging system which has the tables "message" which just contains the "subject" then "message_user" which contains the message body, who sent it, who its for and whether its deleted / unread.

#Message Table

CREATE TABLE `message` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subject` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

INSERT INTO `message` (`id`, `subject`)
VALUES
    (1, 'Test'),
    (2, 'Test Again');

#Message User Table

CREATE TABLE `message_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `interlocutor` int(11) DEFAULT NULL,
  `body` text,
  `folder` enum('inbox','sent') NOT NULL,
  `starmark` tinyint(1) NOT NULL DEFAULT '0',
  `unread` tinyint(1) NOT NULL DEFAULT '1',
  `deleted` enum('none','trash','deleted') NOT NULL DEFAULT 'none',
  `date` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

INSERT INTO `message_user` (`id`, `message_id`, `user_id`, `interlocutor`, `body`, `folder`, `starmark`, `unread`, `deleted`, `date`)
VALUES
    (1, 1, 1, 2, 'Hi, how are you?', 'sent', 0, 1, 'none', '2018-10-23 09:36:02'),
    (2, 1, 2, 1, 'Hi, how are you?', 'inbox', 0, 1, 'none', '2018-10-23 09:36:02'),
    (3, 1, 2, 1, 'I am good thanks, you?', 'sent', 0, 1, 'none', '2018-10-23 09:46:02'),
    (4, 1, 1, 2, 'I am good thanks, you?', 'inbox', 0, 1, 'none', '2018-10-23 09:46:02'),
    (5, 2, 1, 3, 'Hi!', 'sent', 0, 1, 'none', '2018-10-23 09:50:22'),
    (6, 2, 3, 1, 'Hi!', 'inbox', 0, 1, 'none', '2018-10-23 09:50:22');

I wrote the following query:

SELECT
    *
FROM message m
JOIN message_user mu ON m.id = mu.message_id
WHERE mu.deleted = 'none'
    AND mu.user_id = 1 #user_id of person checking messages
ORDER BY mu.id DESC;

But this is currently returning 3 rows even though there is only two conversations. I tried to GROUP BY but it still showed 3 rows.

I would expect the first two rows in the above example not the last one.

I want the query to return a list of the conversations with the latest message which has been sent which I (user_id) am involved in.

Use ranking function (http://www.mysqltutorial.org/mysql-window-functions/mysql-rank-function/), or check this post if your version is too old : https://stackoverflow.com/questions/3333665/rank-function-in-mysql — DanB, Oct 23 '18 at 13:08
@MadhurBhaiya I get the error of "Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hidden.mu.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by" - I tried adding that column in GROUP BY too and the query runs but doesn't return only 1 row for each message. — Elliot Reeve, Oct 23 '18 at 13:23
Please edit your question to add relevant sample data showcasing your case. Ref: https://meta.stackoverflow.com/q/333952/2469308 — Madhur Bhaiya, Oct 23 '18 at 13:28
@ElliotReeve problem is that for a same message id , you have two rows having different folder value. Which one do you want to keep ? — Madhur Bhaiya, Oct 23 '18 at 13:39
@MadhurBhaiya - If my user_id = user_id then I want to keep "sent" as that means I was the last to reply. If user_id != user_id then "inbox" as it means the latest message is received. But folder is irrelevant as there should only be one message per "user_id" which is in the WHERE clause. — Elliot Reeve, Oct 23 '18 at 13:48

Madhur Bhaiya · Accepted Answer · 2018-10-23T14:39:05.060

1

Since your MySQL version is 8.0+, we can utilize Window functions, such as Row_number(); otherwise the solution would have been much verbose, using Session variables.
For a partition (group) of m.id, we will determine the row number values. Row number values will be ordered in descending order of date.
Now, we simply need to use this result-set as a Derived Table, and just consider those rows where row number value is 1.
Date is a keyword in MySQL. You should avoid naming column/table using it. Still if you have to do so, you will need to use backticks around it.

Try the following (DB Fiddle DEMO):

SELECT  dt.*
FROM (
      SELECT  m.id,
              m.subject,
              mu.id AS message_user_id,
              mu.message_id,
              mu.user_id,
              mu.interlocutor,
              mu.body,
              mu.folder,
              mu.starmark,
              mu.unread,
              mu.deleted,
              mu.`date`,
              Row_number()
                OVER (PARTITION BY m.id 
                      ORDER BY mu.`date` DESC) AS row_no
        FROM  message m
        JOIN  message_user mu
          ON  m.id = mu.message_id
        WHERE mu.deleted = 'none'
          AND mu.user_id = 1 ) AS dt
WHERE    dt.row_no = 1
ORDER BY dt.id DESC

edited Oct 23 '18 at 14:39

answered Oct 23 '18 at 14:00

Madhur Bhaiya

28,155
10
49
57

This is close but when I reply to a message it doesn't show that as the lastest body. If you look at the data, for message id 1 the body should show 'I am good thanks, you?' but it doesn't? – Elliot Reeve Oct 23 '18 at 14:30
@ElliotReeve should the latest row only appear for a message_id. Because your earlier comment suggested that `sent` should come before `inbox` values. – Madhur Bhaiya Oct 23 '18 at 14:38
1

This is perfect, thank you. I also updated date to created_date :) – Elliot Reeve Oct 23 '18 at 14:45
Sorry to be a pain but how would I show all inbox messages that are for user_id (no sent). With an column to show whether I have "replied" or not? Thanks – Elliot Reeve Oct 23 '18 at 15:06
@ElliotReeve Look at `Case .. When()` expressions – Madhur Bhaiya Oct 23 '18 at 16:18
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/182376/discussion-between-elliot-reeve-and-madhur-bhaiya). – Elliot Reeve Oct 23 '18 at 18:09

Mickaël Leger · Answer 2 · 2018-10-23T13:48:15.320

0

Try this :

select 
    m.id as id_message, m.subject as subject_message,
    mu.id as id_message_user, mu.interlocutor, mu.body, mu.folder, mu.starmark, mu.deleted, mu.date 
from message as m
inner join message_user as mu on mu.message_id = m.id and mu.deleted = 'none' and mu.user_id = 1
group by id_message
order by id_message_user desc

I removed

mu.user_id : it's in the inner join condition so always 'none'
mu.unread :same, always 1
mu.message_id : duplicate of id_message

http://sqlfiddle.com/#!9/91a5e4/15

edited Oct 23 '18 at 13:48

answered Oct 23 '18 at 13:45

Mickaël Leger

3,426
2
17
36

Thanks but I get an error: "Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hidden.mu.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by" – Elliot Reeve Oct 23 '18 at 13:46
You get this error with a copy/paste of this query? I added a Fiddle and I have no error with this :s – Mickaël Leger Oct 23 '18 at 13:49
Look strange for me since I had the same error and I corrected it in the fiddle using alias. About your error I find this : https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql – Mickaël Leger Oct 23 '18 at 13:56

Only show latest message from each conversation

2 Answers2