condition in criteria

$criteria=new CDbCriteria();

    $criteria->with = array('reviewCount', 'category10', 'category20', 'category30', 'town');

    $criteria->select = ',business,street,postalCode,contactNo,checkinCount,count( as spcount';

    $criteria->join = 'left join tbl_abc on';

    $criteria->group = '';

    $criteria->order = 'spcount DESC';


    $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 will make this query such that I get all the businesses whose spcount is greater than 1

[font=“Arial”][size=“3”]You can’t reference an aliased field name in there where clause. [/size][/font]



[font=“Arial”][size=“3”]I think what you need to do is remove: [color=#1C2837]$criteria->condition=‘spcount>1’;[/color][/size][/font]



[color=#1C2837][font="Arial"][size="3"]And add:[/size][/font][/color]



[font=“Arial”][size=“3”][color="#1c2837"]$criteria->having = ‘spcount>1’[/color][/size][/font]



[font="Arial"][size="3"][color="#1c2837"]If that does not work you could try GROUPing on all columns that are not in aggregate function as well as the adding the HAVING clause. [/color][/size][/font]

You can try HAVING COUNT(>1 as where-condition. But to be honest: This smells pretty much like abuse.

Evan and Da are correct, however the query looks a bit weird for me, because you use “with” and “join”, isn’t “tbl_abc” in your models? or is it a kind of materialized view? o maybe a view?

Btw, I think using "together" can solve some issues and improves the query speed for this case.

having is doing the job. But there is one problem with that. $bizModel->getTotalItemCount() is 613 which is actually the total number of records ignoring the filter spcount>1. But otherwise its showing the filtered results. Because Im using Clistview so its creating a problem in pagination. Its showing pagination for 7pages when it has only 7-8 records.

Yes I know its weird. Actually I have relational STAT called ‘abcCount’. But I dont know how to use ‘abcCount’ so that I can query something like “where abcCount > 1”

Since you’re trying to fetch reviews that have more than one sp (whatever that is supposed to be), why don’t you just join tables? I think that were less expensive than COUNT().