Yii and ActiveRecord Group by Count

Question

I'm relatively new to Yii and would like to experiment a little. I have looked around all over the internet and I have absolutely no idea how to approach this. I have mastered the basics of Yii but I think that this is quite advanced or I'm just being stupid. I have a MYSQL table with a list of items bought. This table has CUSTOMER_ID as the main field with each CUSTOMER_ID possibly having more than one ITEM's. I would like to do a count BY TRANSACTION_ID and then assess as in line with the following logic: ``` 1) IF myCount = 1 then businessType = 'New'; 2) If myCount = 2 then businessType = 'Repeat'; 3) If myCount > 2 then businessType = '2+ Repeat'; ``` I'm not sure where and how would be the best way to do this however? afterSave()?? Or in the controller? I have to get the result of the query into a GROUPED CActiveDataProvider object as I feed this data into Highcharts. My grouping needs to look as follows (From lowest level to highest) : ``` businessType->store->item->{Count(ITEM…

Accepted Answer

to do this you will need to use CDbCriteria to group results in a CActiveDataProvider. See here http://www.yiiframework.com/doc/api/1.1/CDbCriteria#group-detail. You'll need to implement it in your controller something like this;

```
$criteria = new CDbCriteria;
$criteria->select = 'the columns to select, and maybe SUM()';
$criteria->group = 'columns to group by';
etc
etc

$dataProvider = new CActiveDataProvider('model name', $criteria);
```

Without knowing the exact implementation of your database I can only give a general example, but you should find something like this works for you. I'm not sure how to get the grouping your talking about as `businessType->store->item->{Count(ITEM), SUM(Cost)}`, as you haven't shown your database and model structure.

Yii and ActiveRecord Group by Count

Solution

Problem