QueryBuilder: union and order by not works

[leandrogehlen]

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

[cebe]

both results are possible so it depends on how you apply operators.

Correct expected result would be:

(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`

can you try the following?

$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');

$query1->union($query2)->orderBy('type');

[leandrogehlen]

I have tried your suggestion, but not works, the result was:

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

I think, currently, is not possible to generate, this way:

(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`

[mdmunir]

$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');

(new yii\db\Query())
    ->select('*')
    ->from($query1->union($query2))
    ->orderBy('type');

[nineinchnick]

@cebe the parenthesis are not necessary here. It's not only a doc issue, I'll prepare a PR for review today.

[qiangxue]

The parenthesis does make difference. We cannot remove it. You should use what @mdmunir suggested here.

[nineinchnick]

@qiangxue they do, ORDER and LIMIT is applied after UNION, not to the specific SELECT. For a reference, see the PostgreSQL SELECT docs. I could make a more detailed unit test if you need it.

[qiangxue]

@nineinchnick Yes, that's why we add parenthesis. If we drop them, then there will be no way to order each SELECT separately.

[nineinchnick]

@qiangxue then you should use a subquery or a CTE. Why you don't want to support valid SQL?

[nineinchnick]

This throws a syntax error:

select * from "order" where id IN (2,3) ORDER BY id DESC
UNION ALL
select * from "order" where id IN (1) ORDER BY id ASC

This does not:

select * from "order" where id IN (2,3)
UNION ALL
select * from "order" where id IN (1) ORDER BY id ASC

I'd rather keep to the standard. Should I provide more arguments? Maybe check other ORMs?

Even if you find use cases to have UNION results sorted differently you make query builder behave opposite to the databases, which is confusing.

[qiangxue]

That's why we need to keep the parenthesis because without the parenthesis it will throw the syntax error as you described.

[nineinchnick]

But what's the reason to add them in the first place? You didn't have them in Yii1 and I'm pretty sure other ORMs don't do that.

[qiangxue]

The current implementation was actually based on a reported issue.

Also syntactically, $query1->union($query2) should treat both query objects equivalently. While dropping the parenthesis, $query1 behaves differently from $query2 because its ORDER BY is used globally.

[qiangxue]

But what's the reason to add them in the first place? You didn't have them in Yii1 and I'm pretty sure other ORMs don't do that.

With the current implementation, it is possible to support both scenarios: order SELECT separately, order globally. By dropping the parenthesis, it's essentially a conceptual mess (note also the problem is not just limited to ORDER BY).

[nineinchnick]

What's the issue number?

Why it should treat both queries same if the SQL standard does not? Are you trying to fix the SQL standard here?

[qiangxue]

We are not fixing SQL standard. We are just trying to cover all possible SQL use cases.

[nineinchnick]

But you even out the rarely used cases with most common ones.

[nineinchnick]

I've read the docs I referenced more thoroughly and also checked out some other projects. I'd keep this issue open, I may up come with a better solution than my last PR.

[qiangxue]

But you even out the rarely used cases with most common ones.

Well, it may be rare, but with your implementation, this rare case becomes impossible to do. Moreover, besides ORDER BY, you should also consider other clauses, such as LIMIT, which are not that rare at all.

I don't think referencing other projects or the docs would help here. The problem is clear: in a UNION statement, there are local clauses and global clauses.

• Our current implementation makes all clauses to be local to the corresponding queries, and if you want global clauses, you do it via a sub-query.
• In your implementation, the first query will uses global clauses while the rest of the queries local clauses. Then the problem occurs when you want to use local clauses for the first query (e.g. ORDER BY, LIMIT, GROUP BY).

Yes, our current implementation is not ideal because we don't want to use sub-queries if possible. But I can't find a better way to solve this problem in order to keep using our query syntax. Your implementation is not acceptable not only because it breaks BC, but also because it is functionally crippled.

[nineinchnick]

@qiangxue you're right, I've got a little too emotional after closing the issue so fast. You were right to reject my PR. Give me a few days to think if this can be solved.

I did some cleanup and added missing tests in that PR so I'll make another one without the UNION changes.

[qiangxue]

@nineinchnick No problem. I closed the issue and PR quickly because I am very sure about the current implementation which was the result of some considerable thoughts in order to fix a similar issue. Yes, I'd like to see if there is a better solution.

[lzv]

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.

[luke-]

Fyi, there is also a problem with union parentheses in this example:

$search = (new Query())...->union($union2);
$find = (new Query())...->where(['IN', 'field', $search]);

[ghost]

Any plan to fix global union functionality?

[nokimaro]

Same problem. Need to sort union results.

[cozumel424]

Same problem here. Although Izv's solution works great.

[timofey-l]

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,
]);

[jeesus]

timofey-l, what if I need to left join other table for both of them (incomes and expenses) in order to get the project title they are associated with? That table doesn't have date field and so the thing breaks when trying to sort by date.

Going with ActiveQuery doesn't work also, then the sorting's all messed up.

[timofey-l]

jeesus, do you mean something like this?

$incomes = (new Query())->select(['id', 'date', 'sum', 'currency_id', 'p.title project_title'] )
        ->from('incomes')
        ->leftJoin('projects p', 'p.id = incomes.project_id');
$expenses = (new Query())->select(['id', 'date', 'sum', 'currency_id', 'p.title project_title'])
        ->from('expenses')
        ->leftJoin('projects p', 'p.id = expenses.project_id');
$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,
]);

resulting query after executing should have field project_title I'm not sure, but this code should work, as i think =)

[jpodpro]

i tried this using the suggestion by @mdmunir - it does not work. i get the following error:

trim() expects parameter 1 to be string, object given

from Query.php line 486 - in the 'from' function

public function from($tables)
    {
        if (!is_array($tables)) {
            $tables = preg_split('/\s*,\s*/', trim($tables), -1, PREG_SPLIT_NO_EMPTY);
        }
        $this->from = $tables;
        return $this;
    }

it looks like you cannot pass $query1->union( $query2 ) as the from parameter to a query. i do not understand why his suggestion was accepted.

@qiangxue , can you explain how a sub-query is supposed to allow for setting global order by and limit clauses when using union?

[SilverFire]

@jpodpro show your code, that leads to the error trim() expects parameter 1 to be string, object given