count_all_results() not returning correct result

[eluser]alphastudio[/eluser]
This is an old thread but I am having the same issue.

The trouble is the count_all_results method uses a query that starts with SELECT COUNT(*) AS `numrows`..., so if your query uses GROUP BY you don't get a count of all results, but a count of each group, and the returned count is whichever comes first.

For instance :

Code:

$this->db->from( 'table1' );
$this->db->join('table2', 'table1.id = table2.id' );
$this->db->group_by( 'table2.something' );
$this->db->count_all_results();

will run this query:

Code:

SELECT COUNT(*) AS `numrows`
FROM (`table1`)
LEFT JOIN `table2` ON `table1`.`id` = `table2`.`id`
GROUP BY `table2`.`something`

returning something like:

| numrows |
| 6 |
| 1 |
| 1 |
| 3 |

And so count_all_results will return 6, instead of 4, which would be the number of rows returned by the join query.

Any idea on how to get a real count of all results when using group by ?

I am, like the original poster, meaning to count the total records for pagination, when only retrieving the records for the current page.