CODEWITHSUNDEEP
phplaraveleloquenteloquent-relationship
Radi
Radi

Reputation: 179

Get all child models from parent Many to Many relationship Laravel Eloquent

I am building an ecommerce website using Laravel 5.8 and have the following problem. I want to retrieve all products from a category and its child categories, but to be able to perform filtering queries afterwards in my code. For example price limits and quantitative availability on the products.

The store has categories, which have child categories in the same table. Categories table (Simplified) - id|name|parent_id - Where if parent_id != 0, then the category is considered a child to a main category.

The products can be in more than one category and therefore I'm using a many to many relationship. Products table (Simplified) - id|name... Product categories table - id|product_id|category_id

My Products model look like this:

public function categories()
{
    return $this->belongsToMany(
        'App\CatalogCategory', 
        'catalog_product_categories', 
        'product_id', 
        'category_id'
    );
}

And my Categories model:

public function allProducts()
{
    return $this->belongsToMany(
        'App\CatalogProduct', 
        'catalog_product_categories', 
        'category_id', 
        'product_id'
    )
        ->where('is_active', 1)
        ->whereDate('active_from', '<=', Carbon::now('Europe/Sofia'))
        ->where(function ($query)
        {
            $query->whereDate('active_to', '>=', Carbon::now('Europe/Sofia'))
                ->orWhereNull('active_to');
        })
        ->wherePivotIn('category_id', $this->allChildrenIds());;  
}

Currently doing this, returns an empty collection:

$category = CatalogCategory::find(3);
dd($category->allProducts);

Upvotes: 1

Views: 1566

Answers (1)

Donkarnash
Donkarnash

Reputation: 12835

Okay I guess the problem is ->wherePivotIn('category_id', $this->allChildrenIds()).

You are trying to get products for a category - corresponding records can be identified by a row in pivot table which has this category's id with various other product_id.

But the wherePivotIn doesn't contain current category's id in $this->allChildrenIds() so no records are returned.

Define the relation without the wherePivotIn

public function allProducts()
{
    return $this->belongsToMany(
        'App\CatalogProduct', 
        'catalog_product_categories', 
        'category_id', 
        'product_id'
    )
        ->where('is_active', 1)
        ->whereDate('active_from', '<=', Carbon::now('Europe/Sofia'))
        ->where(function ($query)
        {
            $query->whereDate('active_to', '>=', Carbon::now('Europe/Sofia'))
                ->orWhereNull('active_to');
        });  
}

Then to get all products for current category and all it's subcategories

$category = Category::with(['products', 'subcategories.products'])->get();

With this query the products associated with subcategories will be nested under each subcategory respectively.

Upvotes: 1

Related Questions