Aggregate functions in CActiveRecord

Archives Feature Requests
1

When creating a website I wanted to have a query like:

SELECT sum(filesize) FROM File

To see how much disk space was used by all uploaded files in the application.

To my big surprise CActiveRecord provides no method of doing this.

What I would like therefore is a function called something like CActiveRecord::aggregate($function, $attribute, $criteria=’’)

So that File::model()->aggregate(‘sum’, ‘filesize’) would return me the value of

SELECT sum(filesize) FROM File

Of course a function CActiveRecord::sum() would also be fine, but since there are quite a lot of aggregate functions I thought CActiveRecord::aggregate() would be simpler :)

2

I’d say, AR can (and should) not match any possible situation. You can get what you want like this:


public function getTotalSize() 

{

    return $this->getDbConnection()->createCommand('SELECT sum(filesize) FROM File')->queryScalar();

}

Usage:


$size=File::model()->getTotalSize();
3

Then I would rather do




public static function getTotalSize()

{

   return Yii::app()->db->createCommand('SELECT sum(filesize) FROM File')->queryScalar();

}

Usage:




File::getTotalSize();

Then I don’t need to let PHP create a model instance for this “simple” query :)

4

Yii does provide STAT relations. Look it up in the guide.

5

If you do that, you create a dependency with Yii::app()->db. That might work in your case now, but can lead to problems later when you e.g. want to configure multiple db’s or have other more complex db setups (sharding…). That’s why getDbConnection() is preferred as it helps encapsulating your AR models. Using model() also makes sure, that the static (singleton) model instance gets correctly initialized. This would happen anyway as soon as you perform any AR operation.

6

That’s true, but as you say it yourself, those are STAT relations.

Those relations are capable over computing some statistic over some model B that is related to model A.

AFAIK it is not possible to compute statistics over model A itself.

7

You’re right, it’s not possible to calculate the sum of all filesizes in the table with STAT, however you can calculate the total used filezise per user (for example), so depending on your application it might still be useful somehow. I was just bringing that up :)

8

This thread has been really helpful. But wouldn’t it be slightly better to remove the static table name, and use the function you already have:




public function getTotalSize() 

{

    return $this->getDbConnection()->createCommand('SELECT sum(filesize) FROM ' . $this->tableName())->queryScalar();

}

Or am I missing something?

9

How would you do this with criteria set? This was the best I could figure.




$criteria=new CDbCriteria;

$criteria->compare('date',$this->date);

$criteria->select = 'SUM(amount) as total';

$prospect=Prospect::model()->findAll($criteria);
10

So is their really no way to compute statistics over the model itself? Hard for me to believe.

11

For aggregation I am using self::STAT in the model with a "select" parameter.


			

'rating' => array(self::STAT, 'Rating', 'walk_id',

			'select' =>'ROUND(AVG(rating))',

			'group' => 'walk_id',

		),