Can you please post some relevant code from you model? 
Can you please post some relevant code from you model?
No problem. In SQL i can use:
...
HAVING 3956000 * 2 * ASIN(SQRT(
POWER(SIN((latitude - abs(55.75168113348126)) * pi()/180 / 2),
2) + COS(latitude * pi()/180 ) * COS(abs(55.75168113348126) *
pi()/180) * POWER(SIN((longitude- 37.5655871629715) *
pi()/180 / 2), 2) ))
But when i try to use in YII:
$criteria->having='maxdistance < 5000';
Error:
[i]Error in querying SQL: SELECT COUNT(DISTINCT shop_id) FROM shop t
LEFT OUTER JOIN shopprices prices ON (t.id=prices.shop_id)
WHERE (((prices.price BETWEEN 100 AND 5000) AND (city_id=1)) AND (stars
IN (4,3))) AND (recomended_id IN (3)) GROUP BY shop_id HAVING
maxdistance < 5000
in C:\SRV\Apache\htdocs\yii\framework\zii\widgets\CBaseListView.php (105)
in C:\SRV\Apache\htdocs\yii\framework\zii\widgets\grid\CGridView.php (220)
in C:\SRV\Apache\htdocs\car\protected\views\shop\search.php (114)[/i]
as seen from the error: my $criteria->select option changed with: SELECT COUNT(DISTINCT shop_id)
CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘maxdistance’ in ‘having clause’
I don’t understand your problem. You have some working SQL statement. But it’s different from the statement you use in Yii.
Again: Please check MySQL reference for valid HAVING clauses. This is not a Yii problem.
Huh, i undestand you: the code i used in SQL
SELECT `t` . * , MIN( price ) AS minprice, 3956000 *2 * ASIN( SQRT( POWER( SIN( (
latitude - abs( 55.75168113348126 ) ) * pi( ) /180 /2 ) , 2 ) + COS( latitude * pi( ) /180 ) * COS( abs( 55.75168113348126 ) * pi( ) /180 ) * POWER( SIN( (
longitude - 37.5655871629715
) * pi( ) /180 /2 ) , 2 ) )
) AS maxdistance
FROM `shop` `t`
LEFT OUTER JOIN `shopprices` `prices` ON ( `t`.`id` = `prices`.`shop_id` )
WHERE `prices`.`price`
BETWEEN 100
AND 5000
GROUP BY `shop_id`
HAVING maxdistance <5000
They are same with YII query:
$criteria->select='`t`.*,MIN(price) as minprice,3956000 * 2 * ASIN(SQRT(
POWER(SIN((latitude - abs(55.75168113348126)) * pi()/180 / 2),
2) + COS(latitude * pi()/180 ) * COS(abs(55.75168113348126) *
pi()/180) * POWER(SIN((longitude- 37.5655871629715) *
pi()/180 / 2), 2) )) as maxdistance';
$criteria->join='LEFT OUTER JOIN `shopprices` `prices` ON (`t`.`id`=`prices`.`shop_id`)';
$criteria->group='`shop_id`';
$criteria->addCondition('city_id='.$this->city_id);
$criteria->having='maxdistance < 5000';
They are equal, are not?
So if i understand you right, the problem comes with the first query (query the total number of results) that Yii does automatically?
Maybe you’ve hit a limitation of AR then.
No no no.
When i acked about two queries i have used this code:
....
$criteria=new CDbCriteria;
$criteria->with=array('shopprices'=>array(
'select'=>'shop_id,MIN(price) as minprice',
'group'=>'shop_id',
'having'=>'minprice<5000');
$criteria->addCondition('city_id=1');
....
But now i’m using this:
$criteria->join='LEFT OUTER JOIN `shopprices` `prices` ON (`t`.`id`=`prices`.`shop_id`)';
$criteria->group='`shop_id`';
$criteria->addCondition('city_id='.$this->city_id);
$criteria->having='maxdistance < 5000';
So the question about 2 queries instead of 1 is not actual. 
The problem is: why my code in YII
....
$criteria->select='`t`.*,MIN(price) as minprice,3956000 * 2 * ASIN(SQRT(
POWER(SIN((latitude - abs(55.75168113348126)) * pi()/180 / 2),
2) + COS(latitude * pi()/180 ) * COS(abs(55.75168113348126) *
pi()/180) * POWER(SIN((longitude- 37.5655871629715) *
pi()/180 / 2), 2) )) as maxdistance';
....
changes with SQL:
...
SELECT COUNT(DISTINCT `hotel_id`) FROM `hotel` `t`
....
and Error: Column not found: 1054 Unknown column ‘maxdistance’ in ‘having clause’
Frankly, your questions are very confusing and missing the important code parts. The behavior you describe is the same problem i mentioned above. Yii does the COUNT() to find the total number of results.
One way around that could be to set totalItemCount manually on your dataprovider object (with your own query), before you use it in a CGridView. This will prevent the extra COUNT() query.
Ok, thanks a lot. I solved my problem via using codition methods.