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.
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.
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.