MySQL Get unique conversation and last messages ordered by date

Question

I'm making a messaging system. I want to retrieve specific user's conversations with content (this user can be both sender or receiver) ordered by date. I looked for every question online but none of them was working as expected. Here is example table data.

+-----+---------+-------+---------+------------+
| id  |from_user|to_user| content |   msg_date |
+-----+---------+-------+---------+------------+
|  1  |    5    |    2  | test1   | 2019-12-01 |
|  2  |    2    |    5  | test2   | 2019-12-02 |
|  3  |    2    |    7  | test3   | 2019-12-03 |
|  4  |    2    |    7  | test4   | 2019-12-04 |
|  5  |    5    |    2  | test5   | 2019-12-05 |
|  6  |    7    |    2  | test6   | 2019-12-06 |
|  7  |    7    |    2  | test7   | 2019-12-07 |
|  8  |    5    |    2  | test8   | 2019-12-08 |
+-----+---------+-------+---------+------------+

And here is what I expected. (Assume specific user id is 2)

+-----+---------+-------+---------+------------+
| id  |from_user|to_user| content |   msg_date |
+-----+---------+-------+---------+------------+
|  7  |    2    |    7  | test7   | 2019-12-07 |
|  8  |    5    |    2  | test8   | 2019-12-08 |
+-----+---------+-------+---------+------------+

Thanks

GMB · Accepted Answer · 2019-12-10T00:22:23.433

1

You can filter with a correlated subquery that pulls out the date of the latest message of each conversation:

select t.*
from mytable t
where 
    2 in (from_user, to_user)
    and t.msg_date = (
        select max(t1.msg_date)
        from mytable t1
        where 
            least(t1.from_user, t1.to_user) = least(t.from_user, t.to_user)
            and greatest(t1.from_user, t1.to_user) = greatest(t.from_user, t.to_user)
    )

Demo on DB Fiddle

id | from_user | to_user | content | msg_date  
-: | --------: | ------: | :------ | :---------
 7 |         7 |       2 | test7   | 2019-12-07
 8 |         5 |       2 | test8   | 2019-12-08

edited Dec 10 '19 at 00:22

answered Dec 10 '19 at 00:17

GMB

216,147
25
84
135

Thanks, works like charm. Now I need to understand logic behind this code. I've always worked with simple select, insert statements :) – kenarsuleyman Dec 10 '19 at 00:26
@kenarsuleyman: welcome! The correlated subquery pulls out the date of the latest message for each sender/receiver or receiver/sender tuple in the outer query. This date is used to filter in the outer query. – GMB Dec 10 '19 at 00:32

MySQL Get unique conversation and last messages ordered by date

1 Answers1