Laravel order by related table

12
votes

I have Product and Category models in my project. Product belongs to Category. Since Product has foreign key category_id, I could easily sort the output like so:

$products = Product::orderBy('category_id', 'asc')->get();

But what I really wanted is to sort Products by Category name, so I tried:

$products = Product::with(['categories' => function($q){
            $q->orderBy('name', 'asc')->first();
        }]);

But that outputs nothing. As a test, I have returned return Product::with('categories')->first(); and it outputs fine...

Here are the Eloquent relations.

Product

class Product extends Model
{
    protected $fillable = [
        'name',
        'description',
        'price',
        'category_id',
    ];

    protected $hidden = [
        'created_at',
        'updated_at',
    ];

    public function categories()
    {
        return $this->belongsTo('\App\Category', 'category_id');
    }
}

Category:

class Category extends Model
{
    protected $fillable = [
        'name'
    ];

    public function products()
    {
        return $this->hasMany('\App\Product');
    }
}

And the view part:

@foreach ($products as $product)
                <tr>

                    <td>{!! $product->categories->name !!}</td>
                    <td>
                        @if(!empty($product->picture))
                            Yes
                        @else
                            No
                        @endif
                    </td>
                    <td>{!! $product->name !!}</td>
                    <td>{!! $product->description !!}</td>
                    <td>{!! $product->price !!}</td>
                    <td>
                        <a href="{{ url('/product/'.$product->id.'/edit') }}">
                            <i class="fa fa-fw fa-pencil text-warning"></i>
                        </a>
                        <a href="" data-href="{{route('product.destroyMe', $product->id)}}"
                           data-toggle="modal" data-target="#confirm-delete">
                            <i class="fa fa-fw fa-times text-danger"></i>
                        </a>
                    </td>
                </tr>
            @endforeach
4
phplaravelsortinghtml-table

4 Answers

9
votes

I have not tested this, but I think this should work

// Get all the products
$products = \App\Product::all();

// Add Closure function to the sortBy method, to sort by the name of the category
$products->sortBy(function($product) { 
  return $product->categories()->name;
});

This should also working: 

 $products = Product::with('categories')->get()
   ->sortBy(function($product) { 
       return $product->categories->name;
  })
6
votes

You can use join(), try below code

$query = new Product; //new object
//$query = Product::where('id','!=',0);
$query = $query->join('categories', 'categories.id','=','products.categories.id');
$query = $query->select('categories.name as cat_name','products.*');
$query = $query->orderBy('cat_name','asc');
$record = $query->get();
1
votes

You can simply supply function to Eloquent eager loading where you can handle how the related table gets queried.

$product = Product::with(['categories' => function ($q) {
  $q->orderBy('name', 'asc');
}])->find($productId);
-2
votes

In laravel, you can't perform order by related table without manually joining related table what is really very awkward, but this can package can do this for you out of the box https://github.com/fico7489/laravel-eloquent-join