Alvin Bakker Alvin Bakker - 1 year ago 67
MySQL Question

OrderBy with linked database tables in Laravel 5.1

I have a table with products, one with categories and one linking the products to multiple categories. All with correct foreign keys linking them together. When the user is in a category, the products within this category are selected using the


But what I would like is to find an easy way, without looping through the output of linked products to this category to sort the products by name.

My database layout:

id: 1; name: testing 123
id: 2; name: testing 345
id: 3; name: testing 567

id: 1; name: cat one
id: 2; name: cat two
id: 3; name: cat three

id: 1; product_id: 1; product_category_id: 1
id: 2; product_id: 1; product_category_id: 2
id: 3; product_id: 2; product_category_id: 1
id: 4; product_id: 3; product_category_id: 1
id: 5; product_id: 3; product_category_id: 2
id: 6; product_id: 3; product_category_id: 3

In my ProductCategory Model I have:

public function ProductToCategory() {
return $this->hasMany('App\Models\ProductToCategory');

In my ProductToCategory model I have:

public function Product() {
return $this->belongsTo('App\Models\Product', 'product_id');

In the controller I have:

$linked_products = $category->ProductToCategory()->get();
foreach($linked_products as $linked_product) {
if($linked_product->Product->is_active == 1) {
$array[$linked_product->product_id] = $linked_product->Product);


This gives me with
the product info I need.

But using the Eloquent approach in the models I want to order the products by name before giving me the query result.

How do I go about that?

Answer Source

It seems that you have a many-to-many relation. You do not need a ProductToCategory-Model.

Instead you need this relation in your ProductCategory-Model:

public function products()
    return $this->belongsToMany('App\Models\Product');

And one in your Product-Model:

public function categories()
    return $this->belongsToMany('App\Models\ProductCategory');

You can also specify the name of the pivot-table (the product_to_category-table) and the column names:

public function categories()
    $this->belongsToMany('App\Models\ProductCategory', 'table_name', 'product_id ', 'product_category_id ');

With this you're able to query it as follows:

$linked_products = $category->products()->orderBy('name')->get();
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download