SUM Aggregate functions in CDbCriteria

Hi,

I need to create a SQL like this:


Select fk_plan_de_cuenta_id, SUM(ingreso)

from t_caja

Order by fk_plan_de_cuenta_id

Group by fk_plan_de_cuenta_id



And I’m using CDbCriteria: and model()->findAllByAttribute


$cond = new CDbCriteria;

$cond->order = 'fecha_mov ASC, fk_plan_de_cuenta_id ASC, fk_familia_id ASC, fk_alumno_id ASC 	  ';

$cond->condition = 'ingreso > 0 and egreso = 0';

$cond->group     = 'fk_plan_de_cuenta_id ';

$cond->select    = 'fk_plan_de_cuenta_id, sum(ingreso) AS SUMingreso';



and then retrieve data:


$movCaja = Caja::model()->findAllByAttributes(array('fecha_mov'=>$printCajaForm->fecha), $cond);

The problem is that I receive AR objects …

So I never get "SUMingreso" fields …

Maybe I’m confuse and I have to use SQL only ?

Is there any way of using CDbCriteria instead ??

Best Regards

Try using the ‘select’ property with your aggregate clause.

Hi, I change the focus … and use QueryBuilder approach …




//$command = Yii::app()->db->createCommand();


$ingresos = Yii::app()->db->createCommand()

				->select('fk_plan_de_cuenta_id, sum(ingreso) AS ingresos')

				->from('t_caja_mov')    

				->where('ingreso > 0 AND egreso = 0 AND fecha_mov = :fecha', array(':fecha'=>$printCajaForm->fecha))

				->group('fk_plan_de_cuenta_id')

				->order('fk_plan_de_cuenta_id, fecha_mov ASC')

				->queryAll();



And Works great !!!

Great that you solved your problem .

If you are interested you can try this suggestion to add your ingresos sum field as class member variable to your model.