count(CDBCriteria) with distinct and select params

Archives Bug Discussions
1

Hi

is it a bug?

code in the controller




                

                $columnas=array();

                //$columnas[]='idProfesional'; [edit]

                $columnas[]='sexo'; [edit]                

                $columnas[]='estado';

                

                $criteria->select=$columnas;

                $criteria->distinct=true;

                $cantidad=Rp_profesionales::model()->count($criteria);

                Yii::log($columnas[0], 'trace');

                Yii::log($columnas[1], 'trace');

.....

        $pages=new CPagination($cantidad);

        $pages->pageSize=self::PAGE_SIZE;

        $pages->applyLimit($criteria);

and the log

now

I expect this




SELECT COUNT(distinct sexo,estado) FROM `Rp_profesionales` [edit]

is a bug?

greetings and thanks

[EDIT]

temporary solution

$cantidad=[color="#ff0000"]count([/color]Rp_profesionales::model()->[color="#ff0000"]findAll/color[color="#ff0000"])[/color];

[EDIT 2] chage idProfesional for sexo

2

i found this issue

http://code.google.c…s/detail?id=675

the problem is similar

From my profound ignorance ???

to solve the problem for all case







class CDbCommandBuilder extends CComponent

{

......

    public function createCountCommand($table,$criteria)

    {

        $this->ensureTable($table);

        

        $c=new CDBCriteria;


        $criteria->order=$c->order; //default

        $criteria->limit=$c->limit;//default

        $criteria->offset=$c->offset;//default




        //return $this->createFindCommand($table,$criteria);

        return 'select count(*) from ('.$this->createFindCommand($table,$criteria).') AS subquery_random_name;';


    }

....

original




    public function createCountCommand($table,$criteria)

    {

        $this->ensureTable($table);

        $criteria->select='COUNT(*)';

        return $this->createFindCommand($table,$criteria);

    }
3

No entiendo mucho lo que quisiste hacer en el command builder, pero probaste esto (SIN modificar el command builder)




$columnas=array();

$columnas[]='DISTINCT(idProfesional)'; // EDITE ESTA LINEA

$columnas[]='estado';

$criteria->select=$columnas;

//$criteria->distinct=true; <-- ESTO NO LO PONGAS

$cantidad=Rp_profesionales::model()->count($criteria);

Yii::log($columnas[0], 'trace');

Yii::log($columnas[1], 'trace');

No lo probe, pero calculo que funciona. Igual, si lo probas, me pasarias el SQL resultante a ver que quedo de eso?

4

NOTA: Todo lo que esta en el core no deberias tocarlo. Poruqe si hacen alguna modificacion y vos actualizas el core perdes todos los cambios que hayas hecho. Siempre es mejor heredar (o mejor dicho subclasificar) y usar esas subclases que vos crees con la funcionalidad añadida/cambiada.

5

The idea is change the core, to fix my problem and the issue http://code.google.c…s/detail?id=675

6

not work

CDbException

Descripción

Active record "Rp_profesionales" esta intentando de seleccionar unacolumna inválida "DISTINCT(idProfesional)". Nota: La columna puedeexistir en la base o ser una expresion con alias.

7

Ok, after reading the original and the bug post, finally I understand the solution proposeb by you!!!

I think that it is ok…

We must wait until Qiang look here…

8

The only thing I think we should be aware of is that the generated SQL is working in all DBMS. Also check the performance of this two options (auxiliary table creations, etc:




        SELECT COUNT(distinct id, data) FROM tbl;

vs




       SELECT COUNT(*) FROM (SELECT distinct id, data FROM tbl);

I think this is a better approach:




public function createCountCommand($table,$criteria)

{

     $this->ensureTable($table);

   if ($criteria->distinct){

          $select=is_array($criteria->select) ? implode(', ',$criteria->select) : $criteria->select;

          $criteria->select = 'COUNT (DISTINCT ' . $select . ')';

     }else{

          $criteria->select='COUNT(*)';

   }

   return $this->createFindCommand($table,$criteria);

}
9

Me olvide de decirte que el bug al que vos haces referencia tiene otro problema que es que no se usa el count como dice que debe ser usado y si devuelve lo que realmente debe devolver según mi opinión…

Saludos,

10

Yeah, thats good…

Just one thing:

in the


return $this->createFindCommand($table,$criteria);

The createFindCommand will take care of the $criteria->distict again, rigth?

If so, an even better aproach is:




public function createCountCommand($table,$criteria)

{

   $this->ensureTable($table);

   if ($criteria->distinct){

          $select=is_array($criteria->select) ? implode(', ',$criteria->select) : $criteria->select;

          $criteria->select = 'COUNT (DISTINCT ' . $select . ')';

          $criteria->distinct = false; // ADDED


     }else{

          $criteria->select='COUNT(*)';

   }

   return $this->createFindCommand($table,$criteria);

}

Rigth?

11

thanks Sebas!!!!

The only thing I think we should be aware of is that the generated SQL is working in all DBMS. Also check the performance of this two options (auxiliary table creations, etc:




        SELECT COUNT(distinct id, data) FROM tbl;

vs




       SELECT COUNT(*) FROM (SELECT distinct id, data FROM tbl);

[color="#0000ff"]You’re right[/color]

I think this is a better approach:




public function createCountCommand($table,$criteria)

{

     $this->ensureTable($table);

   if ($criteria->distinct){

          $select=is_array($criteria->select) ? implode(', ',$criteria->select) : $criteria->select;

          $criteria->select = 'COUNT (DISTINCT ' . $select . ')';

     }else{

          $criteria->select='COUNT(*)';

   }

   return $this->createFindCommand($table,$criteria);

}

[color="#0000ff"][b]this not work for group by

http://code.google.com/p/yii/issues/detail?id=675

[/b][/color]

12

thanks PoL !!!

We are nearly done

now manage "group"

13

I was thinking… there is no reason to do all this…

Because:

For example COUNT(DISTICNT(something)) FROM something _else and DISTINCT(COUNT(something) FROM something_else

Are both valir SQL query… so: wich one will win?

So, to deal whit this kind of querys I propose nested criterias:

for example:

$criteria1.select = ‘something’;

$criteria1.group = ‘something2’;

$criteria1.distinct = true;

$criteria2.select = $criteria1;

$criteria2.disticnt = true;

…

And so on…

What you think guys?

I have not enough time to make some implementation now, but in few days maybe…

14

DISTINCT(COUNT(something) is not valid result,[b] always return 1 row(distinct not helpful)

and the count is total not only the distinct[/b]

good point!!!!

[b]I think we are complicated

hope you until you can do

greetings

or … use my first solution ;D

[/b]


return 'select count(*) from ('.$this->createFindCommand($table,$criteria).') AS subquery_random_name;';

[b]

[/b]

15

The point is that

COUNT(DISTICNT(something)) FROM something _else

and

DISTINCT(COUNT(something) FROM something_else

ARE both VALID querys (not means that the result is or not usefull)…

So if both are valid: why must win one over the other?

But, with nested criterias, we can control more especific these kind of querys…

Your solution is good, but just for those especific querys, what happens y I want the other one?

Again, nested criterias, IMHO, would be a great enhacement…