There is a chat. How to display a list of conversations with the last received or sent message?

Question

PHP

Tell me, there is a chat. How to display a list of conversations with the last received or sent message? The base structure is as follows:

The principle is as follows: the bot sends a message to the user and vice versa.

I tried this, but it's not the right solution:

$viewLastMsg = R::findAll('chats', 'ORDER BY id DESC LIMIT 1');

foreach ($viewLastMsg as $vLM)
{
    if(
        ($vLM->sender_type == 'user' && $vLM->sender_id == $_SESSION['logged_user']->id && $vLM->recipient_id == $bot->id)
        ||
        ($vLM->sender_type == 'bot'  && $vLM->sender_id == $bot->id && $vLM->recipient_id == $_SESSION['logged_user']->id)
    )
    {
        echo $vLM->message;
    }
}

Tell me the solution, maybe how to make a selection correctly or push on the right path ..

Answer 1

Since you didn't specify a database, here's a solution for you, but not all window functions are supported =)

select distinct 
first_value(message) over (partition by LEAST(sender_id, recipient_id),GREATEST(sender_id, recipient_id)
                                   order by id desc)
from table

If window functions are not supported, it will look like this

select message
 from table
 where id in (select max(id) from table group by LEAST(sender_id, recipient_id),GREATEST(sender_id, recipient_id))

Well, or you can rewrite it through join, but I'm too lazy, I think you can do it ;-)

Answer 2

How to select only one row with desired values?