How to count product with respect to categories using query builder?

This is my Category Database Table

 id |  category_name 	| created_at
1   | Mobile            |2020/09/20
2   | Laptop	        |2020/09/20


This is my brand database table

id|category_id  | brand_name  | created_at   
 1|   1         |  Samsung    | 2020/09/20   
2 |   2         |    DELL     | 2020/09/20   
3 |   2         |    Lenova   |2020/09/20  
4 |   1         |    Nokia    | 2020/09/20
5 |   2         |     HP      |2020/09/20


This is my product database table

id | brand_id | product_name | created_at
1  |     1    |   Galaxy A10 |  2020/09/20
2  |     2    |    H5        |  2020/09/20
3  |     3    |   len-5      |  2020/09/20	 
4  |     3    |  len-6       |  2020/09/20
5  |     5    |   Hp-2       |  2020/09/20

This is Table I want to make from above database.Counting product through category.

Category Name  |  Mobile     | Laptop
  counting     |       1     |    4


I have tried a query like this but not getting result.

$count=DB::table('categories')
                  ->select("category", DB::raw("COUNT(products.brand_id) as product_count"))
                 ->leftJoin('brands','brands.category_id','=','category.id')
                 ->leftJoin('products','products.brand_id','=','brands.id')
                 ->groupBy('categories.category_name')
                 ->get();

MarianoMoreyra

5 years ago

Best Answer

Level 25

Hi @saurav77

You could define a hasThrough relationship between Category and Product adding the following to your Category Model::

    public function products()
    {
        return $this->hasManyThrough('App\Models\Product', 'App\Models\Brand');
    }

So then you can use withCount in your query to get the Product count for each Category:


$categories = App\Models\Category::withCount('products')->get();

Of course I’m assuming your Models’ names as well as that you have them placed inside a Models folder.

Si you’ll have to change that in my example to make it work probably.

Refs:

https://laravel.com/docs/master/eloquent-relationships#has-many-through https://laravel.com/docs/master/eloquent-relationships#counting-related-models

Hope this helps!

1 like

saurav77

5 years ago

Level 2

@marianomoreyra

I am sorry if my question was unclear but I need to use that DB:: raw. I am just not getting accurate result so I donot know whats wrong in my query. Although thanks for your answer.It seem useful.

MarianoMoreyra

5 years ago

Level 25

No problem @saurav77

Just got confused because your post was under Eloquent category so I assumed that you wanted to resolve it, the Eloquent way hehe.

Regarding the query that you already tried, what you mean by "not getting result"? You don't get anything at all? You get an error? You get an count that doesn't mach the expected on?

saurav77

5 years ago

Level 2

@marianomoreyra No there is no error. I am getting results but not getting the expected result. Do you think my query is right or needs to be improved for better results? Do you think this query will be for the long-term ?? & Thank you so much for your time

MarianoMoreyra

5 years ago

Level 25

@saurav77 at first glance that query looks like it should work (except for the "category" at the select, which I guess it should be "category_name" based on your tables description).

But if you think the results are not accurate, then you'll have to post more information in order to be able to help you.

For instance, and example with data, expected result and current result obtained might be helpful.

saurav77

5 years ago

Level 2

@marianomoreyra Leave it for now. Now it's giving the expected result but I am worried when there will be more that 5000 data.I hope this query work in future.

Thanks Brother

Please or to participate in this conversation.