How to get a list of chats by the date the messages were sent?

Question

SQL

There is a number of users and messages, how to request a list of users with which user 1 has either sent or received messages, so that the list is sorted by sent_at whether they are sent messages or received.

Thanks,

Answer 1

SELECT CASE WHEN sender_id = 1
            THEN receiver_id
            ELSE sender_id 
            END AS buddy
FROM message
WHERE 1 IN (sender_id, receiver_id)
GROUP BY buddy
ORDER BY MAX(sent_at)

Well and accordingly

SELECT user.username
FROM user
JOIN ( SELECT CASE WHEN sender_id = 1
                   THEN receiver_id
                   ELSE sender_id 
                   END AS id,
              MAX(sent_at) sent_at
       FROM message
       WHERE 1 IN (sender_id, receiver_id)
       GROUP BY 1 ) ids USING (id)
ORDER BY ids.sent_at