I have been searching a lot, but probably I don't know how to ask the question properly.
I'm storing all chat messages between all users in table called 'user_messages'
which looks like this:
message_from_user_id | message_to_user_id | message_content | message_date
1 | 2 | "hey" | 07:36
1 | 2 | "how are u?" | 07:37
2 | 1 | "hey, im fine" | 07:38
3 | 1 | "wassup" | 09:21
4 | 2 | "wow" | 11:55
5 | 1 | "example" | 5:34
Now let's say I'm the user with id 1. And I want to display my latest chats with all people (something like messenger).
The result I would like to achieve is:
message_from_user_id | message_to_user_id | message_content | message_date
3 | 1 | "wassup" | 09:21
2 | 1 | "hey, im fine" | 07:38
5 | 1 | "example" | 5:34
So basically I need to select all messages where message_from_user_id = 1
or message_to_user_id = 1
but how can I make that the only one latest result for every chat will be displayed?
Even in this scenario:
message_from_user_id | message_to_user_id | message_content | message_date
1 | 2 | "hey" | 07:36
1 | 2 | "how are u?" | 07:37
2 | 1 | "hey, im fine" | 07:38
I want to get only one result that would be this:
2 | 1 | "hey, im fine" | 07:38
I'm using MySQL and PHP.
One method uses window functions:
select um.*
from (select um.*,
row_number() over (partition by coalesce(nullif(message_from_user_id, 1), message_to_user_id)
order by message_date desc
) as seqnum
from user_messages um
where 1 in (message_from_user_id, message_to_user_id)
) um
where seqnum = 1;
The expression:
coalesce(nullif(message_from_user_id, 1), message_to_user_id)
Is just a more concise way of writing:
(case when message_from_user_id <> 1
then message_to_user_id
else message_from_user_id
end)