laravel withCount of linear nested relations in laravel

user_5761597 0
Viewed 71

I have tables,

ec_products : id | brand_id (id from ec_brands) | store_id (id from mp_stores)

ec_brands: id

mp_stores: id

I am calculating total products belong to each brand and store using relations and withCount of Laravel, Like,

Brand model,

public function products()
{
    return $this->hasMany(Product::class, 'brand_id')->where('is_variation', 0);
}

Stores model

public function products()
{
    return $this->hasMany(Product::class, 'store_id')->where('is_variation', 0);
}

And in each model,

$data = $data->withCount(‘products’);

Now I introduced categories, One product belongs to multiple categories.

So used separate table to link category and product.

ec_product_category_product: id | category_id (from category table) | product_id (from our products table)

So final question I have, How to join all,

I want to list each of these (brand,store, category) and count products of each based on request parameters.

Like

if any brands selected, then count store and category related to that brand.

if stores selected, then count brands and category related to that store.

if any category selected, then count store and brand related to that category.

structure of UI

Suggestion or solution.

Thanks

relation laravel mysql php
1 Answers

You should always start you Eloquent query with the Object you want to manipulate, here, it's Product and THEN constraint your query,

so in your controller :

$product_count = Product::when($request->category_id, function($query) use ($request){
       $query->where('category_id', $request->category_id);
    })
[...] //do that for each of your constraints
->count();

if you want to do a search on the name of the category for example, use a whereHas :

$product_count = Product::when($request->search, function($query) use ($request){
       $query->whereHas('category', function($query) use ($request){
             $query->where('name', 'like', "%$request->search%");
       });
    })
[...] //do that for each of your constraints
->count();
user_78943340
Related