How to order by pivot table data in Laravel's Eloquent ORM

Question

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.

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.

Answer 1

There are 2 ways - one with specifying the table.field, other using Eloquent alias pivot_field if you use 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.

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.

Answer 2

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

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.