Order pagination result by pivot field

I have 2 table with many to many relationships :

Films
Tags Many films can have many tags (belongsToMany relationship)

So from a controller, I want to get the films having a tag and paginate the results. How can I order my results with the field 'ordre' the pivot table film_tag ? So far I have :

$films = Film::whereHas('tags', function($query) use ($tags){
    $query->whereIn('tags.id', $tags)
})->orderBy('year','DESC')->paginate(40);

This works because year field is on the films table, but if I try and change year with film_tag.ordre, it breaks.

I tried a join in the request, but I guess it breaks the 'Film' model afterwards, as I tried to access a custom attribute without success...

class Film extends Model 
{
public function getTitleAttribute()
    {
        return $this->titles()->where('default',true)->first()->titre;
    }
}

What am I missing ? Is it not possible ?

bobbybouwmann

8 years ago

Level 88

You should use the columnpivot_order here instead

$filem->tags()
    ->orderBy('pivot_order', 'desc')
    ->get();

Reference: https://laravel.io/forum/04-17-2014-order-by-pivot-table-attribute-in-eloquent

morganThePirate

8 years ago

Level 1

I can't access the tags method from this :

$films = Film::whereHas('tags', function($query) use ($tags){
    $query->whereIn('tags.id', $tags)
})

athulpraj

8 years ago

Level 1

You can try adding an inverse relationship to the Tag model like

    public function films()
    {
        return $this->belongsToMany('App\Film','film_tag','tag_id','film_id');
    }

as you have mentioned you want to get the films having a tag and paginate the results

Please or to participate in this conversation.