Use CActiveRecord to get the sum of a column

Question

1) Is there a way to get the sum of an integer column using CActiveRecord in Yii?

Otherwise I will have to get the column data and sum it up on the server side.

2) I also assume getting the sum via one sql query is faster than getting the column of data and sum it up on the server with php. If performance matters, should the mysql server be bothered to do such operation or just let the php server to take care of this.

Please kindly advice.

Solution 1

1) I don't thinks so, and it doesn't really makes sense to use CAvtiveRecord that way, unless you want to have a STAT relation. Let's say you have a "Question" model and an "Answer" model and the answers belongs to a question. You could make a statistical relation and implement it in "Question" like this:

Public function relations() {
    return array(
        'answerSum'=>array(self::STAT, 'Answer', 'questionId', 'select' => 'SUM(answerSum.someFieldFromAnswerTableToSum)')
    );
}

Then you retrieve the information: $question->answerSum; where $question is an instance of Question with the relations declared as above.

2) It's a matter of the amount of data. I would personally choose SQL, because it is capable of handling a larger amount of datasets and is thereby future-save.

Solution 2

Try it

$user = User::model()->findBySql('select sum(`you_column`) as `sum` from user', array());
var_dump($user->sum);

In model should be present field sum

Comments

[Alocus]

Why doesn't it make sense to use CActiveRecord this way? perhaps I should just execute a sql to sum a column of data without using CActiveRecord?

[Alocus]

Accepting this as the solution as it points out that ActiveRecord should not be used in such a way. However, further explanation on why would be nice.

[Alocus]

I love your suggestion. I think you know my application more than I do. Thank for suggesting to use STAT.