[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.