count() with group by

Yii 1.1 Tips, Snippets and Tutorials
1

Hi,

For a stats page, I needed to know how many users of each type I had.

There is a lot of discussion online on how to do this. This is the simplest I found, so I thought I’d share…

Model:


// Variable holding the count value - required !

public $user_count;

public function search($dateReadingRange=null)

{

	$criteria=new CDbCriteria;

	$criteria->compare('t.id',$this->id,true);

	$criteria->compare('usertype_id',$this->number);

	$criteria->compare('name',$this->name,true);

	(... more conditions ...)


	// Grouping

	$criteria->group = 'usertype_id';

	// Counting. The result of this will automagically be stored in $model->user_count

	$criteria->select = "count(t.id) as user_count";


	(...)

View


(... gridview definition ...)

'columns'=>array(

	array(

		'name' => 'user_count',

		'value' => '$data->submeter_count',

	),

(...more columns...)