php - Get products count which are under sub categories of a parent category in laravel ← (PHP, Laravel)

I am trying to display all the categories with their products_count. I am using eloquent to get categories and their individual products_count without any problem. But as my categories are nested and I thought it would be great if I can sum all of its subcategories products count as parent category products_count.

This is my categories json output

{
            "id": 3,
            "name_en": "Spices",
            "parent_id": null,
            "products_count": 0, // it should be 1 instead of 0
            "children_count": 4,
            "children": [
                {
                    "id": 4,
                    "name_en": "HIJ",
                    "parent_id": 3,
                    "products_count": 0,
                    "children_count": 0,
                    "children": []
                },
                {
                    "id": 5,
                    "name_en": "XYZ",
                    "parent_id": 3,
                    "products_count": 1,
                    "children_count": 0,
                    "children": []
                },
                {
                    "id": 6,
                    "name_en": "ABC",
                    "parent_id": 3,
                    "products_count": 0,
                    "children_count": 0,
                    "children": []
                },
                {
                    "id": 7,
                    "name_en": "DEF",
                    "parent_id": 3,
                    "products_count": 0,
                    "children_count": 0,
                    "children": []
                }
            ]
        }

Here is my categoryController

...
public function index() {
   return Category::whereNull('parent_id')
            ->with(['children' => function($category) {
                return $category->withCount(['children', 'products']);
            }])
            ->withCount(['children', 'products'])
            ->get()
}
...

CategoryModel

class CategoryModel extends Model {

    public function parent() {
       return $this->belongsTo('App\Category', 'id', 'parent_id');
    }

    public function children() {
        return $this->hasMany('App\Category', 'parent_id', 'id')->with('children');
    }

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

ProductModel

class Product extends Model
{
    public function categories() {
        return $this->belongsToMany('App\Category')->withTimestamps();
    }
}

EDIT

category_product pivot table has two columns:

product_id | category_id

the parent category has no direct relation with its subcategory products. Is it possible to get this done with an eloquent query? As I am exploring some similar questions, people have tried it with a recursive relationship, and some are trying with the manual for each loop and adding product counts of subcategories by looping through parent categories. I thought this is an expensive approach. There must be optimal way to do it. Please suggest me what could be the best way to deal with this.

Solution:

In your category::modal you have to use hasMany. A product belongs to a category. And a category has many products.

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

This will give you a product count ->withCount('products')

Keep me posted in the comments below.

Solution:

You can use sub select

$categories = Category::with('children')
        ->selectRaw('( SELECT COUNT(*) FROM `products` WHERE products.category_id IN (SELECT C.id from categories as C WHERE c.parent_id = categories.id) ) AS `product_count`')
        ->get();