db: QueryBuilder: union and order by not works

I have:

$query1 = (new \yii\db\Query())
    ->select("id, category_id AS type, name")
    ->from('post')

$query2 = (new \yii\db\Query())
    ->select('id, type, name')
    ->from('user')
    ->orderBy('type');

$query1->union($query2);

This code generates the following SQL:

(SELECT `user`.`id`, `user`.`category_id` AS `type`, `user`.`name` FROM `post`) 
UNION
(SELECT `user`.`id`, `user`.`category_id`, `user`.`name`  FROM `post` ORDER BY `type`)

The SQL above does not order correctly. It’s necessary remove the parentheses. Ex:

SELECT `user`.`id`, `user`.`category_id` AS `type`, `user`.`name` FROM `post`
UNION
SELECT `user`.`id`, `user`.`category_id`, `user`.`name`  FROM `post` ORDER BY `type`

Funding

You can sponsor this specific effort via a Polar.sh pledge below
We receive the pledge once the issue is completed & verified

<picture> <source media="(prefers-color-scheme: dark)" srcset="https://polar.sh/api/github/yiisoft/db/issues/81/pledge.svg?darkmode=1"> Fund with Polar

</picture>

About this issue

Original URL
State: open
Created 9 years ago
Comments: 45 (26 by maintainers)

Links to this issue

🔥 Yii Database abstraction release

Commits related to this issue

Resolves #7992, don't add parenthesis around SELECT statements when using UNION — committed to nineinchnick/yii2 by nineinchnick 9 years ago

Most upvoted comments

Solved with third query (using PostgreSQL):

$incomes = (new Query())->select(['id', 'date', 'sum', 'currency_id'] )->from('incomes');
$expenses = (new Query())->select(['id', 'date', 'sum', 'currency_id'])->from('expenses');
$expenses->union($incomes, true)->orderBy(['date' => SORT_ASC]);

$query = new Query();
$query->select('*')->from(['u' => $expenses])->orderBy(['date' => SORT_DESC]);

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

return $this->render('transactions', [
    'dataProvider' => $dataProvider,
]);

+12

timofey-l on Sep 8, 2015

Hello. I have same problem. OrderBy not works for whole query. I need write terrible code like this:

$query->union($query_2, true); $query->union($query_3, true); $sql = $query->createCommand()->getRawSql(); $sql .= ’ ORDER BY sort_field '.($sort_asc ? ‘ASC’ : ‘DESC’); $query = MyActiveRecord::findBySql($sql);

Can you add methods for add orderBy, limit, offset to whole query? Maybe like this: $query->globalOrderBy([…]);

Sorry, if I not found existing solution. My english not good.

lzv on May 15, 2015

Hello. I have same problem. OrderBy not works for whole query. I need write terrible code like this:

$query->union($query_2, true); $query->union($query_3, true); $sql = $query->createCommand()->getRawSql(); $sql .= ’ ORDER BY sort_field '.($sort_asc ? ‘ASC’ : ‘DESC’); $query = MyActiveRecord::findBySql($sql);

Can you add methods for add orderBy, limit, offset to whole query? Maybe like this: $query->globalOrderBy([…]);

Sorry, if I not found existing solution. My english not good.

i did something like this, the dirty but it works on all machine

cethol on Jan 6, 2023

yes, and it returns an array. the problem is that i want the ActiveRecord objects with eager-loaded data specified in the ‘with’ parameters. doing a sorted union necessarily means losing eager-loaded data and any custom fields created in the active record class. not a proper solution.

jpodpro on Dec 17, 2015