Laravel Eloquent nested query

Question

I was working with Laravel and got stuck in a situation. I have following models:

Category
Product
CategoryProduct

CategoryProduct holds the information about which product belongs to which category (a product may belong to multiple categories).

Now, when I want to load all products belonging to a particular category, I need to run query on Product and CategoryProduct which is where I'm stuck.

I gave it the following try but was unsuccessful:

$products = Product::where('status', '=', 'active')
->where('category_id', '=', $category_id)
->take($count)
->skip($skip)
->get();

Obviously, it will say that category_id is not a column.

Here is my DB & Model structure:

categories table

id, name, etc.

products table

id, name, sku, etc.

category_products table

id, product_id, ( Foreign key to Product.id ) category_id, ( Foreign key to Category.id ) etc.

Product model

class Product extends Eloquent {

protected $table = 'products';

protected $hidden = array();

    public static $rules = array('name' => 'required|min:3');

}

Category model

class Category extends Eloquent {

protected $table = 'categories';

public static $rules = array('name' => 'required|min:3');

}

CategoryProduct model

<?php

class CategoryProduct extends Eloquent {

protected $table = 'category_products';

public function product()
{
    return $this->belongsTo('Product');
}

public function category()
{
    return $this->belongsTo('Category');
}
}

Update

A new question on this

I'm trying to display products. If category is not passed (value is -1), then I will show all products, otherwise I will show products from the passed category.

Now, when I show all products, those products may already exist in a category. I want to display ticked checkbox for products that are already in a category. I'm doing something like this:

if($category_id==-1)
        $products = Product::where('status', '=', 'active')->take($count)->skip($skip)->get();
    else{
        $products = Product::whereHas('categories', function($q) use ($category_id)
        {
            $q->where('category_id', $category_id);
        })->where('status', 'active')
            ->take($count)
            ->skip($skip)
            ->get();
    }

The table category_products have product_id, category_id as columns.

Now, the query:

$products = Product::where('status', '=', 'active')->take($count)->skip($skip)->get();

will pick products only from products table. If I check each product for its existence in category_products, then there will be too many database queries for large number of products.

Any idea, how to achieve this. I hope I was able to clear my situation. Thanks

Look into the `has`/`whereHas` functions. – ceejayoz Apr 20 '15 at 14:55 — ceejayoz, Apr 20 '15 at 14:55

score 4 · Accepted Answer · edited Jun 17 '15 at 07:29

4

The CategoryProduct model should not be necessary unless you have additional fields besides product_id and category_id which point to other relationships.

What is necessary are the methods for setting up the relationship on the Category and Product models.

In Category, add the relationship function...

public function products()
{
    return $this->belongsToMany('Product', 'category_products');
}

In your Product model, do the same for categories.

public function categories()
{
    return $this->belongsToMany('Category', 'category_products');
}

Then you can query for your active products that belong to that category using your relationship method and whereHas()

$products = Product::whereHas('categories', function($q) use ($category_id)
{
    $q->where('id', $category_id);
})->where('status', 'active')
->take($count)
->skip($skip)
->get();

edited Jun 17 '15 at 07:29

Pᴇʜ

56,719
10
49
73

answered Apr 20 '15 at 14:55

user1669496

32,176
9
73
65

Hey user3158900 thanks for the quick reply. CategoryProduct is used because a product may belong to multiple category. – Ashutosh Apr 20 '15 at 15:07
1

@Ashutosh Many-to-many pivot tables don't require a model. Take a look at the documentation link in my answer – padarom Apr 20 '15 at 15:08
Ok, got it what you wanted to say, thanks again. Looking at the documentation now. – Ashutosh Apr 20 '15 at 15:12
When you use `belongsToMany` on the parent tables, Laravel will handle and manage the pivot table (`category_prodcuts`) for you. The only thing you need to do is tell Laravel what the name of that table is, which I have edited/added in my answer. – user1669496 Apr 20 '15 at 15:18
@user1669496 hi, but what if the product owned by a vendor? instead of `$product = Products::whereHas()` but `$vendor = Vendors::whereHas()`. Vendor is the parent of the Products. How is this possible to use the condition under multiple nested relations? – Wiguna R Mar 31 '21 at 12:52

score 1 · Answer 2 · edited May 23 '17 at 12:29

You don't need a model for a pivot table in Many-to-Many relationships. Look at this section of the Eloquent documentation for further explanation.

You still need to create a migration to set up the pivot table (or do it manually if you don't use migrations), but not a model. Instead, create a function for Category to designate the relationship:

public function products()
{
    return $this->belongsToMany('App\Product', 'category_products');
    // - You might need to adjust the namespace of App\Product
    // - category_products refers to the pivot table name
}

Likewise, Product needs a similar public function.

Then you're able to do it the other way around, by finding the category and then listing all its related products:

$products = Category::find($category_id)
   ->products()
   ->where('status', 'active')
   ->take($count)
   ->skip($skip)
   ->get();

This question could also be relevant to yours.