Yii how to remove 101 request when getting related data?

Question

Yii

Hello! Relationship question. I just can’t figure out where to do so that there are not many requests when using links in the foreach () loop 1. I get a model for the view 2. In the view I have the following code with related datawith('')

$model = Event::findOne($id)

<div class="profile-info-block">
            <div class="profile-info-full">
                <div class="event-users-block">
                    <?php if ($model->appointments[0]):?>
                        <?php foreach (array_unique(\yii\helpers\ArrayHelper::getColumn($model->appointments,'arena')) as $item => $value):?>
                            <div class="row">
                                <div class="col-sm-12"><?=$model->appointments[0]->arenaList[$value]?></div>
                                <?php foreach ($model->appointments as $appointment): ?>
                                    <?php if ($appointment->arena == $value):?>
                                        <?php if ($appointment->isAppointed):?>
                                            <div class="col-xs-12 col-sm-6 col-md-4 profile-users">
                                                <div class="profile-list-content">
                                                    <div class="profile-users-list border-bottom-none">
                                                        <div class="profile-users-list-row">
                                                            <div class="profile-users-photo-wrap">
                                                                <a class="profile-users-photo" href="<?=\yii\helpers\Url::to(['/user/profile/view', 'id'=> $appointment->certification->user_id])?>">
                                                                    <?=Html::img($appointment->profile->fullAvatarUrl,['class'=>'profile-users-photo-img'])?>
                                                                </a>
                                                            </div>

                                                            <div class="profile-users-info">
                                                                <div class="profile-user-title">
                                                                    <?=Html::a($appointment->profile->miniName, ['/user/profile/view', 'id'=> $appointment->certification->user_id])?>
                                                                </div>
                                                                <div class="profile-text">
                                                                    <?=$appointment->certification->infoCertification?>
                                                                </div>
                                                                <div class="admin">
                                                                    <?php
                                                                    if (Yii::$app->user->can('admin')) {
                                                                        echo  Html::a('<i class="fas fa-pencil-alt"></i>', ['/event/appointment/update', 'id' => $appointment->id], [
                                                                            'class' => 'btn-default btn-sm',
                                                                            'title' => Yii::t('appointment', 'Update')
                                                                        ]);
                                                                        echo Html::a('<i class="fa fa-trash"></i>', ['/event/appointment/delete', 'id' => $appointment->id], [
                                                                            'class' => 'btn-default btn-sm',
                                                                            'title' => Yii::t('appointment', 'Delete'),
                                                                            'data-confirm' => Yii::t('appointment', 'Are you sure to delete this item?'),
                                                                            'data-method' => 'post',
                                                                        ]);
                                                                    }
                                                                    ?>
                                                                </div>
                                                            </div>
                                                        </div>
                                                    </div>
                                                </div>
                                            </div>
                                        <?php endif;?>
                                    <?php endif;?>
                                <?php endforeach; ?>
                                <hr>
                            </div>
                        <?php endforeach; ?>
                    <?php endif;?>
                </div>
            </div>
        </div>

3. Connections are made like this

Event Model

class Event extends \yii\db\ActiveRecord
{
      ......
      /**
     * @return \yii\db\ActiveQuery
     */
    public function getAppointments(): ActiveQuery
    {
        return $this->hasMany(Appointment::className(), ['event_id' => 'id'])
            ->with('certification')
            ->orderBy('arena ASC');
    } 
      ......
}

Appointment Model

......
         class Appointment extends \yii\db\ActiveRecord
         {
          /**
     * Получение аттестации
     * @return \yii\db\ActiveQuery
     */
    public function getCertification()
    {
        return $this->hasOne(Certification::className(), ['id' => 'judge_comitet_id']);
    }

    /**
     * Получение профилей
     * @return \yii\db\ActiveQuery
     */
    public function getProfile()
    {
        return $this->hasOne(Profile::className(), ['user_id' => 'user_id'])
            ->via('certification');
    }
         }
         .......

Such repeated requests appear in the debug panel. I want to get rid of them because there are more than 100 requests!!!

Maybe then not to receive this data through connections, but in the controller with one request and pass it to the view?
I will be very grateful for criticism and for any help)))

Answer 1

Figured it out myself. Made with and joinWith directly into the connection

/**
     * @return \yii\db\ActiveQuery
     */
    public function getAppointmentsJudges(): ActiveQuery
    {
        return $this->hasMany(Appointment::className(), ['event_id' => 'id'])
            ->joinWith(['certification', 'certification.comitet', 'certification.category'])
            ->with(['profile'])
            ->andWhere([Judge::tableName().'.role' => Judge::ROLE]);
    }

Answer 2

$model = Event::find()->where(['id' => $id])->with([appointments])->one();

so you can try

Answer 3

Good afternoon.
If I'm not mistaken, you can use it like this

$model = Event::find()->where(['id' => $id])->with(['appointments.certification'])->one();