如何在Laravel的Eloquent ORM中通过数据透视表数据进行排序

In my Database, I have:

在我的数据库中,我有:

tops Table
posts Table
tops_has_posts Table.

When I retrieve a top on my tops table I also retrieve the posts in relation with the top. But what if I want to retrieve these posts in a certain order ? So I add a range field in my pivot table tops_has_posts and I my trying to order by the result using Eloquent but it doesn't work.

当我在tops表上检索顶部时,我还检索与顶部相关的帖子。但是,如果我想按特定顺序检索这些帖子怎么办?所以我在我的数据透视表tops_has_posts中添加了一个范围字段,我尝试使用Eloquent按结果排序,但它不起作用。

I try this :

我试试这个:

$top->articles()->whereHas('articles', function($q) {
    $q->orderBy('range', 'ASC');
})->get()->toArray();

And this :

还有这个 :

$top->articles()->orderBy('range', 'ASC')->get()->toArray();

Both were desperate attempts.

两人都是绝望的尝试。

Thank you in advance.

先感谢您。

4 个解决方案

#1

There are 2 ways - one with specifying the table.field, other using Eloquent alias pivot_field if you use withPivot('field'):

有两种方法 - 一种是指定table.field,另一种是使用Eloquent别名pivot_field如果你使用withPivot('field'):

// if you use withPivot
public function articles()
{
  return $this->belongsToMany('Article', 'tops_has_posts')->withPivot('range');
}

// then: (with not whereHas)
$top = Top::with(['articles' => function ($q) {
  $q->orderBy('pivot_range', 'asc');
}])->first(); // or get() or whatever

This will work, because Eloquent aliases all fields provided in withPivot as pivot_field_name.

这将起作用,因为Eloquent将withPivot中提供的所有字段别名为pivot_field_name。

Now, generic solution:

现在,通用解决方案:

$top = Top::with(['articles' => function ($q) {
  $q->orderBy('tops_has_posts.range', 'asc');
}])->first(); // or get() or whatever

// or:
$top = Top::first();
$articles = $top->articles()->orderBy('tops_has_posts.range', 'asc')->get();

This will order the related query.

这将订购相关的查询。

Note: Don't make your life hard with naming things this way. posts are not necessarily articles, I would use either one or the other name, unless there is really need for this.

注意:不要以这种方式命名。帖子不一定是文章,我会使用一个或另一个名称,除非确实需要这个。

#2

in your blade try this:

在你的刀片中试试这个:

$top->articles()->orderBy('pivot_range','asc')->get();

#3

In Laravel 5.6+ (not sure about older versions) it's convenient to use this:

在Laravel 5.6+(不确定旧版本)中使用它很方便:

public function articles()
{
  return $this->belongsToMany('Article', 'tops_has_posts')->withPivot('range')->orderBy('tops_has_posts.range');
}

In this case, whenever you will call articles, they will be sorted automaticaly by range property.

在这种情况下,每当您调用文章时,它们将按范围属性自动排序。

#4

In Laravel 5.4 I have the following relation that works fine in Set model which belongsToMany of Job model:

在Laravel 5.4中,我有以下关系在Set模型中工作正常,属于JobMany of Job model:

public function jobs()
    {
        return $this->belongsToMany(Job::class, 'eqtype_jobs')
               ->withPivot(['created_at','updated_at','id'])
               ->orderBy('pivot_created_at','desc');
    }

The above relation returns all jobs that the specified set has been joined ordered by the pivot table's (eqtype_jobs) field created_at DESC.

上述关系返回由数据透视表的(eqtype_jobs)字段created_at DESC按顺序排列的指定集的所有作业。

The SQL printout of $set->jobs()->paginate(20) Looks like the following:

$ set-> jobs() - > paginate(20)的SQL打印输出如下所示:

select `jobs`.*, `eqtype_jobs`.`set_id` as `pivot_set_id`,
`eqtype_jobs`.`job_id` as `pivot_job_id`,
 `eqtype_jobs`.`created_at` as `pivot_created_at`,
`eqtype_jobs`.`updated_at` as `pivot_updated_at`,
 `eqtype_jobs`.`id` as `pivot_id` from
`jobs` inner join `eqtype_jobs` on `jobs`.`id` = `eqtype_jobs`.`job_id` where
`eqtype_jobs`.`set_id` = 56
 order by `pivot_created_at` desc limit 20 offset 0

#1