Yii2 QueryBuilder union not working

Question

Here is my code. I want to select results from two columns into one. Thanks.

   $messages = (new \yii\db\Query())
               ->select(['idsender as idotherguy'])
               ->from('messages')
               ->where(['idreceiver' => Yii::$app->user->identity->id])
               ->all(); 
    $messages2 = (new \yii\db\Query())
               ->select(['idreceiver as idotherguy'])
               ->from('messages')
               ->where(['idsender' => Yii::$app->user->identity->id])
               ->all(); 
    $messages->union($messages2);

after advice here I also tried....

    $messages = (new \yii\db\Query())
               ->select(['idsender as idotherguy'])
               ->from('messages')
              ->where(['idreceiver' => Yii::$app->user->identity->id]);

    $messages2 = (new \yii\db\Query())
               ->select(['idreceiver '])
               ->from('messages')
              ->where(['idsender' => Yii::$app->user->identity->id]);

    $messages->union($messages2);
    $messages->all(); 

and i get query object

      yii\db\Query Object

( [select] => Array ( [0] => idsender as idotherguy )

[selectOption] => 
[distinct] => 
[from] => Array
    (
        [0] => messages
    )

[groupBy] => 
[join] => 
[having] => 
[union] => Array
    (
        [0] => Array
            (
                [query] => yii\db\Query Object
                    (
                        [select] => Array
                            (
                                [0] => idreceiver as idotherguy
                            )

                        [selectOption] => 
                        [distinct] => 
                        [from] => Array
                            (
                                [0] => messages
                            )

                        [groupBy] => 
                        [join] => 
                        [having] => 
                        [union] => 
                        [params] => Array
                            (
                            )

                        [_events:yii\base\Component:private] => Array
                            (
                            )

                        [_behaviors:yii\base\Component:private] => 
                        [where] => Array
                            (
                                [idsender] => 2
                            )

                        [limit] => 
                        [offset] => 
                        [orderBy] => 
                        [indexBy] => 
                        [emulateExecution] => 
                    )

                [all] => 
            )

    )

[params] => Array
    (
    )

[_events:yii\base\Component:private] => Array
    (
    )

[_behaviors:yii\base\Component:private] => 
[where] => Array
    (
        [idreceiver] => 2
    )

[limit] => 
[offset] => 
[orderBy] => 
[indexBy] => 
[emulateExecution] => 

)

that is it... I tried also answe bellow and used in select 'idreceiver as idotherguy' ... but the result is the same

Answer 1

I changed the code offered here like this and it works now fine....

   $messages = (new \yii\db\Query())
               ->select(['idsender as idotherguy'])
               ->from('messages')
               ->where(['idreceiver' => Yii::$app->user->identity->id]);
    $messages2 = (new \yii\db\Query())
               ->select(['idreceiver as idotherguy'])
               ->from('messages')
              ->where(['idsender' => Yii::$app->user->identity->id]);
    $messages = $messages->union($messages2)
                ->all();

The result now is array with values as I wanted. Array ( [0] => Array ( [idotherguy] => 3 )

[1] => Array
    (
        [idotherguy] => 11
    )

[2] => Array
    (
        [idotherguy] => 10
    )

)

Thanks for help boys :)

Answer 2

you should avoid the alias in united query , use literal select format and apply all to the result only

  $messages = (new \yii\db\Query())
             ->select('idsender as idotherguy')
             ->from('messages')
             ->where(['idreceiver' => Yii::$app->user->identity->id]); 

  $messages2 = (new \yii\db\Query())
             ->select('idreceiver')
             ->from('messages')
             ->where(['idsender' => Yii::$app->user->identity->id]); 

  $messages->union($messages2);
  $messages->all();

for see the result you should

 foreach($messages as $key=> $value) {

   echo $value->idotherguy;
 }

or if the result is an array

  foreach($messages as $key=> $value) {

    echo $value['idotherguy'];
 }

try check the ral sql code this way (instead of $messages->all();)

var_dump( $messages->createCommand()->sql);

Answer 3

Take a look at the documentation http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html#union, you shouldn't have all() in your queries

$messages = (new \yii\db\Query())
           ->select(['idsender as idotherguy'])
           ->from('messages')
           ->where(['idreceiver' => Yii::$app->user->identity->id]); 
$messages2 = (new \yii\db\Query())
           ->select(['idreceiver as idotherguy'])
           ->from('messages')
           ->where(['idsender' => Yii::$app->user->identity->id]); 
$messages->union($messages2);
$messages->all();