condition in criteria

$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 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"]

[/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"]

[/size][/font][/color]

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

[color=#1C2837][font="Arial"][size="3"]

[/size][/font][/color]

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

[font="Arial"][size="3"][color="#1c2837"]

[/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(tbl_abc.id)>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().