condition in criteria in yii framework php
10,404
As far as I know, you can't reference aliases in a WHERE
part (proof link). Remove the condition line and add the following:
$criteria->having = 'COUNT(tbl_abc.id) > 1';
UPDATE
CActiveDataProvider
accepts finder instance, so you'll need a model scope:
<?php
class Business extends CActiveRecord
{
public function scopes()
{
return array(
'hasSpcount' => array(
'with' => array('reviewCount', 'category10', 'category20', 'category30', 'town'),
'select' => 't.id,business,street,postalCode,contactNo,checkinCount,count(tbl_abc.id) as spcount',
'join' => 'left join tbl_abc on t.id=tbl_abc.businessId',
'group' => 't.id',
'order' => 'spcount DESC',
'having' => 'COUNT(tbl_abc.id) > 1',
),
);
}
}
// usage
$provider = new CActiveDataProvider(Business::model()->hasSpcount());
Hope this works
Comments
-
iThink almost 2 years
$criteria=new CDbCriteria(); $criteria->with = array('reviewCount', 'category10', 'category20', 'category30', 'town'); $criteria->select = 't.id,business,street,postalCode,contactNo,checkinCount,count(tbl_abc.id) as spcount'; $criteria->join = 'left join tbl_abc on t.id=tbl_abc.businessId'; $criteria->group = 't.id'; $criteria->order = 'spcount DESC'; $criteria->condition='spcount>1'; $bizModel = new CActiveDataProvider(Business::model(), array( 'criteria' => $criteria ));
I'm getting this error:
Column not found: 1054 Unknown column 'spcount' in 'where clause'
If I omit the condition the query works fine & orders businesses by spcount. So how do I rewrite this query such that I get all the businesses whose spcount is greater than 1?