Quite a typical scenario, but not easy to find the answer. You can easily order Eloquent results by some column, but what if you want to order by related column in child table? Most common example is a forum like Laracasts which shows topics in order by the latest post in that topic. How to do that?
First, this is our relationship in app\Topic.php:
public function posts()
{
return $this->hasMany(\App\Post::class);
}
Now, you need to realize that this wouldn’t work:
$topics = Topic::with('posts')->orderBy('posts.created_at')->get();
What we actually need to do – two things, actually:
1. Describe a separate relationship for the latest post in the topic:
public function latestPost()
{
return $this->hasOne(\App\Post::class)->latest();
}
2. And then, in our controller, we can do this “magic”:
$users = Topic::with('latestPost')->get()->sortByDesc('latestPost.created_at');
Let’s test it out – here’s our topics table.
Now, let’s show all users order by latest post.
If you look at the data, user ID 2 should come first with latest post on 27th day, then user ID 3 with post on 26th day, and then user ID 1 with post on 25th.
$users = User::with('latestPost')->get()->sortByDesc('latestPost.created_at');
foreach ($users as $user) {
echo $user->id . ' - ' . $user->latestPost->title . ' (' . $user->latestPost->created_at . ')
';
}
Result:
So, isn’t that sweet? You can read more about sortBy and sortByDesc methods here.
So weird, I was looking to solve this exact problem (in my case, it’s tags not topics, but I’m effectively using them as topics) and this post shows up in my RSS reader… Thumbs up and many thanks!
Great to hear, glad to help.
how about latest() ???
How to to this with pagination?
What if I want to get most post count instead of latest!
I meant to say that, I want to get topics sorted by which have most posts. Currently, I’m doing this by putting posts count in an array and then sort it.
Is there also a solution to get this result with one query call, without having to sort the collection afterwards?
It doesn’t work in all cases but you can
Model::where(‘column’, ‘someValue’)->(otherwheresetc)->join(‘relationship_table’, ‘model.id’, ‘=’, ‘relationship_table.model_id’)->orderBy(‘relationship_table.column_name’, ‘asc|desc’)->paginate();
The other problem is you know longer have the model in a model format that you have when using with…
An old post solving my current problem. I didn’t know this magic…. SortBy after ->get()…. Awesome, thanks!
pagination doesnt work with sort
This will not work correctly. If you have dates in different months it will sort only by month.
Solution is:
Topic::with(‘latestPost’)->get()->sortByDesc(function($topic) {
return strtotime($topic->latestPost->created_at);
});
This will sort by unix timestamp of ‘created_at’ field.
Hi Povilas, is this possible to do within a global query scope?
Not really, because this action is performed on a Collection Result, not on Eloquent model itself.
is this still work in laravel 6 i try it but it’s not working
$desc = Discussion::with(‘latestmsg’)
->where(‘user_id’, $user_id)
->orWhereHas(‘ad’, function (Builder $query) use ($user_id) { $query->where(‘user_id’, $user_id); })
->get()
->sortByDesc(‘latestmsg.msg_stamp’);