How to make union all command using Yii framework command DAO object

Question

I am trying to make a union of a few tables data into one query using. I am using Yii framework (v1.15). I have done it like this and this worked fine:

$command = Yii::app()->db->createCommand();
$command->select('something')->from('somewhere');
$command->union($sql);

This produces something like this:

SELECT SOMETHING FROM SOMEWHERE UNION (SELECT ....)

But union merges duplicate row values. I need to combine all of the data using UNION ALL, but I can't find anything about how to do it in the documentation.

Maybe some of you how can I do it using the yii database object? Thank you

Answer 1

In Yii 1.x.x's query builder, UNION ALL is not supported. You can change it to:

Yii::app()->db->createCommand("SELECT something FROM somewhere UNION ALL({$sql})");

Another way is to override union() and buildQuery() methods in CDbCommand command or create unionAll() method by inheriting CDbCommand.

---

Another dirty way would be:

$sql=Yii::app()->db->createCommand()->select('something')->from('somewhere')->union("SELECT something FROM somewhere")->getText();

Which is equal to:

SELECT `something` FROM `somewhere` UNION ( SELECT something FROM somewhere)

Then:

$sql=  str_replace('UNION', 'UNION ALL', $sql);

Or using regular expression:

$command->text = preg_replace('/\sUNION\s/', ' UNION ALL ', $command->text);

Which is equal to:

SELECT `something` FROM `somewhere` UNION ALL ( SELECT something FROM somewhere)

Then, pass it through createCommand() method.