Laravel eloquent sort by relationship

Question

I have 3 models

• User
• Channel
• To answer

model relationship

• 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 more recent answers than channel-1

Now, I want to order a user’s channel according to its current channel response. just like a chat application. How can I order a custom channel this way?

• channel 2
• channel 1

I already tried some codes. but nothing happens

 // 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 got the expected result, but it will not download the entire channel if there is no answer.

 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:

Answer 1

To my knowledge, the method with impatience of loading with starts the second request. That's why you cannot achieve what you want with a reliable with download .

I think the join method combined with the relationship method is a solution. The following solution is fully tested and works 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(); } 

You can then call $user->sortedChannels('desc') to get a list of channels by editing the created_at attribute.

For a channel type condition (which may or may not have answers), simply use the 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 the groupBy method to the query, you should pay particular attention to your orderBy . Because of the nature of Sql , the Group By clause is executed first before the Order By clause. For more on this issue, see this question on the stack .

So, if you add the groupBy method, you should use the orderByRaw method and should be implemented as follows.

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

Answer 2

First, you don’t need to specify a pivot table name if you follow the Laravel naming convention so that your code looks a bit cleaner:

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

Secondly, you will need to explicitly call join to achieve the result in a single request:

 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 3

Inside your channel class, you need to create this hasOne relation (you hasMany answers , but it has hasOne last answer ):

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

Now you can get all the channels ordered by the last answer, for example like this:

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

To get all the channels from the user, sorted by the latest answer, you will need this method:

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

where channels() defined as:

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

Answer 4

If you have a hasOne() relation, you can sort all the entries by doing:

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

This sorts all the channel entries by the most recent answers (assuming you only have one answer per channel.) This is not your case, but someone might look for something similar (like me).