OrderBy with linked database tables in Laravel 5.1

Question

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?

What is ProductToCategory? Why don't you use many-to-many with belongsToMany? — sleepless
ProductToCategory is a table where one product can be linked into multiple categories. So you have for example product_id 1 is in the database as product_id 1; category_id 1; and product_id 1; category_id 2;. So I do a query with the category_id to get all products in this category. It would be simpler if each product was linked to one category — Alvin Bakker

sleepless sleepless · Accepted Answer · 2016-11-18T14:36:54

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

OrderBy with linked database tables in Laravel 5.1

1 Answers