How to do Counting the number of elements for statistics?

Question

Yii

In general, I want to display in a tree how many services were ordered and what categories.
3 tables.
category (id , name, description )price (id cateory_id name unit ptice) and Orders (id category_id price_id name telephone date processed)
I built the tree itself relative to the models

<div class="panel-body">
                                        <p>Всего заказов : 43<br>
                                            <hr>
                                        <?php echo '<pre>'. print_r($orders, true).'</pre>'?>
                                        Из них:<br>
                                        <?php  foreach ($cat as $item) :?>
                                        <ul class="treeview">

                                            <?php if ($item['id'] !=  17): ?>
                                            <li><a href="#"><?=$item['name']?> - 3</a>
                                                <ul>
                                            <?php  foreach ($price as $itemok) :?>
                                                <?php if ($item['id'] ==  $itemok['category_id']): ?>
                                                    <li><a href="#"><?=$itemok['name']?></a></li>
                                                <?php endif; ?>
                                            <?php endforeach; ?>
                                                </ul>
                                            </li>
                                            <?php endif; ?>

                                        </ul>
                                        <?php endforeach; ?>

                                    </div>

But now I want to have the number of orders next to each category and service (In the future with date limits)
I tried to shove the forich, the game is going on. The tables are all connected

Answer 1

I also encountered such a problem, but I have a tree of 4-5 levels, I sorted out different options, but just stopped at individual requests
1.

$query = Product::find();
$query->select(['category_id', 'count' => 'count(`id`)']);
$query->visible();
$query->andWhere(['category_id' => $categoryIds]);
$query->groupBy('category_id');
$query->asArray();

We got how many products each section has
2. Query the entire tree in reverse order, so that the last elements are children, then bypass the entire tree with a simple loop and add the number of elements, if the level or parent changes, then reset

Answer 2

Create the $ordersCount property in the category model, add a query to the database (LEFT JOIN and COUNT()) so that the number of category orders is equal to ordersCount, then display this property in the table.