CDBCriteria to CDbCommand

Hi,

I have a query with somewhat tricky conditions. I’ve built a CDbCriteria to fill CActiveProvider that feeds a CGridView. So far so good, but I need to make some aggregate calculations on the same criteria conditions (SUMs and so on) and I can’t use a CActiveRecord for that: i tried selecting a [font=“Courier New”]SUM(…)[/font] but it doesn’t work.

Is there a way to reuse the criteria to feed a CDbCommand? I was thinking of extracting criteria conditions and supplying them to the command, but maybe there’s a better approach.

Thank you.

for sum() to work you must use CDbExpression

also you can build an active data provider from pure sql

I’m not very experinced in it, someone might answer you better, but that is the way I would do it

There is a topic you can read through that might help you here.

Thanks to both, but I think I didn’t explain myself.

I have a CDbCriteria to which I’ve added some conditions. I can use that CDbCriteria with a CActiveProvider/CActiveRecord, but if I want to reuse the same conditions for an aggregate query I dont know how. I wouldn’t want to rewrite the same “where” conditions with the CDbCommand API…

The link I posted above does give you some information on how to use an aggregate function within CDbCriteria properties, it’s just in the context of a relation. You’ll want to put the function in your select string/array with an alias




$criteria->select = 'SUM(columnA) as aSum, columnB'



then you need to make sure that you use group by on a column other than the one you are performing the aggregate function on




$criteria->group = 'columnB'



Sometimes it won’t work unless you add the alias as a property in your AR model. Following my example, you would add the public property $aSum to the AR model you are using with CActiveDataProvider. If the aggregate function needs to be applied to a model relation, you can see how to do that in the link I posted above.

OK, now I understand…

The trick is to add the property to the model, thank you very much!!

Well, it works…partially.

If a "simple" expresssion is used, like




sum( field1 ) as my_sum



works fine, but my expression looks like this:




sum( hour( timediff( date_end, date_ini ) ) ) as total_hours



and that gives me a "ActiveRecord is trying to select an invalid column" …

Try to specify the select columns in your CDbCriteria as Array instead of a comma separated list.

This construct worked when I performed a quick test yesterday, pasting it into a $criteria->select statement (MySQL).

/Tommy

Problem is that if you do not use an Array like this:




$crit->select=array('...','...');



Yii will explode the select string by ‘,’. Since there’s a comma in your statement, it will not work right. So use an Array and you’ll be fine.

Actually it worked without an array when I tried it :huh: .

That’s why I didn’t post an answer, it has to be something obvious.

/Tommy

I’ll try when I get home. Thank you all for your help.

@tri:

Could happen with "with" queries only. Check the source of CJoinElement::getColumnSelect(). This is where the





            if(is_string($select))

                $select=explode(',',$select);



happens. I think it’s used in JOIN queries.

Maybe this behaviour has changed (fixed?) in the latest snapshots. I’m using the last stable 1.1.6… Does it has sense that the “,” has anything to do with the number of “(” that appear in an expression?

I traced the code to the CActiveFinder until I found a regular expression that kind of freaked me out :) (I’ve never been able to fully understand them). I’ll take a deeper look into it and i’ll post back.

Thank you all. My code looks like this now:




$criteria->select = array( 'ifnull( sum( hour( timediff( date_end, date_ini ) ) ), 0 ) as custom1',

                            'ifnull( sum(price), 0 ) as custom2');



And I had to define $custom1 and $custom2 as public properties in my model class.