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

[jpodpro]

i have found yii's built-in query system to be extremely insufficient for medium complexity queries. as such here is the only way i can find to generate the results i need. however as stated above this doesn't even work because 'from' in Query expects a string or an array, not an object as returned by the union:

$joinExpression = new \yii\db\Expression( '
                    `tag_suggestion`.`item_id` = `track`.`id`
                AND `tag_suggestion`.`item_type` = '.Yii::$app->params['itemTypes']['track'].'
                AND `tag_suggestion`.`tag_id` = `tag`.`id`
                AND `tag_suggestion`.`action` = '.TagSuggestion::TAG_REMOVE.'
                AND `tag_suggestion`.`user_id` = '.Yii::$app->user->identity->id );

$tracks = Track::find()
->where( ['public' => 1] )
->with( 'owners', 'userVoteState', 'waveform' )
->joinWith( 'tags' )
->leftJoin( 'tag_suggestion', $joinExpression )
->andWhere( ['tag.name' => $tagName, 'tag_suggestion.action' => null] );

$sql = "SELECT `track`.*
                        FROM `track`, `tag_suggestion`, `tag`
                        WHERE `tag_suggestion`.`action` = :action
                        AND `tag_suggestion`.`item_type` = :item_type
                        AND `tag_suggestion`.`user_id` = :user_id
                        AND `tag_suggestion`.`item_id` = `track`.`id`
                        AND `tag_suggestion`.`tag_id` = `tag`.`id`
                        AND `tag`.`name` = :tag_name";

$tracks2 = Track::findBySql( $sql, [
':action'       => TagSuggestion::TAG_ADD,
':item_type'    => Yii::$app->params['itemTypes']['track'],
':user_id'      => Yii::$app->user->identity->id,
':tag_name'     => $tagName ] );

$allTracks = ( new \yii\db\Query() )
->select( '*' )
->from( $tracks->union( $tracks2 ) )
->orderBy( ['creation_date' => SORT_DESC] )
->limit( 10 )
->all();

i'm assuming my union isn't working because i'm using two ActiveRecord instances instead of Query instances. but how am i supposed to do eager loading with my relations when using the query builder?

i feel as though i've encountered a number of situations where manual queries are the only option. it would be extremely helpful to have something (...anything) in the documentation suggesting that there are such cases. i have wasted days trying to make queries with medium complexity work within the system when it appears impossible. being honest with the limitations of yii in your documentation would be considerate to those of us who have (perhaps irresponsibly) decided to use it.

[jpodpro]

this particular issue has also been mentioned in #2175

[cozumel424]

And yiisoft/yii2#5677 and yiisoft/yii2#8313

Yii is a great framework but active record is so broken, it's just not usable.

[jpodpro]

:(
i have wasted so much time trying to make active record work. what a disappointment.

@qiangxue @cebe @samdark
PLEASE try to describe the limitations in the documentation. i would like to believe others can be saved the trouble i've been through.

[SilverFire]

@jpodpro did you try ->from(['a' => $tracks->union($tracks2)])?

[jpodpro]

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.

[dynasource]

Sqlite has troubles with subqueries and ordering + limits.

The following will not work and generate "General error: 1 ORDER BY clause should come after UNION ALL not before". If you use parentheses, this would create errors too.

$subQuery->select(['sum(uniq) as uniq','sum(pageviews) as pageviews','url']);
$subQuery->groupBy($this->groupBy);
$subQuery->orderBy($this->orderBy);
$subQuery->limit(1000);
$mainQuery = $fromQuery->union($subQuery);

A solution exists, fortunately. You should force a unioned subquery to be its own child.
An example for this:

$subQuery->select(['sum(uniq) as uniq','sum(pageviews) as pageviews','url']);
$subQuery->groupBy($this->groupBy);
$subQuery->orderBy($this->orderBy);
$subQuery->limit(1000);

//Add:
$subQuery= (new Query)->from(['alias'=>$subQuery]);

$mainQuery = $fromQuery->union($subQuery);

[7flash]

Can we doing something with the problem @jpodpro described?

[rizwanmcs]

i have following query which give me wrong result and send all box_id from user_posts_boxes table and also without matching and limit too.
can anyone help me ?

SELECT  
box_id,
B.id, 
B.NAME, 
B.picture, 
(SELECT Count(user_posts_boxes.id) 
FROM   user_posts_boxes, 
user_posts 
WHERE  user_posts.id = user_posts_boxes.user_posts_id 
AND B.id = user_posts_boxes.box_id 
AND B.archive = 0 
AND user_posts.archive = 0) AS postCount 
FROM   box B , user_posts UP
LEFT JOIN box BX ON BX.id IN ( 
SELECT user_posts_boxes.box_id FROM user_posts_boxes WHERE user_posts_boxes.box_id = BX.id ORDER BY id DESC LIMIT 9
) 

WHERE  B.user_id = 43 
AND B.archive = 0 
ORDER  BY B.id DESC

[SilverFire]

@rizwanmcs

Thank you for your question.
In order for this issue tracker to be effective, it should only contain bug reports and feature requests.

We advise you to use our community driven resources:

• The support forum
• The IRC chat room
• The Gitter chat room
• The Definitive Guide to Yii

This is an automated comment, triggered by adding the label question.

[SamMousa]

Related to #12968

[igorveremsky]

I use own UnionQueryHelper to build global queries for multiply ActiveRecord models. Here it is:

namespace frontend\helpers;

use yii\db\ActiveQuery;
use yii\db\ActiveRecord;
use Yii;

class UnionQueryHelper {
	/* **************************GET VARIABLES HELPER FUNCTIONS**********************************/

	/**
	 * Get intersect attributes for models
	 *
	 * @param ActiveRecord[] $modelClassNames
	 * @param array $excludeAttributes
	 *
	 * @return mixed
	 */
	public static function getIntersectAttributes(array $modelClassNames, array $excludeAttributes = []) {
		$modelsAttributes = [];
		foreach ($modelClassNames as $modelClassName) {
			$modelsAttributes[] = array_keys($modelClassName::getTableSchema()->columns);
		}

		$intersectAttributes = call_user_func_array('array_intersect', $modelsAttributes);

		foreach ($excludeAttributes as $excludeAttribute) {
			$key = array_search($excludeAttribute, $intersectAttributes);
			if ($key !== false) {
				unset($intersectAttributes[$key]);
			}
		}

		return $intersectAttributes;
	}

	/* **************************FIND DATA FUNCTIONS**********************************/

	/**
	 * Get union active query for multiply models
	 *
	 * @param ActiveRecord[] $modelClassNames
	 * @param array $excludeAttributes
	 * @param int $limit
	 * @param int $offset
	 * @param array $orderOptions
	 *
	 * @return ActiveQuery
	 */
	public static function findUnion(array $modelClassNames, $returnModelClassName = '', array $excludeAttributes = [], $limit = 0, $offset = 0,  array $orderOptions = ['created_at' => SORT_DESC]) {
		foreach ($modelClassNames as $modelClassName) {
			$modelsQuery[] = $modelClassName::find();
		}

		$unionAttributes = self::getIntersectAttributes($modelClassNames, $excludeAttributes);
		$returnModelClassName = (!empty($returnModelClassName)) ? $returnModelClassName : $modelClassNames[0];

		return self::unionQuery($modelsQuery, $unionAttributes, $returnModelClassName, $limit, $offset, $orderOptions);
	}

	/* **************************DATA HELPER FUNCTIONS**********************************/

	/**
	 * Build union query for multiply models query with global specific options
	 *
	 * @param ActiveQuery[] $modelsQuery
	 * @param $unionAttributes
	 * @param $returnModelClassName
	 * @param int $limit
	 * @param int $offset
	 * @param array $orderOptions
	 *
	 * @return ActiveQuery
	 */
	public static function unionQuery($modelsQuery, $unionAttributes, $returnModelClassName, $limit = 0, $offset = 0,  array $orderOptions = ['created_at' => SORT_DESC]) {
		$limit = (int) $limit;
		$offset = (int) $offset;

		foreach ($modelsQuery as $key => $modelQuery) {
			$modelsQuery[$key] = $modelQuery->select($unionAttributes);

			if ($limit !== 0) {
				$modelsQuery[$key]->limit($limit + $offset);
			}

			if ($key == 0) {
				$unionModelsQuery = $modelsQuery[$key];
			} else {
				$unionModelsQuery->union($modelsQuery[$key]);
			}
		}

		$unionQuery = (new ActiveQuery($returnModelClassName))->from(['u' => $unionModelsQuery]);

		if ($limit !== 0) {
			$unionQuery->limit($limit);
		}

		if ($offset !== 0) {
			$unionQuery->offset($offset);
		}

		foreach ($orderOptions as $orderAttributeKey => $orderSort) {
			if (!in_array($orderAttributeKey, $unionAttributes)) unset($orderOptions[$orderAttributeKey]);
		}

		if (!empty($orderOptions)) {
			$unionQuery->orderBy($orderOptions);
		}

		return $unionQuery;
	}
}

It doesnt have validations for variables rendered to functions but works :) My task was to view on home page projects and posts ordered by created_at attribute.
Example in use for specific task:

namespace frontend\helpers;

use frontend\models\Post;
use frontend\models\Project;
use yii\db\ActiveQuery;
use Yii;

class HomeModelHelper {
	/**
	 * Find models viewed on home page
	 *
	 * @param $q
	 * @param int $limit
	 * @param int $offset
	 *
	 * @return ActiveQuery
	 */
	public static function findHome($limit = 0, $offset = 0) {
		$modelClassNames = [Project::className(), Post::className()];

		foreach ($modelClassNames as $modelClassName) {
			$modelsQuery[] = $modelClassName::find()->where(['is_home' => 1];
		}

		$unionAttributes = UnionQueryHelper::getIntersectAttributes($modelClassNames);

		return UnionQueryHelper::unionQuery($modelsQuery, $unionAttributes, $modelClassNames[0], $limit, $offset);
	}
}

Hope this code helps for someone.

[KINGMJ]

you can use this:

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

;

return (new Query())->select(*)->from($query1->union($query2))

if you use ActiveQuery, for example:

	$query1 = $this->find()
			->where(['ent_id' => $entId])
			->andWhere('status !=' . STATUS_DELETED)
			->andWhere(['in' , 'dept_id' , $deptIds]);

		$query2 = $this->find()
			->where(['creator_id' => $creatorId , 'dept_id' => $deptId])
			->andWhere('status !=' . STATUS_DELETED);

		return $this->find()->select("*")
			->from($query1->union($query2))
			->orderBy(['requirement_id' => SORT_DESC]);


private function find(): ActiveQuery {
		return RequirementPo::find()->with('dept' , 'budget' , 'officeAddress' , 'staffs');
}

this will get an ActiveQuery object

[cethol]

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

[samdark]

yiisoft/active-record#65 (comment)