Add a Group-By Scope to Yii's CActiveRecord Subclass

Yii Framework allows for named scopes to be added to a CActiveRecord's subclass (you can read more about this here). This also makes it more convenient to filter models based on a predetermined criteria. You can chain them with any filter methods of that class, and ultimately the CDbCriteria methods like `findAll()`.

To site an example, let's have a database table named `animals`. It also has the following fields: id, name, and classification. Let's fill this up with some data. I know this can still be normalized, but let's just leave it like this for the sake of simplicity.

[ 1, 'Eagle',     'Bird']
[ 2, 'Peacock',   'Bird']
[ 3, 'Kangaroo',  'Mammal']
[ 4, 'Dog',       'Mammal']
[ 5, 'Horse',     'Mammal']
[ 6, 'Snake',     'Reptile']
[ 7, 'Turtle',    'Reptile']
[ 8, 'Lizard',    'Reptile']
[ 9, 'Crocodile', 'Reptile']
[10, 'Spider',    'Arthropods']

Now, assuming we're asked to provide a summary of classifications, like return a list of classifications with a number of animals in each of them. This is how our sql query looks like if nothing else is added in the criteria.

SELECT t.classification, COUNT(*) AS animalCount
  FROM `animals` AS t
  GROUP BY t.classification
  ORDER BY animalCount DESC;

If you try to run this query, the result looks something like this.

[Reptile,     4]
[Mammal,      3]
[Bird,        2]
[Arthropods,  1]

Let's assume that we've created a model class named `Animal`, which represents our `animals` db table and has the following method which does the same thing as the aforementioned sql query. It looks like the following:

public function scopes()
{
  return array(
    'groupByClass' => array(
      'group'   => 't.classification',
      'select'  => 't.classification, COUNT(*) as animalCount',
      'order'   => 'animalCount DESC',
    ),
  );
}

This is the overriden method coming from CActiveRecord. To use this, we simply type:

Animal::method()->groupByClass()->findAll();

Then we get the same result:

[Reptile,     4]
[Mammal,      3]
[Bird,        2]
[Arthropods,  1]

You must be wondering how the heck I'm supposed to access `animalCount` alias. Well for that, we can simply add a special property. That would of course be of the same name, which is `animalCount`.

So there you have it.