laravel eloquent sort by relationship

问题:

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');
}

EDIT:

回答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();

回答2:

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');
}