How to properly optimize links in Yii2?

Question

Yii

Good evening. There are three tables:
1) users [id, site_id]
2) users_fields [id, user_id, site_id]
3) users_fields_value [id, user_id, field_id]
Correct SQL that needs to be translated into Yii2 relationship logic (getFieldValue):

select 
users.id,
users_fields.name,
users_fields_value.value
from `users`
join `users_fields` on users.site_id=users_fields.site_id
left join `users_fields_value` on users_fields.id=users_fields_value.field_id
and users_fields_value.user_id=users.id
where users.site_id=14

It is enough to specify the site identifier and all additional fields and their values ​​will be unloaded
+ and users.id=X <- unloading a specific user from the site and its additional
fields ) and the value of each field
Now everything works like this: for each user, one request is made to select additional fields, and then their values. As a result, the number of requests to the database goes off scale ...

public function getListUsers($site_id) 
{
        if( ! ( $userModel = Site::findModel($site_id) ) ) {
            return false;
        }
       $models = $userModel->getUsers()
                    ->where([
                        'status' => 1
                    ])
                    ->all();

        if( ! $models ) {
            throw new NotFoundHttpException('Users not found');
        }

        $result = [];

        foreach($models as $model) {
            $result[] = [
                'name' => $model->name,
                'other' => $this->getArrayFieldValues($model->fields, $model->id)
            ];
        }
        
        return $result;
}

private function getArrayFieldValues(array $models, int $user_id)
    {
        $result = [];

        foreach ($models as $model) {
            if( ! ($value = $model->getFieldValue($user_id)->one()) ) {
                continue;
            }

            $result[$model->name] = $value->value;
        }

        return ! empty($result) ? $result : false;
    }

model Site.php:

public static function findModel($id)
    {
        if( ! ($model = self::findOne($id)) ) {
            throw new NotFoundHttpException(Yii::t('sites', 'Website is not found'));
        }

        return $model;
    }

public function getUsers()
    {
        return $this->hasMany(Users::className(), ['site_id' => 'id']);
    }

modelUsers.php

public function getFields()
    {
        return $this->hasMany(UsersFields::className(), ['site_id' => 'site_id']);
    }

model UsersFields.php

public function getFieldsValue()
    {
        return $this->hasMany(UsersFieldsValue::className(), ['field_id' => 'id']);
    }

    public function getFieldValue($user_id)
    {
        return $this->getFieldsValue()
                    ->where(['user_id' => $user_id]);
    }

Answer 1

Set relationships for models .