Как сделать подзапрос в AR в select на YII2?

Question

[Максим]

Здравствуйте!

Есть 2 таблицы: договоры и штрафы. У договра может быть много штрафов, у штрафа только один договор. Есть модели: Contract и ContractSearch. Есть view и GridView. В GridView выводятся договоры.

Мне надо, подсчитать кол-во штрафов у договора, сумму всех неоплаченных штрафов и т.д.
Можно ли это сделать подзапросом?
Примерно таким, но в рамках 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

[OKyJIucT]

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

Comments

[Максим]

Если, ты прочтешь внимательно, там подзапрос в WHERE, а мне надо в SELECT.

[Максим]

Можно, сделать так

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

Но, как потом из модели обратиться к total_fines?

[Максим]

Вопрос решен. Спасибо, навел на правильную мысль.