Calculate Sum For Cactiverecord Column

Yii 1.1 General Discussion
1

Hi,

I’m struggling with the following problem:

I have a page showing a list of active records (using CActiveDataProvider). CActiveDataProvider provides a method to calculate the total number of rows (calculateTotalItemCount()). Now I want to create a similar method to calculate the sum for a certain column. I’m having a hard time thinking of an elegant way…

My search() method creates table joins so the CActiveFinder class is used to retrieve records en count the total amount of records.

So far I tried the following, in my active record class I added a method:




public function calculateSum() {

	// Duplicate the original CDbCriteria object (created in search())

	$criteria = clone $this->_criteria;

	// Replace the 'select' part by 'sum'

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

	$count = $this->count($criteria);

	$this->setDbCriteria($this->_criteria);

	return $count;

}

Unfortunately this doesn’t work since the select part is replaced in CJoinElement.count() resulting in a regular count (instead of sum).

Thanks in advance for any help!

2

Hi my friend

I didn’t try similar thing but I think that the problem is that your code


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

        $count = $this->count($criteria);

returns the count indeed.

try this


$var_sum = YourModel::model()->findBySql('select sum(`amount`) as `total` from your_table', array());

return $var_sum->total;
3

Hi KonApaz,

Thanks for your reply. This would work if I didn’t have search conditions.

However, I do have search conditions (most are more complex than simple ‘compare’ since they use values from other tables). That’s why I want to re-use the count code as provided by CActiveDataProvider/CActiveFinder/CJoinElement. Obviously I don’t want to create duplicate code just to calculate the sum.

4

You don’t need count() to use aggregates.

Just add aggregate function to "select", and do a normal search query (find or findAll).

Btw you’ll need a virtual attribute(s) in yor model for storing results.

Here’s an example from my code




$c = new CDbCriteria;

// $c->addCondition(...); here are your conditions


$c->select = array(

    'count(*) as total',

    'SUM(learnNew = 5) as best_learn',

    'SUM(serviceSiteMark = 5) as best_site'

);


$c->group = 'museumId'; // optional group by


$list = Report::model()->findAll($c);

In Report model I have


public $total;

public $best_learn;

public $best_site;
5

Thanks ORey! This is what I was looking for.