CODEWITHSUNDEEP
phplaraveleloquentpivot
Emad Az
Emad Az

Reputation: 35

how to sort results based on pivot table in laravel

i have three table like this :

it's ManyToMany relationship.

i want to sort tours based on meta value in meta_tour table like this :

tours orderBy tour_duration.value

Update

i tried this :

        Tour::with(['metas' => function ($query) {
        $query->where('key', 'tour_duration')
        ->orderByDesc('value');
        }])->paginate(15);

and this :

        Tour::with(['metas' => function ($query) {
        $query->where('key', 'tour_duration');
        }])->orderByDesc('value')->paginate(15);

and this : 

        Tour::whereHas('metas' , function ($query) {
        $query->where('key', 'tour_duration')
              ->orderBy('value', 'desc')
        })->paginate(15);

but not working.

Upvotes: 1

Views: 1104

Answers (3)

mayrop
mayrop

Reputation: 701

Try:

Tour::join('meta_tour', 'meta_tour.tour_id', '=', 'tour.id')
    ->join('meta', 'meta_tour.meta_id', '=', 'meta.id')
    ->where('meta.key', '=', 'tour_duration')
    ->orderBy('meta_tour.value', 'DESC')
    ->get();

Upvotes: 0

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25936

You can use a modified withCount():

Tour::withCount(['metas as tour_duration' => function ($query) {
    $query->select('meta_tour.value')->where('key', 'tour_duration');
}])->orderByDesc('tour_duration')->paginate(15);

This uses a subquery to get the value from the pivot table:

select `tours`.*,
  (select `meta_tour`.`value`
   from `metas`
   inner join `meta_tour` on `metas`.`id` = `meta_tour`.`meta_id`
   where `tours`.`id` = `meta_tour`.`tour_id`
     and `key` = 'tour_duration') as `tour_duration`
from `tours`
order by `tour_duration` desc

Upvotes: 0

Brian Lee
Brian Lee

Reputation: 18197

You can use the withPivot method to order by a pivot table column:

// Tour model
public function meta () {
    return $this->belongsToMany(Meta::class)
                ->withPivot('meta_tour')
                ->orderByDesc('meta_tour.value');
}

Update

To only get the meta with tour duration, try eager loading and adding a where clause on the meta:

$tour->with(['meta' => function ($query) {
    $query->where('key', 'tour_duration');
}])->orderByDesc('meta.value')->get();

Upvotes: 1

Related Questions