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

Answer 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