Yii2 how to extract nested links and get their count?

Question

Yii

I do statistics for the user.
Days, weeks, months. year. all times and date ranges.
There are three entities on which all this is tied, they are all glued one to many
Partner-> Coupon-> Order
There is a partner_id in the coupon
table There is a partner_id, coupon_id in the order table
Everywhere there are creation dates in UNIX from the TimeStampBehavior behavior
on the controller for each period an action is created . On the front, it's just buttons.
For the whole period I did as follows. received the id of the current partner and pulled out all the coupons belonging to him. shoved it into the data provider, but on the frontend. made an additional request for counts.
provider

<table class="table table-hover">
            <tr>
                <td><strong>Название купона</strong></td>
                <td><strong>Покупок</strong></td>
            </tr>
            <?= \yii\widgets\ListView::widget([
                'dataProvider' => $couponsProvider,
                'itemView' => '_coupons',
            ]) ?>
        </table>

counter directly.

<?php
use yii\helpers\Html;
use yii\helpers\Markdown;

//получаем кол-во
$orders = \common\models\Order::find()->where(['coupon_id' => $model->id])->count(); ?>

<tr>
    <td><?= Html::encode($model->title) ?></td>
    <td><?= Html::encode($orders) ?></td>
</tr>

Actually, by dates, I put the conditions by date into the count method. And before that, I convert the date in a similar way

$timestamp = time();
    $dates = Yii::app()->dateFormatter->format('dd-MM-yyyy', $timestamp);
    $date = new DateTime($dates);
    $date->modify('-1 day');
    $date_elements  = explode("-",$date->format('d-m-Y'));
    $beforeTime = mktime(0,0,0,$date_elements[1],$date_elements[0],$date_elements[2]);

And it turns out that +1 request for each coupon. Coupons have statuses and archived coupons do not participate in statistics. In fact, there will be no more than 10 product coupons. But anyway, it’s written with crutches and I don’t know how to improve it through the provider, or how would it be more correct to implement statistics in general? Considering now that there are connections, but somehow I can’t use them because we get a partner, then a bunch of his coupons, and then all orders for these coupons.

Answer 1

As far as I remember, there was a special type of connection in yii, which was called a static request. this special link was just intended for aggregation queries. in the second yii I think this should also be