get a list of users who I have contacted or they have contacted me-CodePudding

I'm working on messaging application (internal) and I would like to get a list of users who have contacted or I have contacted, just like facebook inbox ( on the left you see a list of users who contacted you or you have contacted )

I tried a lot but I couldn't success, what kind of relations I should use to get what I'm looking for, This is my migration

Schema::create('messages', function (Blueprint $table) {
            $table->increments('id');

            $table->unsignedBigInteger('sender_id');
            $table->unsignedBigInteger('receiver_id');

            $table->foreign('sender_id')->references('id')->on('users');
            $table->foreign('receiver_id')->references('id')->on('users');

            $table->text('message')->nullable();
            $table->timestamps();
        });

CodePudding user response：

One thing you could try is to use relationships in your User model.

public function sent_messages() {
        return $this->hasMany('App\Message','sender_id','id');
}

public function received_messages() {
        return $this->hasMany('App\Message','receiver_id','id');
}

Then get the messages with $user->sent_messages and $user->received_messages

CodePudding user response：

Consider this raw SQL query:

SELECT DISTINCT sender_id user_id
FROM messages
WHERE receiver_id = ?

UNION

SELECT DISTINCT receiver_id
FROM messages
WHERE sender_id = ?

If you substitute your user ID into both ? then you'll get a list of all users with who you ever communicated. Then you can easily form a list of chats or whatever you need.

UPD

Of course this solution isn't optimal. And it's fine only for low-loaded projects. If you need a really fast solution then you'll have to create a special caching table like chats (user1_id, user2_id) where you'll store unique pairs of (receiver_id, sender_id) and (sender_id, receiver_id) from messages. Then getting the list of chats will be as easy as SELECT user2_id FROM chats WHERE user1_id = ?

CodePudding user response：

This could be resolved in your blade view file once you've received the complete dataset.

In reference to the dataset received from your previously answered question:

I'm getting messages twice, duplicated

In your blade view file...

@foreach($chats as $chat)

@php($user = ($chat->from->id != \Auth::id()) ? $chat->from : $chat->to)

<span>{{$user->name}} - </span> <span>{{$chat->message}}</span>
<br />

@endforeach

Addendum

If in case you're only interested in the user list:

get a list of users who I have contacted or they have contacted me

In your Chat.php model, you could make use of a subquery...

// ...
    function contacts()
    {
        $authId = Auth::id();

        $contactIds = Chat::where('chats.from_id', $authId)->orWhere('chats.to_id', $authId)->select(["from_id", "to_id"])
            ->groupBy("from_id", "to_id")
            ->get()
            ->map(function ($user) use ($authId) {
                return ($user->from_id != $authId) ? $user->from_id : $user->to_id;
            })
            ->unique();

        return \App\Models\User::whereIntegerInRaw("id", $contactIds)
            ->get(["id", "name"]);
    }
// ...