Yii and complex MySQL comparisons for search and sort functions

One of the things I recently ran into was wanting to sort and filter by a calculated result. Say, for example, you wanted to store total capacity of hard drives (GB) on several systems but wanted to sort by percentage of the drive that used.

Well, you're also going to need to know the capacity of those drives, so you'll need that field as well. However, let's suppose you don't want to store the %full / %empty values and would rather calculate those as needed.

At the beginning of our model, we need to add a variable declaration.

public $fill_pct;

In the CDbCriteria section of the sort function within the model, we're going to add the following:

$criteria->compare('(t.used / t.capacity * 100)', $this->fill_pct);

Then, in the CGridview, we can add something like this:

...
array(
    'name' => 'fill_pct',
    'value' => '@(sprintf(%d", $data->used / $data->capacity * 100))',
),
...

Now, we can filter things like >70 or <20 all.="" are="" aren="" as="" at="" but="" close="" find="" full="" getting="" great="" need="" now="" or="" p="" sort="" t="" that="" those="" to="" we="" well.="">
In the CActiveDataProvider (again in the model), we can add the following:

'sort' => array(
    'defaultOrder' => 'id ASC',
    'attributes' => array(
        'fill_pct' => array(
            'asc' => '(t.used / t.capacity)',
            'desc' => '(t.used / t.capacity) DESC'
        ),
        '*',
    ),
),

The default order can be adjusted to whatever you like (perhaps the same as fill_pct desc), and now you can sort by the % used. I didn't have to multiply by 100 here because that doesn't affect the order.

The more I use Yii, the more I enjoy how flexible it is.