MySQL get last message for each conversation

Question

I have big problem with mysql because Im frontend developer and Im very poor with SQL ^^

I have 3 tables in database + user table

participants:

+----+---------+-----------------+---------------------+
| id | user_id | conversation_id |          updated_at |
+----+---------+-----------------+---------------------+
|  1 |       1 |               1 | 2022-01-03 00:00:00 |
|  2 |       2 |               1 | 2022-01-03 00:00:00 |
|  3 |       1 |               2 | 2022-01-03 00:00:00 |
|  4 |       3 |               2 | 2022-01-03 00:00:00 |
|  5 |       1 |               2 | 2022-01-03 00:00:00 |
+----+---------+-----------------+---------------------+

conversations:

+----+---------------------+
| id |          updated_at |
+----+---------------------+
|  1 | 2022-01-03 00:00:00 |
|  2 | 2022-01-03 00:00:00 |
+----+---------------------+

messages:

+----+----------------+-----------------+-------------------+----------------------+
| id | participant_id | conversation_id |           message |           updated_at |
+----+----------------+-----------------+-------------------+----------------------+
|  1 |              1 |               1 | test msg 1 room 1 |  2022-01-03 00:00:00 |
|  1 |              1 |               1 | test msg 2 room 1 |  2022-01-05 00:00:00 |
|  1 |              2 |               1 | test msg 3 room 1 |  2022-01-07 00:00:00 |
|  1 |              3 |               2 | test msg 1 room 2 |  2022-01-03 00:00:00 |
|  1 |              2 |               2 | test msg 2 room 2 |  2022-01-04 00:00:00 |
|  1 |              1 |               2 | test msg 3 room 2 |  2022-01-05 00:00:00 |
|  1 |              1 |               1 | test msg 4 room 1 |  2022-01-14 00:00:00 |
+----+----------------+-----------------+-------------------+----------------------+

So I'm trying to get last message from each rooms but I have problem, when I'm trying to group by then I have error like this

Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'zzzz' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Its possible to get last messages by each room? like this

+-----------------+-------------------+----------------------+
| conversation_id |           message |           updated_at |
+-----------------+-------------------+----------------------+
|               2 | test msg 3 room 2 |  2022-01-05 00:00:00 |
|               1 | test msg 4 room 1 |  2022-01-14 00:00:00 |

I tried something like this, but not working...

WITH RECURSIVE test AS (
    SELECT
        pp.user_id,
        pp.conversation_id,
        pp.updated_at
    FROM
        participants pp
    UNION
        SELECT
            p.user_id,
            p.conversation_id,
            p.updated_at
        FROM
            participants p
        INNER JOIN conversations c ON c.id = p.conversation_id
        
) SELECT
    m.conversation_id,
    m.message,
    m.updated_at
FROM
    test t
    
INNER JOIN messages as m ON  m.conversation_id = t.conversation_id 
ORDER BY m.updated_at DESC
;

Does this answer your question? [Retrieving the last record in each group - MySQL](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql) — Stu, Jul 16 '22 at 09:31
so currently I need to make 2 queries, first to get all conversation to user and participants `SELECT pp.conversation_id, pp.user_id FROM participants as p LEFT JOIN participants as pp ON p.conversation_id = pp.conversation_id AND pp.user_id <> 1 WHERE p.user_id = 1;` then for each conversation get last message... `SELECT * FROM messages as m where m.conversation_id = 1 order by m.updated_at desc limit 1;` its probably wrong answer... — axlpl, Jul 16 '22 at 11:05
May I ask what kind of application you're developing? Because it looks like some kind of chat application. In which case you really don't want to rely on a short polling based system like MySQL. The number of queries going around per second will pretty soon slow your system down up to the point that it becomes unusable. You probably want to look at using a websocket instead. Basically loading the DB data into the server's RAM when you fire up the socket and only perform INSERT statements while it's running. That way you'll have persistent data without the problem of your server slowing down. — icecub, Jul 16 '22 at 20:33

score 0 · Answer 1 · answered Jul 16 '22 at 20:22

If I understand your question correctly. Everything you need can come from the message table. so you could try something like this:

WITH cte as(
SELECT
       RIGHT (message,LENGTH(message)-LOCATE('r',message)) AS room,
       MAX(LEFT (message,LENGTH(message)-LOCATE('m',message))) AS msg,
       MAX(updated_at) AS updated_at
FROM messages
group by room
)

select * 
FROM messages
WHERE RIGHT (message,LENGTH(message)-LOCATE('r',message)) in (select room from cte)
AND   LEFT (message,LENGTH(message)-LOCATE('m',message)) in (select msg from cte)
AND   updated_at in (select updated_at from cte)

db fiddle

Let me know if I misunderstood anything.

MySQL get last message for each conversation

1 Answers1