php 关系上的Laravel orderBy

ecr0jaav  于 2023-02-03  发布在  PHP
关注(0)|答案(6)|浏览(160)

我正在循环查看某个帖子的作者发表的所有评论。

foreach($post->user->comments as $comment)
{
    echo "<li>" . $comment->title . " (" . $comment->post->id . ")</li>";
}

这给了我

I love this post (3)
This is a comment (5)
This is the second Comment (3)

如何按post_id排序,使上面的列表按3,3,5排序

fae0ux8s

fae0ux8s1#

可以使用查询函数扩展关系:

<?php
public function comments()
{
    return $this->hasMany('Comment')->orderBy('column');
}

[edit评论后]

<?php
class User
{
    public function comments()
    {
        return $this->hasMany('Comment');
    }
}

class Controller
{
    public function index()
    {
        $column = Input::get('orderBy', 'defaultColumn');
        $comments = User::find(1)->comments()->orderBy($column)->get();

        // use $comments in the template
    }
}

默认用户模型+简单控制器示例;在获取注解列表时,只需应用基于Input::get()的orderBy()。(确保执行一些输入检查;))

lmvvr0a8

lmvvr0a82#

我相信你也可以做到:

$sortDirection = 'desc';

$user->with(['comments' => function ($query) use ($sortDirection) {
    $query->orderBy('column', $sortDirection);
}]);

它允许你在每条相关的评论记录上运行任意的逻辑,你可以在其中包含如下内容:

$query->where('timestamp', '<', $someTime)->orderBy('timestamp', $sortDirection);
7vhp5slm

7vhp5slm3#

使用sortBy...可能会有所帮助。
$users = User::all()->with('rated')->get()->sortByDesc('rated.rating');

rqqzpn5f

rqqzpn5f4#

试试这个解决方案。

$mainModelData = mainModel::where('column', $value)
    ->join('relationModal', 'main_table_name.relation_table_column', '=', 'relation_table.id')
    ->orderBy('relation_table.title', 'ASC')
    ->with(['relationModal' => function ($q) {
        $q->where('column', 'value');
    }])->get();
    • 示例:**
$user = User::where('city', 'kullu')
    ->join('salaries', 'users.id', '=', 'salaries.user_id')
    ->orderBy('salaries.amount', 'ASC')
    ->with(['salaries' => function ($q) {
        $q->where('amount', '>', '500000');
    }])->get();

您可以根据数据库结构更改join()中的列名。

uyhoqukh

uyhoqukh5#

我在一个关系字段上做了一个trait to order。我在有状态关系的网店订单上遇到了这个问题,状态有一个名称字段。
Example of the situation
用雄辩模型的"连接"排序是不可能的,因为它们不是连接。它们是在第一个查询完成后运行的查询。所以我做了一个小黑客来读取雄辩的关系数据(如表,连接键和其他地方,如果包括在内),并将其连接到主查询上。这只适用于一对一的关系。
第一步是创建一个trait并在模型上使用它。在trait中你有两个功能。第一个:

/**
 * @param string $relation - The relation to create the query for
 * @param string|null $overwrite_table - In case if you want to overwrite the table (join as)
 * @return Builder
 */
public static function RelationToJoin(string $relation, $overwrite_table = false) {
    $instance = (new self());
    if(!method_exists($instance, $relation))
        throw new \Error('Method ' . $relation . ' does not exists on class ' . self::class);
    $relationData = $instance->{$relation}();
    if(gettype($relationData) !== 'object')
        throw new \Error('Method ' . $relation . ' is not a relation of class ' . self::class);
    if(!is_subclass_of(get_class($relationData), Relation::class))
        throw new \Error('Method ' . $relation . ' is not a relation of class ' . self::class);
    $related = $relationData->getRelated();
    $me = new self();
    $query = $relationData->getQuery()->getQuery();
    switch(get_class($relationData)) {
        case HasOne::class:
            $keys = [
                'foreign' => $relationData->getForeignKeyName(),
                'local' => $relationData->getLocalKeyName()
            ];
        break;
        case BelongsTo::class:
            $keys = [
                'foreign' => $relationData->getOwnerKeyName(),
                'local' => $relationData->getForeignKeyName()
            ];
        break;
        default:
            throw new \Error('Relation join only works with one to one relationships');
    }
    $checks = [];
    $other_table = ($overwrite_table ? $overwrite_table : $related->getTable());
    foreach($keys as $key) {
        array_push($checks, $key);
        array_push($checks, $related->getTable() . '.' . $key);
    }
    foreach($query->wheres as $key => $where)
        if(in_array($where['type'], ['Null', 'NotNull']) && in_array($where['column'], $checks))
            unset($query->wheres[$key]);
    $query = $query->whereRaw('`' . $other_table . '`.`' . $keys['foreign'] . '` = `' . $me->getTable() . '`.`' . $keys['local'] . '`');
    return (object) [
        'query' => $query,
        'table' => $related->getTable(),
        'wheres' => $query->wheres,
        'bindings' => $query->bindings
    ];
}

这是读取有说服力的数据的"检测"功能。
第二个:

/**
 * @param Builder $builder
 * @param string $relation - The relation to join
 */
public function scopeJoinRelation(Builder $query, string $relation) {
    $join_query = self::RelationToJoin($relation, $relation);
    $query->join($join_query->table . ' AS ' . $relation, function(JoinClause $builder) use($join_query) {
        return $builder->mergeWheres($join_query->wheres, $join_query->bindings);
    });
    return $query;
}

这是一个向模型添加作用域以在查询中使用的函数。现在只需在模型上使用trait,就可以像这样使用它:

Order::joinRelation('status')->select([
    'orders.*',
    'status.name AS status_name'
])->orderBy('status_name')->get();
u59ebvdq

u59ebvdq6#

$withEmployees = Groups::get()->sortBy('employee.name');

相关问题