Possible bug on count_all_results with distinct clause???

Possible bug on count_all_results with distinct clause??? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Possible bug on count_all_results with distinct clause??? (/showthread.php?tid=30241)

Possible bug on count_all_results with distinct clause??? - El Forum - 05-07-2010

[eluser]Nextneed[/eluser]
hi to all,
i've notice that the following statement

$this->db->distinct("azioni_finanziabili_id");
$this->db->from("azioni_schede_beneficiari_schede");
$this->db->where_in("beneficiari_schede_id", $beneficiari_check);
$azioni_count = $this->db->count_all_results();

produce the following query:
SELECT COUNT(*) AS numrows
FROM (azioni_schede_beneficiari_schede)
WHERE beneficiari_schede_id IN ('65', '67')

and ignore the distinct statement...

if i run the same query instead of counting results i obtain the following...
SELECT DISTINCT *
FROM (azioni_schede_beneficiari_schede)
WHERE beneficiari_schede_id IN ('67', '65')

which is correct...

bye
Luca

Possible bug on count_all_results with distinct clause??? - El Forum - 05-07-2010

[eluser]WanWizard[/eluser]
What are you trying to archieve?

You can not use DISTINCT and COUNT(*) in one query, at least not the way you are trying to do it.

Either use

Code:
SELECT DISTINCT *

FROM (azioni_schede_beneficiari_schede)

WHERE beneficiari_schede_id IN (‘67’, ‘65’)

and then use the num_rows() method to get the count, or

Code:
SELECT COUNT( DISTINCT azioni_finanziabili_id ) AS numrows

FROM (azioni_schede_beneficiari_schede)

WHERE beneficiari_schede_id IN (‘67’, ‘65’)

but there is no support for the last syntax in CI's AR class...

Possible bug on count_all_results with distinct clause??? - El Forum - 08-11-2011

[eluser]deadelvis[/eluser]
Ran into the same problem. In a nutshell:

This will NOT work (returns ALL rows in table instead)...

Code:
$this->db->select('col');

$this->db->distinct();

$this->db->from('table');

return $this->db->count_all_results();

This will work...

Code:
$this->db->select('col');

$this->db->distinct();

$this->db->from('table');

$query = $this->db->get();

return $query->num_rows();

Possible bug on count_all_results with distinct clause??? - El Forum - 08-11-2011

[eluser]jmadsen[/eluser]
from the mysql_driver.php (and all other drivers, I believe):

Code:
/**

     * The syntax to count rows is slightly different across different

     * database engines, so this string appears in each driver and is

     * used for the count_all() and count_all_results() functions.

     */

    var $_count_string = 'SELECT COUNT(*) AS ';

So, using count_all_results() this way will not work - you cannot create a COUNT(DISTINCT col) with it.

Workaround is obviously to use the ->select() method, and the Guide should prolly be updated