OrderBy与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?

Answer 1

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