How to fetch count from distant relationship in laravel way?

Question

I am trying to fetch all the attributes with product count that is associated with products in the given category.

table structure

attributes table:

| id  | name | value | filterable |

products table:

| id  | name |

categories table:

| id  | name |

attribute_product pivot table:

| id  | attribute_id | product_id

category_product pivot table:

| id  | category_id | product_id

Below is how my models look like.

Attribute

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

Product

public function attributes(){
        return $this->belongsToMany('App\Models\Attribute');
    }

public function categories(){
        return $this->belongsToMany('App\Models\Category');
    }

Category

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

I can get all the attributes associated with a product in the given category with the below query.


Attribute::where('filterable', '=', 1)
->whereHas('products.categories', function (Builder $query) use ($category) {

$query->where('category_product.category_id', '=', $category->id);

})->get();

However, I cannot figure out how I should get product counts.

I started testing query builder to achieve this but condition on where pivot seems to be binding attribute_product.attribute_id as a string value instead of the column value.

$data = DB::table('attributes')->where('filterable', '=', true)
->whereExists(function ($query) {
$query->select('*')->from('products')
->join('attribute_product', function ($join){
    $join->on('products.id', '=', 'attribute_product.product_id');
})->where('attributes.id', '=', 'attribute_product.attribute_id');

})->get();

Below is more or less the final SQL query I need to send to the database

select `attributes`.*, 
(SELECT 
count(*) from `products` 
inner join `attribute_product` on `products`.`id` = `attribute_product`.`product_id` 
where `attributes`.`id` = `attribute_product`.`attribute_id`) as `products_count` 
from `attributes` where `filterable` = 1 
and EXISTS 
(select * from `products` inner join `attribute_product` on `products`.`id` = `attribute_product`.`product_id` 
where `attributes`.`id` = `attribute_product`.`attribute_id` 
and EXISTS 
(select * from `categories` inner join `category_product` on `categories`.`id` = `category_product`.`category_id` 
where `products`.`id` = `category_product`.`product_id` and `category_product`.`category_id` = 9))

Please, someone, assist me in getting attributes with associated product count in the laravel way.

athulpraj · Answer 1 · 2020-02-13T11:56:28.043

3

Check out the

 withCount('relation_name')

method in eloquent Ex:

 $posts = App\Post::withCount([
    'comments',
    'comments as pending_comments_count' => function (Builder $query) {
    $query->where('approved', false);
    }
])->get();

edited Feb 13 '20 at 11:56

answered Feb 13 '20 at 11:50

athulpraj

1,547
1
13
24

Thank you @Athul. This helped me resolve the problem. – Kanchana Randika Feb 15 '20 at 16:31

score -1 · Accepted Answer · answered Feb 15 '20 at 16:41

I managed to get all my results in a single request with the below query. Thanks! @athul for showing the direction.

Attribute::where('filterable', '=', 1)->whereHas('products.categories', function (Builder $query) use($category) {
                    $query->where('categories.id', '=', $category->id);
                })
                ->withCount(['products' => function ($query) use($category) {
                    $query->whereHas('categories', function (Builder $query) use ($category) {
                        $query->where('categories.id', '=', $category->id);
                  });
           }])->get();

How to fetch count from distant relationship in laravel way?

table structure

2 Answers2