Eloquent eager load Order by

Question

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 ?

I believe there may be some confusion as to what constitutes a one-to-one relationship, as a one-to-one should not need to be eager loaded. If a student has lots of exams (which I am assuming is the case since you want to order them), you will need to update your relating function from `hasOne()` to `hasMany()`. With that in mind, Glad To Help's answer should be correct. — user1669496, Sep 18 '13 at 19:42

Glad To Help · Answer 1 · 2013-09-18T01:55:13.060

86

Try this:

Student::with(array('exam' => function($query) {
        $query->orderBy('result', 'DESC');
    }))
    ->get();

edited Sep 18 '13 at 01:55

answered Sep 18 '13 at 01:49

Glad To Help

5,299
4
38
56

For 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. – John P Jan 16 '14 at 06:14
21

Beware of doing sorting this way, as it will only sort the eager loaded models, not all the results as a whole. – Gary Green Sep 05 '14 at 08:53
3

How would you perform a sort on all results as a whole then? – Miguel Stevens Aug 28 '16 at 14:53
@GaryGreen How would you achieve sorting on resulted query – Riliwan Balogun Jan 20 '17 at 19:26
@Notflip and riliwanrabo and an orderBy before the get() method call. – Nick Mar 14 '17 at 18:24
@GaryGreen Is your comment still relevant to the edited answer? – Charles Wood Apr 01 '20 at 15:15

score 55 · Answer 2 · edited Jan 31 '14 at 15:13

55

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.

edited Jan 31 '14 at 15:13

MrCasual

153
1
1
3

answered Sep 18 '13 at 20:57

Luis Dalmolin

3,416
1
18
24

2

why 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! – ClearBoth Aug 18 '16 at 12:46
The 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 (https://laravel.com/docs/5.2/collections#method-sortby). – Luis Dalmolin Aug 18 '16 at 18:36
1

This works for me until I appended `->paginate()` to the query – Riliwan Balogun Jan 20 '17 at 12:39
1

@riliwanrabo try to add a ->select('students.*') in the beginning of the query. – Luis Dalmolin Jan 30 '17 at 16:58
@LuisDalmolin can you please check my question, its similar but I tried your approach https://stackoverflow.com/questions/63348113/is-there-a-way-to-select-fields-from-an-eager-loaded-table-in-laravel/63348560?noredirect=1#comment112043654_63348560 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])` – BRose Aug 11 '20 at 18:18

score 44 · Answer 3 · answered Feb 25 '18 at 12:26

44

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)

answered Feb 25 '18 at 12:26

rosell.dk

2,228
25
15

This apply for ever. And when you don't want to order by result? – francisco Dec 10 '21 at 14:05
It applies for the hasMany-relationship from the student-model. If you list the exams from an ExamController::index for example, you must apply the sortBy again (or re-specify it otherwise) – nJGL Jul 29 '22 at 08:31

score 31 · Answer 4 · edited Jun 20 '20 at 09:12

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 with LIMIT), 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.

this 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. — mohammad asghari, Jul 16 '20 at 05:36

score 22 · Answer 5 · edited Jun 04 '17 at 15:10

22

This worked for me:

$query = Student::select(['id','name']);


    $query->has('exam')->with(['exam' => function ($query) {
        return $query->orderBy('result','ASC');
    }]);


    return $query->get();

edited Jun 04 '17 at 15:10

HDJEMAI

9,436
46
67
93

answered Jun 04 '17 at 14:47

Guru

327
3
7

score 0 · Answer 6 · answered May 16 '17 at 10:02

You could use \Illuminate\Database\Eloquent\Relations\Relation and query scopes to add far column through relationship, I wrote a traits for this, it misses HasOne o HasMany but having BelongsTo and BelongsToMany could easily adapted

Also the method could be enhanced to support more than depth 1 for multiple chained relationship, I made room for that

<?php
/**
 * User: matteo.orefice
 * Date: 16/05/2017
 * Time: 10:54
 */


use Illuminate\Database\Eloquent\Relations\BelongsToMany;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Eloquent\Builder;


trait WithFarColumnsTrait
{

