Alvin Bakker Alvin Bakker - 18 days ago 5
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

ProductToCategory
model.

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:

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

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

product_to_categories
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
$linked_product->Product
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

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();