CODEWITHSUNDEEP
phpmysqllaraveleloquent
KeizerBridge
KeizerBridge

Reputation: 2757

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

In my Database, I have:

When I retrieve a top on my tops table I also retrieve the posts about the top. But what if I want to retrieve these posts in a certain order? So I added a range field in my pivot table tops_has_posts and I tried to order by the result using Eloquent but it didn'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.

Upvotes: 37

Views: 49254

Answers (7)

Eghbal Shirasb
Eghbal Shirasb

Reputation: 3

you can use this:

    public function keywords() {
    return $this->morphToMany(\App\Models\Keyword::class, "keywordable")->withPivot('order');
}
public function getKeywordOrderAttribute() {
    return $this->keywords()->first()->pivot->order;
}

and append keyword attribiute to model after geting and use sortby

$courses->get()->append('keyword_order')->sortBy('keyword_order');

Upvotes: 0

SaidbakR
SaidbakR

Reputation: 13534

In Laravel 5.4 I have the following relation that works fine in Set model which belongsToMany 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.

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

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

Upvotes: 20

Doctiger
Doctiger

Reputation: 2508

If you print out the SQL query of belongsToMany relationship, you will find that the column names of pivot tables are using the pivot_ prefix as a new alias.

For example, created_at, updated_at in pivot table have got pivot_created_at, pivot_updated_at aliases. So the orderBy method should use these aliases instead.

Here is an example of how you can do that.

class User {
    ...
    public function posts(): BelongsToMany {
        return $this->belongsToMany(
                   Post::class, 
                   'post_user', 
                   'user_id', 
                   'post_id')
               ->withTimestamps()
               ->latest('pivot_created_at');
    }
    ...
}

You can use orderBy instead of using latest method if you prefer. In the above example, post_user is pivot table, and you can see that the column name for ordering is now pivot_created_at or pivot_updated_at.

Upvotes: 4

Jakub Adamec
Jakub Adamec

Reputation: 1124

For Laravel 8.17.2+ you can use ::orderByPivot().

https://github.com/laravel/framework/releases/tag/v8.17.2

Upvotes: 37

Sergiu
Sergiu

Reputation: 435

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.

Upvotes: 26

Hamid Teimouri
Hamid Teimouri

Reputation: 482

in your blade try this:

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

Upvotes: 4

Jarek Tkaczyk
Jarek Tkaczyk

Reputation: 81167

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.

Upvotes: 48

Related Questions