laravel Eloquent 急切加载 Order by

Eloquent eager load Order by

提问by Andrius

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 ?

但它不起作用。任何想法如何做到这一点？

回答by Glad To Help

Try this:

尝试这个：

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

回答by Luis Dalmolin

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_idand your exams table are named exam.

在这种情况下，假设您有一列student_id并且您的考试表名为exam。

回答by rosell.dk

If you ALWAYS want it sorted by exam result, you can add the sortBy call directly in the relationship function on the model.

如果您总是希望按考试结果排序，您可以直接在模型的关系函数中添加 sortBy 调用。

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)

（这个答案归功于 pfriendly - 他在这里回答了：How to sort an Eloquent subquery）

回答by Guru

This worked for me:

这对我有用：

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


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


    return $query->get();

回答by totymedli

tl;dr

tl;博士

Student::with('exam')->get()->sortByDesc('exam.result');

This will sort the resultsof the query after eager loading using collection methodsand not by a MySQL ORDER BY.

这将在使用集合方法而不是 MySQL预先加载后对查询结果进行排序。ORDER BY

Explanation

解释

When you eager load you can't use an ORDER BYon 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 documentationeager loading happens in 2 query.

当您急切加载时，您不能ORDER BY在已加载的关系上使用 ，因为这些关系将作为第二次查询的结果被请求和组装。正如您在Laravel 文档中看到的，急切加载发生在 2 个查询中。

If you want to use MySQL's ORDER BYyou have to join the related tables.

如果你想使用 MySQL，ORDER BY你必须加入相关的表。

As a workaround, you can run your query and sort the resulting collection with sortBy, sortByDescor even sort. This solution has advantages and disadvantages over the join solution:

作为一种解决方法，您可以运行查询并使用sortBy、sortByDesc甚至对结果集合进行排序sort。与 join 解决方案相比，此解决方案具有优点和缺点：

Advantages:

好处：

• You keep Eloquent functionality.
• Shorter and more intuitive code.

• 您保留 Eloquent 功能。
• 更短、更直观的代码。

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 BYwith 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.

• 排序将由 PHP 而不是数据库引擎完成。
• 您只能按单列排序，除非您为排序器函数提供自定义闭包。
• 如果您只需要查询的排序结果的一部分（例如ORDER BYwith LIMIT），则必须获取所有内容，对其进行排序，然后过滤排序后的结果，否则您最终只会订购过滤后的部分（排序不会考虑过滤掉的元素）。因此，只有当您无论如何都要处理整个数据集或开销不成问题时，才可以接受此解决方案。

回答by MatteoOreficeIT

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

您可以使用 \Illuminate\Database\Eloquent\Relations\Relation 和查询范围通过关系添加远列，我为此编写了一个特征，它错过了 HasOne o HasMany 但拥有 BelongsTo 和 BelongsToMany 可以轻松适应

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

此外，该方法可以增强以支持多链关系的深度 1 以上，我为此腾出了空间

<?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;
    }
}

回答by Joschi Vzfam

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):

有一种替代方法可以在不使用连接的情况下实现您想要的结果。您可以执行以下操作以根据学生的考试结果对学生进行排序。（Laravel 5.1）：

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

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