CODEWITHSUNDEEP
phpmysqlyii
Vitaly
Vitaly

Reputation: 1281

How create query with union two select in Yii?

There is a table "Apartments". Here is a need to create a query in Yii. How to do it?

SQL query:

SELECT * 
FROM {{apartments}} 
WHERE agent_id = 4 
UNION 
SELECT * 
FROM {{apartments}} 
WHERE agent_id != 4

In my cintroller

$arrSql[] = 'SELECT * FROM {{apartments}} WHERE agent_id=:agent_id';
$arrSql[] = 'SELECT * FROM {{apartments}} WHERE agent_id!=:agent_id';
$data = Apartments::model()->findAllBySql(implode(' UNION ', $arrSql), array(
    ':agent_id' => Yii::app()->user->id,
    ':status' => Apartments::STATUS_REMOVED
));

$dataProvider = new CArrayDataProvider($data);

But not work pagination and filter in my widget CGridList.

Upvotes: 2

Views: 10409

Answers (2)

Dinistro
Dinistro

Reputation: 5730

As i know, you can use the CDbCommand like this:

$apartments2 = Yii::app()->db->createCommand()
    ->select("*")
    ->from('apartments')
    ->where('agent_id!=:agent_id', array(':agent_id'=>4))
    ->getText();

$apartments = Yii::app()->db->createCommand()
    ->select("*")
    ->from('apartments')
    ->where('agent_id=:agent_id', array(':agent_id'=>4))
    ->union($apartments2)
    ->queryRow();

you can find your result in the $apartments as an array.

EDIT:

if you want to use a CActiveDataProvider, you need to use CSqlDataProvider:

$sql='SELECT * 
FROM {{apartments}} 
WHERE agent_id = 4 
UNION 
SELECT * 
FROM {{apartments}} 
WHERE agent_id != 4';

$dataProvider=new CSqlDataProvider($sql, array(
    'totalItemCount'=>$count,
    'sort'=>array(
        'attributes'=>array(
             'agent_id', //and all other atributes with withc you want to sort
        ),
    ),
    'pagination'=>array(
        'pageSize'=>10,
    ),
));

Upvotes: 6

Yunus Aslam
Yunus Aslam

Reputation: 2466

Its very simple 

  (SELECT *
   FROM {{apartments}}
   WHERE `agent_id` = '4')
UNION ALL
  (SELECT *
   FROM {{apartments}}
   WHERE `agent_id` <> '4')

Let me know if that worked for you..

Upvotes: -4

Related Questions