Ad

Join And OrderBy Within With Laravel Eloquent Query

- 1 answer

I want to display ordered list of stores based on cities from a brand.

This is the code I tried

 $brand = Brand::where('slug','=',$slugurl)
      ->with(['stores' => function($q){
        $q->where('status', 1)
        ->join('cities', function ($join){
              $join->on('cities.id', '=', 'stores.city_id')->orderBy('cities.sort_number', 'DESC');
        });

      }])
      ->firstOrFail();

The relationship of the tables :

Brand hasMany Stores and Stores belongTo Cities

The listings results output is not ordered based on cities sort_number. Any idea how to achieve this ?

Ad

Answer

It is useless to order in join's closure.

You need to append the orderBy after join:

$brand = Brand::where('slug','=',$slugurl)
      ->with(['stores' => function($q){
        $q->where('status', 1)
          ->join('cities', 'cities.id', '=', 'stores.city_id')
          ->orderBy('cities.sort_number', 'DESC');
      }])
      ->firstOrFail();

This query convert to raw sql is:

select * from brands where slug = ? limit 1;

select * from stores 
join cities on cities.id = stores.city_id 
where status = 1 and stores.brand_id in (?)
order by cities.sort_number desc;
Ad
source: stackoverflow.com
Ad