CODEWITHSUNDEEP
phpphpactiverecord
Cristhian Boujon
Cristhian Boujon

Reputation: 4190

Using sum() sql with php ActiveRecord

I'm using ActiveRecord in my app. I need to add many objects by attribute. Currently I do it as follows:

foreach($objects as $object):
    $result += $object->value;
endforeach;

But it is very slow. I think I could get the same result but more efficient by sql sum(), so de question is can ActiveRecord return me the sum() result?

Upvotes: 1

Views: 1547

Answers (2)

alex
alex

Reputation: 11

Add custom model to your lib/Model.php file

public static function sum($args) {
    $args = func_get_args();
    $options = static::extract_and_validate_options($args);
    $options['select'] = 'SUM('.$args[0]["sum"].')';
    if (!empty($args) && !is_null($args[0]) && !empty($args[0]))
    {
        if (is_hash($args[0]))
            $options['conditions'] = (isset($args[0]["conditions"]) ? $args[0]["conditions"] : array());
        else
            $options['conditions'] = call_user_func_array('static::pk_conditions',$args);
    }
    $table = static::table();
    $sql = $table->options_to_sql($options);
    $values = $sql->get_where_values();
    return static::connection()->query_and_fetch_one($sql->to_s(),$values); 
}

Then call it:

YourModel::sum(array('conditions' => 'userid = 3', 'sum' => 'your_column'));

or

YourModel::sum(array('sum' => 'your_column'));

Upvotes: 0

Bogdan D
Bogdan D

Reputation: 5611

The only aggregating function already implemented in the model is count(). For sum(), you'd have to use an SQL query. You can do that using different classes (Connection, Table or Model).

Here's using find_by_sql() of the Model (returns an array of models): 

$result = FooModel::find_by_sql('SELECT SUM(`value`) AS sum FROM `foo_table`')[0]->sum;

Upvotes: 3

Related Questions