Reorder chat tabs so tabs with newest received or sent messages are on top of list

I’m working on a simple messenger.

This is how the Chat table looks like:
This is how the User table looks like:
This is how the PinnedUser table looks like:

By using this query I can display chat tabs with users I have pinned (pinned to the Messenger so I can chat with them).

SELECT pu.*, u.* FROM User u JOIN PinnedUser pu ON pu.pinned_user = u.user_id 
WHERE pu.pinned_by_user = $actual_user ORDER BY u.first_name ASC

Example of chat tabs:

When I click on one of those tabs, chat messages with that user are displayed.

Right now the tabs are ordered by users first name (you can see that in the query above). What I need to somehow do is, that I need to order tabs by newest messages.

So if “Anet” sends me a message (and I refresh the page because right now I don’t wanna do it with AJAX), that tab will appear on the top of the tab list. Then if “Demo” sends me a message (and I refresh the page because right now I don’t wanna do it with AJAX), “Demo” tab will be positioned on the top and “Anet” will be right below him. So I need the tabs to be ordered by “newest received or sent messages” just like Facebook has it in its messenger.

I tried to do the magic with GROUP BY but I failed.

Answer

You need to retrieve the date of the latest received message from each user – for this, we need to look up table chat. You can either join, or use a correlated subquery directly in the where clause:

select pu.*, u.* 
from user u 
join pinneduser pu on pu.pinned_user = u.user_id 
where u.user_id = ? 
order by (
    select coalesce(max(send_at), '200-01-01')
    from chat c
    where c.sender_id = pu.pinned_user_id and c.receiver_id = u.user_id
) desc

coalesce() is there in case there are no messages to be found, in which case the pinned user should be sorted last (this assumes that you don’t have messages before year 2k).

You might want to retrieve the message date as well, to display it in your application or else (in which case we don’t need a default value):

select 
    pu.*, 
    u.*,
    (
        select max(send_at)
        from chat c
        where c.sender_id = pu.pinned_user_id and c.receiver_id = u.user_id
    ) latest_message_sent_at
from user u 
join pinneduser pu on pu.pinned_user = u.user_id 
where u.user_id = ? 
order by (latest_message_sent_at is null), latest_message_sent_at desc