Eloquent eager load Order by
Solution 1
Try this:
Student::with(array('exam' => function($query) {
$query->orderBy('result', 'DESC');
}))
->get();
Solution 2
If you need to order your students collection by the result column, you will need to join the tables.
Student::with('exam')
->join('exam', 'students.id', '=', 'exam.student_id')
->orderBy('exam.result', 'DESC')
->get()
In this case, assuming you have a column student_id
and your exams table are named exam
.
Solution 3
If you ALWAYS want it sorted by exam result, you can add the sortBy call directly in the relationship function on the model.
public function exam() {
return this->hasMany(Exam::class)->orderBy('result');
}
(credit for this answer goes to pfriendly - he answered it here: How to sort an Eloquent subquery)
Solution 4
tl;dr
Student::with('exam')->get()->sortByDesc('exam.result');
This will sort the results of the query after eager loading using collection methods and not by a MySQL ORDER BY
.
Explanation
When you eager load you can't use an ORDER BY
on the loaded relations because those will be requested and assembled as a result of a second query. As you can see it in the Laravel documentation eager loading happens in 2 query.
If you want to use MySQL's ORDER BY
you have to join the related tables.
As a workaround, you can run your query and sort the resulting collection with sortBy
, sortByDesc
or even sort
. This solution has advantages and disadvantages over the join solution:
Advantages:
- You keep Eloquent functionality.
- Shorter and more intuitive code.
Disadvantages:
- Sorting will be done by PHP instead of the database engine.
- You can sort only by a single column, unless you provide a custom closure for the sorter functions.
- If you need only a part of the ordered results of a query (e.g.
ORDER BY
withLIMIT
), you have to fetch everything, order it, then filter the ordered result, otherwise you will end up with only the filtered part being ordered (ordering will not consider the filtered out elements). So this solution is only acceptable when you would work on the whole data set anyway or the overhead is not a problem.
Solution 5
This worked for me:
$query = Student::select(['id','name']);
$query->has('exam')->with(['exam' => function ($query) {
return $query->orderBy('result','ASC');
}]);
return $query->get();
Andrius
Updated on July 05, 2022Comments
-
Andrius almost 2 years
I have problem with eloquent query. I am using eager loading (one to one Relationship) to get 'student' With the 'exam', Using the code below.
Student::with('exam')->orderBy('exam.result', 'DESC')->get()
And i want to order received rows by the 'result' column in 'exam'. I am using
->orderBy('exam.result', 'DESC')
But it is not working. Any ideas how to do it ?
-
John P over 10 yearsFor those of you still struggling with laravel 3 - order by eager loaded relationship is not working. In my case I could use static ordering and used order_by in the model instead.
-
Gary Green over 9 yearsBeware of doing sorting this way, as it will only sort the eager loaded models, not all the results as a whole.
-
mloureiro over 8 yearsThis would only sort the ones that were returned, so if you have 100 and only getting the 10 first you wont/might not get the desired ones
-
ClearBoth over 7 yearswhy I would make a join plus eager load at the same time? must be another way to order by the query from the eager loaded one. I'm stuck!
-
Luis Dalmolin over 7 yearsThe join you are making to order the results. The eager loading are for performance purposes. If you can order the results after getting them from the database, you could not making the join and then order the collection (laravel.com/docs/5.2/collections#method-sortby).
-
Miguel Stevens over 7 yearsHow would you perform a sort on all results as a whole then?
-
Riliwan Balogun over 7 yearsThis works for me until I appended
->paginate()
to the query -
Riliwan Balogun over 7 years@GaryGreen How would you achieve sorting on resulted query
-
Luis Dalmolin over 7 years@riliwanrabo try to add a ->select('students.*') in the beginning of the query.
-
Nick about 7 years@Notflip and riliwanrabo and an orderBy before the get() method call.
-
Pierre over 5 yearsGreat answer for what I needed. Thanks
-
Charles Wood about 4 years@GaryGreen Is your comment still relevant to the edited answer?
-
mohammad asghari almost 4 yearsthis is good answer but it's work if you only use get() function. If you use paginate() sort is only done on current page not total result.
-
BRose over 3 years@LuisDalmolin can you please check my question, its similar but I tried your approach stackoverflow.com/questions/63348113/… unfortunately I still hit an error
SQLSTATE[42S02]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'backorders'. (SQL: select count(*) as aggregate from [IV00102] inner join [backorders] on [items].[ITEMNMBR] = [backorders].[ITEMNMBR])
-
francisco over 2 yearsThis apply for ever. And when you don't want to order by result?