How to make a request to select conversations from a simple message table between two users?

Question

MySQL

Not very strong in sql, but I would like to display dialogs on a separate page.
here is the actual structure of the table with messages

data for one dialog I choose like this

$messages = DB::table('messages')
            ->where([
                ['from_id', '=', $user_id],
                ['to_id', '=', $companion_id],
            ])
            ->orWhere([
                ['to_id', '=', $user_id],
                ['from_id', '=', $companion_id],
            ])
            ->get();

But I can't figure out how to select existing interlocutors for $user_id . That is, you need to get all the interlocutors to whom the user wrote or to whom the user wrote. If it is possible to get the last comment from these dialogues with one request, I will be very happy and grateful

Answer 1

You have a request when you know who wrote and to whom they wrote, now you need to make a request so that the known user ($user_id) is either the sender or the recipient. Those. just remove conditions from $companion_id