如何用Yii中的union two select创建查询?

时间:2021-05-28 00:05:05

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

有一张桌子“公寓”。需要在Yii中创建查询。如何去做?

SQL query:

SQL查询:

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.

但不要在小部件CGridList中进行分页和筛选。

2 个解决方案

#1


5  

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

如我所知,您可以使用CDbCommand如下:

$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.

你可以在$apartment中找到你的结果。

EDIT:

编辑:

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

如果您想使用CActiveDataProvider,您需要使用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,
    ),
));

#2


-2  

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..

如果那对你有用的话,请告诉我。

#1


5  

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

如我所知,您可以使用CDbCommand如下:

$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.

你可以在$apartment中找到你的结果。

EDIT:

编辑:

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

如果您想使用CActiveDataProvider,您需要使用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,
    ),
));

#2


-2  

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..

如果那对你有用的话,请告诉我。