How to make a subquery in AR in select on YII2?

Question

This

Hello!
There are 2 tables: contracts and fines. A contract can have many fines, a fine has only one contract. There are models: Contract and ContractSearch. There is a view and a GridView. Contracts are displayed in the GridView.
I need to calculate the number of fines for the contract, the amount of all unpaid fines, etc.
Can this be done with a subquery?
Something like this, but within the framework of AR:

SELECT id, name (SELECT count(id) FROM tbl_contract_fines WHERE tbl_contracts.id=contract_id) as total FROM tbl_contracts

Метод search() в ContractSearch.

public function search($params)
    {
        $query = Contract::find();
        $query->joinWith('client');
        $query->joinWith('car');

        $query->where('(SELECT count(id) FROM tbl_fines WHERE contract_id=tbl_contracts.id)> 0');

        // add conditions that should always apply here

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
            'pagination' => [
                'pageSize' => 25
            ],
            'sort' => [
                'defaultOrder' => [
                    'name' => SORT_ASC
                ]
            ],
        ]);

        $dataProvider->setSort(ArrayHelper::merge([
            'attributes' => $dataProvider->getSort()->attributes,
        ],[
            'attributes'=>[
                'client_name' => [
                    'asc'=>['tbl_clients.name'=>SORT_ASC,],
                    'desc'=>['tbl_clients.name'=>SORT_DESC],
                ],
                'car_name' => [
                    'asc'=>['tbl_cars.name'=>SORT_ASC,],
                    'desc'=>['tbl_cars.name'=>SORT_DESC],
                ],
            ]
        ]));

        $this->load($params);

        if (!$this->validate()) {
            // uncomment the following line if you do not want to return any records when validation fails
            // $query->where('0=1');
            return $dataProvider;
        }

        // grid filtering conditions
        $query->andFilterWhere([
            'id' => $this->id,
            'contract_status_id' => $this->contract_status_id,
            'contract_type_id' => $this->contract_type_id,
            'contract_date' => $this->contract_date,
            'paid_before' => $this->paid_before,
            'num_days' => $this->num_days,
            'client_id' => $this->client_id,
            'car_id' => $this->car_id,
            'tariff_id' => $this->tariff_id,
            'power_of_attorney_date' => $this->power_of_attorney_date,
            'car_mileage_end' => $this->car_mileage_end,
            'updated_at' => $this->updated_at,
            'created_at' => $this->created_at,
        ]);

/*        $query->orWhere(" CONCAT_WS(' ', tbl_clients.name, tbl_clients.patronymic, tbl_clients.surname)  like :name ", [
            ':name' => '%' . $this->client_name . '%'
        ]);*/


        $query
            //->andFilterWhere(['like', 'tbl_clients.name', $this->client_name])
            //->andFilterWhere(['like', new Expression('CONCAT_WS("-", tbl_clients.surname)'), $this->client_name])
            ->andFilterWhere(['like', 'tbl_cars.name', $this->car_name])
            ->andFilterWhere(['like', 'tbl_clients.phone_main', $this->client_phone])
            ->andFilterWhere(['like', 'name', $this->name])
            ->andFilterWhere(['like', 'number', $this->number])
            ->andFilterWhere(['like', 'contract_notes', $this->contract_notes])
            ->andFilterWhere(['like', 'point_issue_notes', $this->point_issue_notes])
            ->andFilterWhere(['like', 'power_of_attorney_file', $this->power_of_attorney_file])
            ->andFilterWhere(['like', 'power_of_attorney_num', $this->power_of_attorney_num])
            ->andFilterWhere(['like', 'power_of_attorney_gave', $this->power_of_attorney_gave]);

        return $dataProvider;
    }

UPD
Вопрос решен. Вот так можно:

$query = Contract::find()->select('tbl_contracts.*, (SELECT count(id) FROM tbl_fines WHERE tbl_contracts.id=contract_id) as total_fines');

Answer 1

Первый результат в гугле по запросу "Yii2 позапрос" https://stackoverflow.com/questions/30164491/yii2-...