MySQL Union All in Yii's default scope

Question

I'm using Yii 1.1.16, and was wondering how would i Union 2 model's using the default scope?

model 1 = abc
model 2 = abc2

basically i want to do a simple union

SELECT * FROM `abc`
UNION ALL
SELECT * FROM `abc2`

Havent really used default scope, so kind of new to the concept. Both tables have the exact same column numbers and column names.

i tried this, but failed.

in my abc model

public function defaultScope() {
        return array(
            'alias' => 't2',
            'select'=>array('t.*, t2.*'),
            'union'=>array('SELECT * FROM `abc2`')
        );
    }

UPDATE: Just realized http://www.yiiframework.com/doc/api/1.1/CDbCriteria, there is NO union for CDbCriteria.

public function defaultScope() {
        return array(
            //'alias' => 't',
            'select'=>array('*'),
            'join'=>'UNION ALL SELECT * FROM `abc2`'
        );
    }

code above gives me an error

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 't.make_code' in 'where clause'. The SQL statement executed was: SELECT `t`.`class_code`, `t`.`make_code`, `t`.`model_code` FROM `abc` `t` UNION ALL SELECT * FROM `abc2` WHERE `t`.`make_code`=:yp0 LIMIT 1. Bound with :yp0='11' 

in both tables, i have the column make_code

I need the query to look like this

SELECT * FROM 
(
   SELECT * FROM `abc` 
      UNION ALL 
   SELECT * FROM `abc2`
) AS t 

how to go about this?

Answer 1

You can use the Yii query builder; it's the go-to solution when you need to build non-standard queries, and it natively supports UNION:

$data = Yii::app()->db->createCommand()
  ->select('*')
  ->from('abc')
  ->union('SELECT * FROM `abc2`')
  ->where('make_code=:make_code', [':make_code'=>42])
  ->queryAll();

Alternatively, instead of directly querying you can use ->getText() or write the SQL manually and then utilize CSqlDataProvider to make a configurable data source.