hasManyThrough children (descendants)?

[FilipQL]

My root categories (root nodes) cannot have articles, but their child nodes (subcategories) can have articles. I am looking for the most elegant way to get the total number (count) of articles for the root categories (so, for every root category - get the number of articles that belong to its child categories).

I was thinking about using hasManyThrough but is that even possible? For example, in Category model we would have hasManyThrough relationship - one category has many articles through categories that are children?

This didn't work:

public function testing()
{
     return $this->hasManyThrough(Article::class, ArticleCategory::class, 'parent_id', 'category_id');
}

-----------------------------------------------------------------------------------------------
$categories = ArticleCategory::whereIsRoot()->withCount('testing')->get();

... there are no errors, but {{ $category->testing_count }} will always be 0.

[lazychaser]

You'll have to calculate article count for each node separetely. Or just store the value and update whenever articles added or nodes moved.

[medvem]

I make this with products. You must create a subselect like this:

$query->selectRaw('categories.*, categories._lft as lft, categories._rgt as rgt');

$query->addSelect(['products_count' => function ($builder) use ($discounted) {
	$builder->selectRaw("count(*)")
		->from('products')
		->join('categories as product_category', 'product_category.id', '=', 'products.category_id')
		->where(function ($q) {
			$q->whereRaw("`categories`.`id` = `products`.`category_id`");
			$q->orWhereRaw("product_category._lft > lft AND product_category._lft < rgt");
		});
}]);

This works with Root and Leaf categories also.

[MittyBoro]

@medvem thank you!!! otherwise I was already thinking of looking for something else