GROUP BY and model()->count

Archives Yii 1.0.x Discussions
1

Hi,

I have a trouble using GROUP BY in my criteria. I have 89*3 (267) rows in my bbdd, those rows can be grouped in blocks of 3 elements, so if I use GROUP BY in my criteria, it returns me 89 rows.

Well, If I don't use GROUP BY, the function

Results::model()->count($criteria);

returns 267. But if I use GROUP BY in my criteria, model->count ($criteria) returns 3.

3 is just the number of rows grouped by GROUP BY, but isn't the number of rows in my query. I Use this return value for pagination constructor, so I need the number of rows.

Is this a bug or I'm doing something wrong?

This is my code:

$criteria=new CDbCriteria;

$criteria->select = "date";

$criteria->condition="idSearch=$idSearch";

$criteria->group="date";

$pages=new CPagination(Results::model()->count($criteria));

Thanks,

Manu.

2

Could you show what the generated SQL is?

3

Ok, this work's fine:

"SELECT date FROM TResults WHERE idSearch=1"

This doesn't work

"SELECT date FROM TResults WHERE idSearch=1 GROUP BY date"

4

What is the SQL generated by AR? You may turn on logging to see it.

5

This work's fine (Without GROUP BY)

2009/05/11 16:03:56 [trace] [system.db.CDbCommand] query with SQL: SHOW COLUMNS FROM TResultado

2009/05/11 16:03:56 [trace] [system.db.CDbCommand] query with SQL: SHOW CREATE TABLE TResultado

2009/05/11 16:03:56 [trace] [system.db.CDbCommand] query with SQL: SELECT COUNT(*) FROM TResultado WHERE idBusqueda=109

2009/05/11 16:03:56 [trace] [application] Value from model()->count() == 267

2009/05/11 16:03:56 [trace] [system.db.CDbCommand] query with SQL: SELECT fecha FROM TResultado WHERE idBusqueda=109 LIMIT 10

This doesn't work (With GROUP BY):

2009/05/11 16:04:29 [trace] [system.db.CDbCommand] query with SQL: SHOW COLUMNS FROM TResultado

2009/05/11 16:04:29 [trace] [system.db.CDbCommand] query with SQL: SHOW CREATE TABLE TResultado

2009/05/11 16:04:29 [trace] [system.db.CDbCommand] query with SQL: SELECT COUNT(*) FROM TResultado WHERE idBusqueda=109 GROUP BY fecha

2009/05/11 16:04:29 [trace] [application] Value from model()->count() == 3

2009/05/11 16:04:29 [trace] [system.db.CDbCommand] query with SQL: SELECT fecha FROM TResultado WHERE idBusqueda=109 GROUP BY fecha LIMIT 10

6

What result do you get when you use this statement directly on the database (e.g. with phpmyadmin)?

SELECT COUNT(*) FROM `TResultado` WHERE idBusqueda=109 GROUP BY fecha

If you only have 3 different values in "fecha", 3 would be the expected result as GROUP BY behaves similar to DISTINCT in an SQL query.

7

The results I get are 89 rows with a column with 3 as value.

Index    COUNT(*)

0          3

1          3

2          3

n          …

87        3

88        3

89        3

Yes, Resultado::model()->count($criteria), returns 3, the value of the first row.

I thought that this function return me the number of rows, and not the value of the first row.

Then, 3 is the correct value expected for this case?

8

I see. Hmm. If GROUP BY is present, COUNT(x) will always return the number of rows in a group no matter what x is. I think the only query that delivers the correct result in this case would be:

SELECT COUNT( DISTINCT( fecha )) FROM TResultado WHERE idBusqueda=109

Can you confirm that? I’m not sure, if AR should do this automatically when count() is called and the group property is set.

9

If i launch this query

SELECT COUNT( DISTINCT( fecha )) FROM TResultado WHERE idBusqueda=109

in phpMyAdmin, the result is 89

COUNT( DISTINCT( fecha ))

89