PHPActiveRecord group option breaks count function

Question

I have a PHP ActiveRecord model in which I have a function that requires the number of rows a query will return. I obtain the number of rows using the built in static::count($conditions) function. This works well and good but the issue arises when I include a GROUP BY statement. When I include this the count returns 1. I examined the resulting SQL and it was similar to

 SELECT COUNT(*) 
 FROM TABLE
 /* JOINS */
 /* WHERE CONDITIONS */
 GROUP BY `field`

When I ran the query manually I get

 1
 1
 1

. . . 1

 (1,000 times since there are 1,000 rows in the DB)

When I remove the GROUP BY statement, I get the value 1,000 like I should.

Obviously this occurs since COUNT is an aggregate function and it doesn't play well with group by. So with that being said, how can I return the correct number of rows using activerecord with a group by?

Community · Accepted Answer · 2017-05-23 12:23:48Z

I had the same problem. I followed the example set by @jvenema in this question, wherein one defines a BaseModel class to override default ActiveRecord\Model behavior. Your models will then extend the BaseModel class.

class BaseModel extends ActiveRecord\Model
{
    public static function count(/* ... */)
    {

        $args = func_get_args();
        $options = static::extract_and_validate_options($args);

        // Call the original function if $options['group'] is undefined
        if ( !array_key_exists('group', $options) )
        return call_user_func_array( 'parent::count', func_get_args() );

        // This might fail if the table has a `counts` column
        $options['select'] = 'COUNT(*) as counts';

        if (!empty($args) && !is_null($args[0]) && !empty($args[0]))
        {
            if (is_hash($args[0]))
                $options['conditions'] = $args[0];
            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();

        // Again, this might fail if there is a table named `tmp`
        $wrapper = "SELECT COUNT(counts) FROM ({$sql->to_s()}) as tmp";

        // Casting to (int) is optional; remove if it causes problems
        return (int) static::connection()->query_and_fetch_one($wrapper,$values);
    }
}

This function will fire only if $options['group'] is set. Additionally, note that this executes a COUNT() of rows created by GROUP BY rather than a SUM(). This is meant to account for cases when $has_many and $options['joins'] are in play, so as to prevent double-counting when INNER JOIN returns multiple results for an association.

Collectives™ on Stack Overflow

PHPActiveRecord group option breaks count function

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related