List conversation between 2 users in the correct order

Question

I want to create a chat system on which i could list all the chats between specific 2 persons

I have 2 tables users and chats

my chats table have 3 columns - user_id, friend_id and chat

my User.php model file is like this

public function chats() {
    return $this->hasMany('App\Chat');
}

For eg:

I want to list all the chat between user 1 and 3 without changing the order of the conversation
I can simply do it by doing $chats = Auth::user()->chats->where('friend_id', '=', $id); but this will only give the authenticated (which is user 1 or 3) users chats. But I want the conversation between both of them.
So I have found an alternate way to do that by

$first = Chat::all()->where('user_id', '=', Auth::user()->id)->where('friend_id', '=', $id);
$second = Chat::all()->where('user_id', '=', $id)->where('friend_id', '=', Auth::user()->id);
$chats = $first->merge($second);

But this way has some problems. This will not get the chats in the correct order. I think it is impossible to order it correctly.

So my question is how can I list the conversation between two persons in the correct order easily?
If you want more details about my problem you can just ask.

Answer 1

You should be able to do it in one query with parameter grouping, rather than executing two separate queries and then merging them.

Chat::where(function ($query) use ($id) {
    $query->where('user_id', '=', Auth::user()->id)
          ->where('friend_id', '=', $id);
})->orWhere(function ($query) use ($id) {
    $query->where('user_id', '=', $id)
          ->where('friend_id', '=', Auth::user()->id);
})->get();

This might also return your results in the correct order, just because without any sort criteria specified, databases will often return rows in the order they were inserted. However, without adding something to your chat table to sort by, (either a timestamp or an autoincrement id), there's no way to guarantee it.

Answer 2

Try like this

$first = Chat::all()->where('user_id', '=', Auth::user()->id)
         ->where('friend_id', '=', $id)->get();
$second = Chat::all()->where('user_id', '=', $id)
          ->where('friend_id', '=', Auth::user()
          ->id)->get();
$chats = $first->merge($second)
         ->sortBy('created_at');//created_at is timing added change if other

Answer 3

First of all, you should not do all() before filtering. This is bad because fetches all the table data and then does the filtering in PHP.

You should consider doing this:

In your migration:

Schema::create("chat", function (Blueprint $table) {
    //Other creation lines
    $table->timestamps();
})

Then in your chat model:

public function scopeInvolvingUsers($query, $userId,$friendId) {
     return $query->where([ ["user_id",$userId],["friend_id",$friendId] ])
                  ->orWhere([ ["user_id",$friendId],["friend_id",$userId] ]);
} 

Then you can do the following:

$chats = Chat::involvingUsers(\Auth::id(),$otherId)->latest()->get();

Note that latest or earliest requires the timestamps to be present on the table.

Answer 4

I will add timestamps in chat table which will ensure the order. To add timestamp into chat table just add

$table->timestamps();

and the you can select the chat related to the user and sort it by created_at. In laravel 5.3+ use

Chats::where(['user_id', '=', Auth::id()], ['friend_id', '=', $id])->orWhere(['user_id', '=', $id], ['friend_id', '=', Auth::id()])->sortBy('created_at');