IfNULL function in yii does not work

Question

In my model I wrote:

$criteria->select =  ' ( select avg( IfNULL( TR.stars_rating_type_id , 0) ) as stars_rating_type_id from '.$tablePrefix.'tour_review as TR where TR.tour_id = T.id and TR.status = \'A\' ) as reviews_avg_rating ';

And I get error :

Active record "Tour" is trying to select an invalid column "( select avg( IfNULL( TR.stars_rating_type_id". Note, the column must exist in the table or be an expression with alias.

The reason is that I add "IfNULL( ..., 0)" function in subquery to escape "null" in result set.

Without it I have to make additional verification and set 0 in case of null.

If I test raw sql with " SELECT ( select avg( IfNULL( TR.stars_rating_type_id, 0) ) as stars_rating_type_id..." it works ok, so that is the problem from the yii side. How to fix it ?

Yii 1.1.14
Thanks!

Answer 1

Take a look on the manual:

http://www.yiiframework.com/doc/api/1.1/CDbCriteria

the select param receive the columns that will be search, not the hole query