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.
