Hello guys,
I have 5 tables below, I want to get all categories with eagerloading brands and their product counts associated with that category.
my tables structure:
Brands Table:
id,
name
Categories Table:
id,
name,
parent_id
Products Table:
id,
name,
brand_id,
category_id <-- I have also tried to explicitly set it to get category brand product count. but :/
Product Channels Table:
id,
name,
brand_id,
product_id,
channel_id
I have tried:
$categories = Category::selectRaw('*')->forWebsite()
->withCount('products')
->with([
'brands' => function ($query1) {
$query1->select('brands.id', 'brands.name')
->distinct('brands.id')
->withCount(['products'=>function($query2){
$query2->where('products.category_id','categories.id');
}]);
}
])->get();
I'm able to fetch categories and their brands but I'm unable to get product counts of brands associated with the category. :/
But I'm unable to get brand products count belonging to that category.
I have also tried raw query by joining these tables but this is not efficient and very very slow, it takes more then 2 mins to fetch categories along with brands & their product counts from around 36k products.
And in this query i was returning a custom array which i would format as required in my api resource
though this query returns the desired data;
$categories = Category::selectRaw('
categories.*,
GROUP_CONCAT(
distinct p.brand_id,\'|\',(
SELECT COUNT(*)
FROM products AS p2
JOIN category_product AS cp2 ON cp2.product_id = p2.id
WHERE (p2.brand_id = p.brand_id AND cp2.category_id = categories.id)
having count(*)>0
)
) as brands,
count(p.id) as total_items
')
->leftjoin('category_product as cp', 'cp.category_id', '=', 'categories.id')
->leftjoin('products as p', 'cp.product_id', '=', 'p.id')
->when(current_auth() == 'website', function ($query) {
return $query->where(['categories.active' => true, 'categories.website' => true]);
})
->groupBy('categories.id')
->orderBy('categories.name')
->get();
the desired data after formatting in api Resource is:
{
"id": 71,
"parent_id": 8,
"name": "Air Freshners",
"slug": "household-category-air-freshners",
"thumb": null,
"brands": [
{
"id": 368,
"total_items": 2
},
{
"id": 257,
"total_items": 1
}
],
"total_items": 3
}
my category model has relations:
public function products()
{
return $this->belongsToMany(Product::class);
}
// Im using Products table as pivot for brand_category relation here
public function brands()
{
return $this->belongsToMany(Brand::class,'products');
}
No how do i do this by eagerloading these relations.
I need your help guys! help! :/
