Displaying the number of all posts in the parent category?

Question

Yii

Tell me how to display the number of posts in the category widget as a number so that the parent category takes into account child categories.
Here is the widget

class CategoriesWidget extends Widget
{
    /**
     * @var Category
     */
    public $category;
    public $posts_count;

    public function run()
    {
        $categories = Category::find()
            ->select(['{{%category}}.*', 'courses_count' => new Expression('COUNT(DISTINCT {{%post}}.id)')])
            ->joinWith(['posts'], false)
            ->groupBy('{{%category}}.id')
            ->orderBy('name')->all();

        $items = $this->getItemsRecursive($categories, null, $this->category);
        return $this->render('categories', [
            'items' => $items,
        ]);
    }

    /**
     * @var Category[] $categories
     * @param integer $parentId
     * @var Category $current
     * @return array
     */
    private function getItemsRecursive(&$categories, $parentId, $current)
    {
        $items = [];
        foreach ($categories as $category) {
            if ($category->parent_id == $parentId) {
                $items[] = [
                    'label' => $category->name,
                    'template' => '<a href="{url}" class="list-group-item">{label}<small class="badge">'.$category->posts_count.'</small></a>',
                    'url' => ['main/category', 'id' => $category->id],
                    'active' => $this->category && $category->id == $this->category->id ? true : null,
                    'items' => $this->getItemsRecursive($categories, $category->id, $current),
                ];
            }
        }
        return $items;
    }
}

Everything is displayed, but in the parent category only their posts are indicated, and the posts of child categories are not indicated, how to display the total number of posts?

Answer 1

you have to use recursion, but this is a big load on the base. You can of course cache this value. Or you can add a field to the category - number of posts and recalculate it in the trigger

Answer 2

There are several ways to implement your task, which is better - you need to determine depending on the specifics of your system:
1. Recursively extract data from child categories. This option will create a lot of hassle and loads
2. Recalculate the number of posts based on triggers. If the parent does not plan to change abruptly, this is quite a suitable option
3. You can join this data. If the nesting level is minimal, this option is quite suitable.
4. You can use to revise the structure of storing comments and use the nested sets mechanism or other mechanisms designed to work with nested data