CGridView and aggregate queries (GROUP BY, SUM(), etc)

Yii 1.1 General Discussion
1

Hi all,

I am doing a CActiveDataProvider with criteria as follows:




$criteria->select = array('COUNT(id) as CNT', 'SUM(quantity) as QTY');

$criteria->group = 'id';

Now this vastly reduces the number of rows because the table is now only unique items grouped together, and it shows the total number of QTY.

My problems are as follows:

  1. The CGridView doesn’t “know” that there are not as many rows anymore. E.g. at the top it says “Displaying 1 to 25 of 21232 items.” even though by grouping the items there are now only about 1000 items. Also, the page numbers at the bottom allow me to keep clicking until the end as if the table had 21232 items.

  2. When sorting the CGridView by clicking the column headers they are not sorted according to QTY. I assume it’s being sorted by some random quantity instead.

Any insight or help would be greatly appreciated.

2

I found solution to #1 (or workaround, since it requires another query). Just add this to the data provider options:




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

Solution to #2! I needed to define a CSort and use it with the data provider.




$sort = new CSort;

$sort->attributes = array(

	'QTY'=>array(

		'asc'=>'QTY ASC',

		'desc'=>'QTY DESC',

		'default'=>'desc',

	),

);

Thanks for listening! :)

4

Thank you very much brother your solution solved my problem also… :) :) :)