Possible bug on count_all_results with distinct clause???

[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

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

[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();

[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