ActiveDataProvider Select IFNULL available?

Yii 1.1 General Discussion
1

I’m using CActiveDataProvider to provide a recordset to CGridView. As you can see below, I “ROLLUP” my values to include a total value at the end of my recordset. In order to ensure “group_name” is not null I include the condition IFNULL statement. When yii runs this query it parses the IFNULL. My log shows the beginning of the query like so:

SELECT g.group_name AS t0_c2, "Total") AS group_name

You’ll may have noticed the missing IFNULL part.

Here is my data provider:


$summary = new CActiveDataProvider('AccountData', array(

	'criteria' => array(

		'select'    =>  'IFNULL(g.group_name, "Total") AS group_name, amount',

                        'with'      =>  'prebudget',

                        'alias'     =>  'g',

                        'condition' =>  'SUBSTRING(account, 7, 3) IN (' . $departments . ')',

                        'group'     =>  'g.group_name WITH ROLLUP',

                ),

         ));

Is this what yii is supposed to do? I’m using MySQL and works as predicted when writing my own SQL statement. As always, thanks in advance!!

2

does not seems yii is against that. i am using IFNULL in the same fashion & it returns in the query.

try using join in place of with.

3

Anupam - You’re correct. Using join instead of with has solved the problem, though I’m not sure why. When time becomes more available I’ll have to step through the CDbCriteria code to find out why. In the meantime, thank you!

4

I am facing the same problem and am willing to stick with the WITH and not use the JOIN…

Has anyone found a way to do that?