    public function scopeWithFarColumns(Builder $query , $relationPath , $columns , $tableAliasPrefix = null)
    {
        $relationPath = array_wrap($relationPath);
        $tableAliasPrefix = $tableAliasPrefix ?: WithFarColumnsTrait::randomStringAlpha(3);
        $currentModel = $this;

        $subQueries = [];
        $relationIndex = 0;
        foreach ($relationPath as $relationName) {
            if (method_exists($currentModel , $relationName)) {
                $relation = $currentModel->$relationName();
            } else {
                throw new BadMethodCallException("Relationship $relationName does not exist, cannot join.");
            }
            $currentTable = $currentModel->getTable();
            if ($relationIndex == 0) {
                $query->addSelect($currentTable . '.*');
            }
            $relatedModel = $relation->getRelated();
            /**
             * @var string
             */
            $relatedTable = $relatedModel->getTable();

            if ($relation instanceof BelongsTo) {
                foreach ($columns as $alias => $column) {
                    $tableAlias = $tableAliasPrefix . $relationIndex;
                    $tableAndAlias = $relatedTable . ' AS ' . $tableAlias;
                    /**
                     * Al momento gestisce soltanto la prima relazione
                     * todo: navigare le far relationships e creare delle join composte
                     */
                    if (!isset($subQueries[$alias])) {
                        $subQueries[$alias] = $currentQuery = DB::query()
                            ->from($tableAndAlias)
                            ->whereColumn(
                                $relation->getQualifiedForeignKey() , // 'child-table.fk-column'
                                '=' ,
                                $tableAlias . '.' . $relation->getOwnerKey()  // 'parent-table.id-column'
                            )
                            ->select($tableAlias . '.' . $column);
                        // se la colonna ha una chiave stringa e' un alias
                        /**
                         * todo: in caso di relazioni multiple aggiungere solo per la piu lontana
                         */
                        if (is_string($alias)) {
                            $query->selectSub($currentQuery , $alias);
                        } else {
                            throw new \InvalidArgumentException('Columns must be an associative array');
                        }
                    } 
                    else {
                        throw new \Exception('Multiple relation chain not implemented yet');
                    }
                } // end foreach <COLUMNs>
            } // endif
            else if ($relation instanceof BelongsToMany) {
                foreach ($columns as $alias => $column) {

                    $tableAlias = $tableAliasPrefix . $relationIndex;
                    $tableAndAlias = $relatedTable . ' AS ' . $tableAlias;

                    if (!isset($subQueries[$alias])) {
                        $pivotTable = $relation->getTable();
                        $subQueries[$alias] = $currentQuery = DB::query()
                            ->from($tableAndAlias)
                            ->select($tableAlias . '.' . $column)
                            // final table vs pivot table
                            ->join(
                                $pivotTable ,                               // tabelle pivot
                                $relation->getQualifiedRelatedKeyName() ,    // pivot.fk_related_id
                                '=' ,
                                $tableAlias . '.' . $relatedModel->getKeyName() // related_with_alias.id
                            )
                            ->whereColumn(
                                $relation->getQualifiedForeignKeyName() ,
                                '=' ,
                                $relation->getParent()->getQualifiedKeyName()
                            );

                        if (is_string($alias)) {
                            $query->selectSub($currentQuery , $alias);
                        } else {
                            throw new \InvalidArgumentException('Columns must be an associative array');
                        }
                    } 
                    else {
                        throw new \Exception('Multiple relation chain not implemented yet');
                    }
                } // end foreach <COLUMNs>
            } else {
                throw new \InvalidArgumentException(
                    sprintf("Relation $relationName of type %s is not supported" , get_class($relation))
                );
            }
            $currentModel = $relatedModel;
            $relationIndex++;
        } // end foreach <RELATIONs>
    }

    /**
     * @param $length
     * @return string
     */
    public static function randomStringAlpha($length) {
        $pool = array_merge(range('a', 'z'),range('A', 'Z'));
        $key = '';
        for($i=0; $i < $length; $i++) {
            $key .= $pool[mt_rand(0, count($pool) - 1)];
        }
        return $key;
    }
}

score -2 · Answer 7 · answered Jan 20 '16 at 22:24

-2

There is an alternative way of achieving the result you want to have without using joins. You can do the following to sort the students based on their exam's result. (Laravel 5.1):

$students = Student::with('exam')->get();

$students = $students->sortByDesc(function ($student, $key)
{
    return $student->exam->result;
});

answered Jan 20 '16 at 22:24

Joschi Vzfam

15
4

4

This 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 – mloureiro Jan 23 '16 at 19:33

Eloquent eager load Order by

7 Answers7

tl;dr

Explanation

Linked