yii框架php中条件的条件 [英] condition in criteria in yii framework php

问题描述

$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
));

我收到此错误:

Column not found: 1054 Unknown column 'spcount' in 'where clause'

如果我省略该条件，则查询可以正常运行&通过spcount订购业务.那么，如何重写此查询，以便获得spcount大于1的所有业务?

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?

推荐答案

据我所知，您不能在WHERE部分(

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';

更新

CActiveDataProvider接受查找程序实例，所以您需要一个模型范围:

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());

希望这行得通

这篇关于yii框架php中条件的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！