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..
如果那对你有用的话,请告诉我。