union and limit

[michaelnguyen2021]

I am trying to union 2 queries, but the LIMIT is inside first query, instead of at the outer most level

here is my code

In CostCodeQuery.php

public function filterByCurrentUserCompanyAndProjectPermission($params)
    {

        $companyParent = Company::findOne(['parent_company_id' => null]);

        $costCodeCompanySpecific = \common\models\CostCode::find()
            ->innerJoin(
                'user_has_project', 
                'user_has_project.project_id = cost_code.project_id AND user_has_project.user_id = :userId',
                ['userId' => Yii::$app->user->id]
            )->where([
                'cost_code.company_id' => Yii::$app->user->identity->company_id
            ]);

        $costCodeCompanySpecific->andFilterWhere(['like', 'description', $params->description])
            ->andFilterWhere(['like', 'code', $params->code]);

        $this->union($costCodeCompanySpecific);

        $this->andWhere(['cost_code.project_id' => NULL]);

        return $this->andFilterWhere(['or',
            ['cost_code.company_id' => Yii::$app->user->identity->company_id],
            ['cost_code.company_id' => $companyParent->id]
        ]);
    }

In CostCodeSearch.php

    public function search($params)
    {
        $this->load($params);

        $query = CostCode::find()->filterByCurrentUserCompanyAndProjectPermission($this)->with('company', 'project', 'costCodeTypes', 'claimingRules');

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
        ]);

        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;
        }

        $query->andFilterWhere([
            'id' => $this->id,
            'discipline_id' => $this->discipline_id,
            'measurement_unit_id' => $this->measurement_unit_id,
            'project_id' => $this->project_id,
            'client_afe_id' => $this->client_afe_id,
        ]);

        $query->andFilterWhere(['like', 'description', $this->description])
            ->andFilterWhere(['like', 'code', $this->code]);

        return $dataProvider;
    }

In CostCodeController.php

    public function actionIndex()
    {
        $searchModel = new CostCodeSearch();
        $dataProvider = $searchModel->search(Yii::$app->request->queryParams);

        return $this->render('index', [
            'searchModel' => $searchModel,
            'dataProvider' => $dataProvider,
        ]);
    }

The generated query is

(SELECT * FROM `cost_code`
 WHERE (`cost_code`.`project_id` IS NULL) 
AND ((`cost_code`.`company_id`=1) OR (`cost_code`.`company_id`=1)) LIMIT 20) 
UNION 
( 
SELECT `cost_code`.* FROM `cost_code` 
INNER JOIN `user_has_project` ON user_has_project.project_id = cost_code.project_id 
AND user_has_project.user_id = 5 WHERE `cost_code`.`company_id`=1 )

You can see LIMIT 20 is placed inside the first query. I want it outside of both query so I expect it to look like this

(SELECT * FROM `cost_code`
 WHERE (`cost_code`.`project_id` IS NULL) 
AND ((`cost_code`.`company_id`=1) OR (`cost_code`.`company_id`=1)) ) 
UNION 
( 
SELECT `cost_code`.* FROM `cost_code` 
INNER JOIN `user_has_project` ON user_has_project.project_id = cost_code.project_id 
AND user_has_project.user_id = 5 WHERE `cost_code`.`company_id`=1 )
LIMIT 20

Do I do something wrong ?

[klimov-paul]

Same as #7992

[michaelnguyen2021]

@klimov-paul Is this a limitation of Yii2 Active Record ? If yes, will there be a work on that ? If no, would you recommend how to fix my issue ?

[klimov-paul]

You will find all your answers at #7992