Sql With 'group By' Makes Cgridview Summary Inconsistent

Yii 1.1 General Discussion
1

I have a CGridView, which normally shows a summary like this:

Displaying 1-4 of 4 results.

I need to apply a grouping into the underlying table before listing records in the CGridView, so I added corresponding property into CDBCriteria:


$criteria->group = 'fieldname';

Of course, this leads to the fact that total number of records (for output) should be less than without grouping. Actually, I got 1 record on my test table. But CGridView’s summary states the following:

[b]Displaying 1-1 of 4 results.

[/b]

This looks like a bug, imho. How can I make sure that the summary does always report total number of records correctly, even when "group by" SQL clause is involved?

As far as I can tell from generated logs, Yii runs separate query with "SELECT COUNT(DISTINCT…" for detecting total number of records. The problem is that this query somehow is not affected by the "group" property of the CDBCriteria. This property does only affect the second query, which actually fetches data from DB. This is why the number of rows available for user and summary do not match each other. How to fix this?

Thanks in advance.

2

I have pinpointed the problem down to CActiveFinder::count method.

First, I don’t understand the reason why grouping is explicitly removed in the line


$query->orders=$query->groups=$query->havings=array();

Second, it is obvious that if a query contains GROUP BY clause, that is $criteria->group is defined, and consequently $query->group array is not empty, they must be somehow added into the sql query, perhaps in an additional if-branch with a code of a kind:


$column = array_filter($query->groups)[0];

$query->selects=array("COUNT(DISTINCT $column)");

This is just a sketch - I was unable to make it work without side effects (for example, {start} and {stop} values became negative). So I decided to stop further investigations by applying a workaround suggested here on the forum with an additional sql query, like this, in data provider options:


'totalItemCount' => Yii::app()->db->createCommand('SELECT COUNT(DISTINCT `id`) FROM `table`')->queryScalar()

where id is a column which is used in GROUP BY clause for fetching data.