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 make UNION like this;

$firtQuery = Yii::app()->db->createCommand()
    ->select("*")
    ->from('abc')
    //->where() you can add WHERE clause in here
    ->getText();

$result = Yii::app()->db->createCommand()
    ->select("*")
    ->from('abc2')
    //->where() you can add WHERE clause in here
    ->union($firtQuery)
    ->queryRow();

Answer 2

i suggest KISS solution :
is it possible create view from union select and work with it?
and then create Active Record from this view.
of course for Create/Remove data in UI, you must create model separately in both table.

Update
you can create view in data base : CREATE VIEW abc_union AS SELECT * FROM abc UNION ALL SELECT * FROM abc2
and then generate model with Gii for this view.

Answer 3

First of all, if you have 2 equal tables in the DB, may be it will be better to gather them into one. Yii's ActiveRecord realization based on the simple rule: one table -> one active record model.

I suggest you this solution using repository pattern. I don't think that usage of defaultScope() is essential.

1.Create an interface to ensure both product models implement some common methods.

interface Product
{
    public function findAllByMakeCode($makeCode);

    // other common methods here
}

2.Create a trait with common methods implementation or default scope you need.

trait ProductTrait
{
    function findAllByMakeCode($makeCode)
    {
        return $this->model()->findAllByAttributes(['make_code' => $makeCode]);
    }

    function defaultScope()
    {
        // your default scope condition
        // for example field is_deleted equals to 0
        return ['condition' => 'is_deleted=0'];
    }
    // other common methods implementation
}

3.Create separate AR models for both tables.

// used product entity
class ProductUsed extends CActiveRecord implements Product
{
    use ProductTrait;

    public static function model($className=__CLASS__)
    {
        return parent::model($className);
    }

    public function tableName()
    {
        return 'product_used';
    }
}

// new product entity
class ProductNew extends CActiveRecord implements Product
{
    use ProductTrait;

    public static function model($className=__CLASS__)
    {
        return parent::model($className);
    }

    public function tableName()
    {
        return 'product_new';
    }
}

4.Create a Repository class.

class ProductRepository
{   
    public function findAllByMakeCode($makeCode)
    {
        return array_merge(
            ProductUsed::model()->findAllByMakeCode($makeCode),
            ProductNew::model()->findAllByMakeCode($makeCode)
        );
    }

    public function findAll($condition = '', array $params = [])
    {
        return array_merge(
            ProductUsed::model()->findAll($condition = '', array $params = []),
            ProductNew::model()->findAll($condition = '', array $params = [])
        );
    }
}

5.Use it.

$repository = new ProductRepository();

// all products
$products = $repository->findAll();

// products with 42 make code
$products42 = $repository->findAllByMakeCode(42);

Maybe it looks little bit complex, but it's worth it.

Answer 4

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.

Answer 5

Hello i hope i can help you out , right now cdbcriteria of yii 1.x is not ready for unions... so i think more on other solutions or wa.

so, the easy way to get around this is to create a view from the query you need wich is:

CREATE VIEW unionAbc AS
SELECT * FROM `abc` 
UNION ALL 
SELECT * FROM `abc2`

then from that view you can construct a new model and call it with out much effort.

the other solution i have found is to merge model queries result, this i am telling you from a php theory perspective, what i mean is that when you generate a model query in yii it is returning either and a array or object, you will need to use array converters from model that exist in yii helper and then merge both arrays so it will result in a new one with both info

third solution not tested would be:

$dataProvider =  new CArrayDataProvider('User');
$dataProvider->setData($modelabc->findAll());
$dataProvider->setData($modelabc2->findAll());

regards.