count_all_results() produces invalid result with group by clause

[eluser]tahpot[/eluser]
Hi,

This bug occurs with the MySQL driver, most likely with others as well.

Codigniter assumes that calling "count (*)" over a query will always produce the total number of results for the given query (see $_count_string in mysql_driver.php). This is incorrect. When using "group by", count(*) doesn't produce a count of all the rows, it produces a result set, counting the number of entries for the given group by column(s).

To fix this I suggest that that the count_all_results() method should be overridden by the respective driver and implement a more driver specific solution (in this case with MySQL, make use of the SQL_CALC_FOUND_ROWS, possibly performing the query with limit = 1 first to get the value).

While I'm here, I also suggest adding a second parameter to count_all_results(), $reset_select=true. That way, it's possible to fetch the number of rows for a query and then fetch the results without having to completely rebuild the active record class.

Cheers,
Chris

[eluser]Unknown[/eluser]
+1

Has this post even been seen?

[eluser]Unknown[/eluser]
I sort of have a solution:

@tahpot you've suggested adding $reset to count_all_results.

I've added the $reset parameter to the get() method:

Code:

public function get($table = '', $limit = null, $offset = null, $reset = true)
{
...
}

now what i do is:

Code:

$this->db->select(...);
$this->db->from(...);
$this->db->group_by(...);
$query = $this->db->get('',null,null,false);  // added the "false" here
echo $query->num_rows();

it's not as pretty but it does the job and since we didn't run $query->result() it does not fetch all results (though it still executes the full select query).