laravel eloquent sort by relationship

Question

I have 3 models

• User
• Channel
• Reply

model relations

• user have belongsToMany('App\Channel');
• channel have hasMany('App\Reply', 'channel_id', 'id')->oldest();

let's say i have 2 channels

• channel-1
• channel-2

channel-2 has latest replies than channel-1

now, i want to order the user's channel by its channel's current reply. just like some chat application. how can i order the user's channel just like this?

• channel-2
• channel-1

i already tried some codes. but nothing happen

// User Model
public function channels()
    {
        return $this->belongsToMany('App\Channel', 'channel_user')
                    ->withPivot('is_approved')
                    ->with(['replies'])
                    ->orderBy('replies.created_at'); // error
    
    }
// also
public function channels()
    {
        return $this->belongsToMany('App\Channel', 'channel_user')
                    ->withPivot('is_approved')
                    ->with(['replies' => function($qry) {
                        $qry->latest();
                    }]);
    }
// but i did not get the expected result

EDIT also, i tried this. yes i did get the expected result but it would not load all channel if there's no reply.

public function channels()
{
    return $this->belongsToMany('App\Channel')
                ->withPivot('is_approved')
                ->join('replies', 'replies.channel_id', '=', 'channels.id')
                ->groupBy('replies.channel_id')
                ->orderBy('replies.created_at', 'ASC');
}

Answer 1

According to my knowledge, eager load with method run 2nd query. That's why you can't achieve what you want with eager loading with method.

I think use join method in combination with relationship method is the solution. The following solution is fully tested and work well.

// In User Model
public function channels()
{
    return $this->belongsToMany('App\Channel', 'channel_user')
        ->withPivot('is_approved');
}

public function sortedChannels($orderBy)
{
    return $this->channels()
            ->join('replies', 'replies.channel_id', '=', 'channel.id')
            ->orderBy('replies.created_at', $orderBy)
            ->get();
}

Then you can call $user->sortedChannels('desc') to get the list of channels order by replies created_at attribute.

For condition like channels (which may or may not have replies), just use leftJoin method.

public function sortedChannels($orderBy)
    {
        return $this->channels()
                ->leftJoin('replies', 'channel.id', '=', 'replies.channel_id')
                ->orderBy('replies.created_at', $orderBy)
                ->get();
    }

Edit:

If you want to add groupBy method to the query, you have to pay special attention to your orderBy clause. Because in Sql nature, Group By clause run first before Order By clause. See detail this problem at this stackoverflow question.

So if you add groupBy method, you have to use orderByRaw method and should be implemented like the following.

return $this->channels()
                ->leftJoin('replies', 'channels.id', '=', 'replies.channel_id')
                ->groupBy(['channels.id'])
                ->orderByRaw('max(replies.created_at) desc')
                ->get();

Answer 2

Inside your channel class you need to create this hasOne relation (you channel hasMany replies, but it hasOne latest reply):

public function latestReply()
{
    return $this->hasOne(\App\Reply)->latest();
}

You can now get all channels ordered by latest reply like this:

Channel::with('latestReply')->get()->sortByDesc('latestReply.created_at');

To get all channels from the user ordered by latest reply you would need that method:

public function getChannelsOrderdByLatestReply()
{
    return $this->channels()->with('latestReply')->get()->sortByDesc('latestReply.created_at');
}

where channels() is given by:

public function channels()
{
    return $this->belongsToMany('App\Channel');
}

Answer 3

Firstly, you don't have to specify the name of the pivot table if you follow Laravel's naming convention so your code looks a bit cleaner:

public function channels()
{
    return $this->belongsToMany('App\Channel') ...

Secondly, you'd have to call join explicitly to achieve the result in one query:

public function channels()
{
    return $this->belongsToMany(Channel::class) // a bit more clean
        ->withPivot('is_approved')
        ->leftJoin('replies', 'replies.channel_id', '=', 'channels.id') // channels.id
        ->groupBy('replies.channel_id')
        ->orderBy('replies.created_at', 'desc');
}

Answer 4

If you have a hasOne() relationship, you can sort all the records by doing:

$results = Channel::with('reply')
                   ->join('replies', 'channels.replay_id', '=', 'replies.id')
                   ->orderBy('replies.created_at', 'desc')
                   ->paginate(10);

This sorts all the channels records by the newest replies (assuming you have only one reply per channel.) This is not your case, but someone may be looking for something like this (as I was.)