2

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

rock-ass
  • 475
  • 5
  • 16

1 Answers1

0

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.

rock-ass
  • 475
  • 5
  • 16
Ali MasudianPour
  • 14,329
  • 3
  • 60
  • 62
  • Well the dirty way can make some unexpected results, but it may save some time, I think I'll use the dirty way, but use `preg_replace` instead of `str_replace` and search for whole words, hopefully lower possibility of replacing wrong words :) thank you for clarification – rock-ass Nov 27 '14 at 15:25
  • In my case union not work at all. When I do `$cmd1->union($cmd2->getText())` I have a problem with parameters inside $cmd2: `Invalid parameter number: number of bound variables does not match number of tokens` – gandra404 May 18 '18 at 09:25