Hitori Hitori - 11 days ago 7
MySQL Question

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

Answer

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
        ->orderBy('replies.created_at', 'desc');
}