Level 75
The last answer here gives a good example http://stackoverflow.com/questions/25700529/laravel-eloquent-how-to-order-results-of-related-models
Of course instead of get you can still paginate if needed.
2 likes
Mar 13, 2017
3
Level 3
Order by on relationship field
How can I order a result set by something on its relationship?
I am trying to get the Eloquent equivalent of this:
SELECT * FROM users INNER JOIN roles ON users.role_id = roles.id ORDER BY roles.label DESC
Here is what I'm trying (based on the documentation):
$order = 'desc';
$users = User::with(['role' => function ($q) use ($order) {
$q->orderBy('label', $order);
}])->paginate(10);
but it doesn't order them properly. What am I doing wrong?
EDIT: Not sure if showing the model relationship is relevant but here it is:
public function role()
{
return $this->belongsTo(Role::class);
}
Level 3
I found the solution for anyone that needs the answer.
You have to use the join() method to sort the entire collection (instead of the eager loading I was trying which just orders the relationship.)
$order = 'desc';
$users = User::join('roles', 'users.role_id', '=', 'roles.id')->orderBy('roles.label', $order)->select('users.*')->paginate(10);
34 likes
Level 1
When you use the with method the Eloquent makes another query in database to retrieve the related data using whereIn. This is why we can't order by a relation field.
For example, when you make a query using $users = User::with('role')->get() the Eloquent makes two queries in database:
SELECT * FROM users;
SELECT * FROM roles WHERE id IN (1, 2, 3, 4, whatever...);
To do so, we must need to use the join method as @getupkid said on his answer.
4 likes
Please or to participate in this conversation.