Yii Framework Forum: count(CDBCriteria) with distinct and select params - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

count(CDBCriteria) with distinct and select params Rate Topic: -----

#1 User is offline   gallego123 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 09 November 2009 - 08:58 AM

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

Quote

2009/11/0910:29:58 [trace] [system.db.CDbCommand] Querying SQL: SELECT DISTINCT COUNT(*) FROM `Rp_profesionales`
2009/11/09 10:29:58 [trace] [application] sexo
2009/11/09 10:29:58 [trace] [application] estado


now

I expect this

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



is a bug?

greetings and thanks

[EDIT]
temporary solution

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

[EDIT 2] chage idProfesional for sexo

This post has been edited by Horacio Segura: 12 November 2009 - 09:48 AM

KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
0

#2 User is offline   gallego123 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 12 November 2009 - 08:57 AM

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);
    }


This post has been edited by Horacio Segura: 12 November 2009 - 10:12 AM

KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
0

#3 User is offline   PoL 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 506
  • Joined: 05-November 08
  • Location:Buenos Aires, Argentina

Posted 12 November 2009 - 09:25 AM

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?
Don't say what you think, think what you say
The problem is communication! Excess of communication!
0

#4 User is offline   PoL 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 506
  • Joined: 05-November 08
  • Location:Buenos Aires, Argentina

Posted 12 November 2009 - 09:27 AM

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.
Don't say what you think, think what you say
The problem is communication! Excess of communication!
0

#5 User is offline   gallego123 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 12 November 2009 - 09:53 AM

View PostPoL, on 12 November 2009 - 09:27 AM, said:

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.


The idea is change the core, to fix my problem and the issue http://code.google.c...s/detail?id=675
KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
0

#6 User is offline   gallego123 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 12 November 2009 - 09:54 AM

View PostPoL, on 12 November 2009 - 09:25 AM, said:

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?


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.


KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
0

#7 User is offline   PoL 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 506
  • Joined: 05-November 08
  • Location:Buenos Aires, Argentina

Posted 12 November 2009 - 09:58 AM

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....
Don't say what you think, think what you say
The problem is communication! Excess of communication!
0

#8 User is offline   sebas 

  • Advanced Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 500
  • Joined: 28-October 08
  • Location:Buenos Aires, Argentina

Posted 12 November 2009 - 10:41 AM

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);
}


0

#9 User is offline   sebas 

  • Advanced Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 500
  • Joined: 28-October 08
  • Location:Buenos Aires, Argentina

Posted 12 November 2009 - 10:47 AM

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,
0

#10 User is offline   PoL 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 506
  • Joined: 05-November 08
  • Location:Buenos Aires, Argentina

Posted 12 November 2009 - 10:50 AM

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?

Don't say what you think, think what you say
The problem is communication! Excess of communication!
0

#11 User is offline   gallego123 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 12 November 2009 - 11:00 AM

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

You're right



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);
}


this not work for group by
http://code.google.c...s/detail?id=675

This post has been edited by Horacio Segura: 12 November 2009 - 04:07 PM

KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
0

#12 User is offline   gallego123 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 12 November 2009 - 03:58 PM

View PostPoL, on 12 November 2009 - 10:50 AM, said:

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->group){
          $select=is_array($criteria->group) ? implode(', ',$criteria->group) : $criteria->group;
          $criteria->select = 'COUNT (DISTINCT ' . $select . ')';
          $criteria->distinct = false; // ADDED

     }
  else 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?


thanks PoL !!!

We are nearly done

now manage "group"
KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
0

#13 User is offline   PoL 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 506
  • Joined: 05-November 08
  • Location:Buenos Aires, Argentina

Posted 13 November 2009 - 07:29 AM

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...
Don't say what you think, think what you say
The problem is communication! Excess of communication!
0

#14 User is offline   gallego123 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 13 November 2009 - 08:26 AM

View PostPoL, on 13 November 2009 - 07:29 AM, said:

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?


DISTINCT(COUNT(something) is not valid result, always return 1 row(distinct not helpful)
and the count is total not only the distinct

Quote


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

good point!!!!

Quote

What you think guys?

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


I think we are complicated

hope you until you can do

greetings

or .... use my first solution ;D

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


KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
0

#15 User is offline   PoL 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 506
  • Joined: 05-November 08
  • Location:Buenos Aires, Argentina

Posted 13 November 2009 - 08:53 AM

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...
Don't say what you think, think what you say
The problem is communication! Excess of communication!
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users