yii2: put the query union on the last object of first query

Question

I have script on yii2:

$query = HrKaryawan::find()
            ->where(['hr_karyawan.outlet_id' => $params['outlet_id']]);

$subquery = (new \yii\db\Query())
                            ->from('hr_karyawan')
                            ->where(['=', 'id',1]);
$query->union($subquery, true);

$dataProvider = new ActiveDataProvider([
            'query' => $query,
            'pagination' => array('pageSize' => 10),
]);

the result is subquery always be on the last in each page that in row 11 on page 1, row 21 on page 2, ...

I want to make it only on the last row and last page. can anyone help me?

Answer 1

it will display 11 because limit is put on the first query, subquery will be attached afterwards

(SELECT * FROM `hr_karyawan` LIMIT 10) UNION ALL ( SELECT * FROM `hr_karyawan` WHERE `id` = 1);

solution could be to use a wrapper query:

$wrapperQuery = new Query();
$wrapperQuery->from($query);

your code would look like something like this:

$query = HrKaryawan::find()
            ->where(['hr_karyawan.outlet_id' => $params['outlet_id']]);

$subquery = (new \yii\db\Query())
                            ->from('hr_karyawan')
                            ->where(['=', 'id',1]);
$query->union($subquery, true);

$wrapperQuery = new Query();
$wrapperQuery->from($query);

$dataProvider = new ActiveDataProvider([
            'query' => $wrapperQuery,
            'pagination' => array('pageSize' => 10),
]);