Posts

Showing posts from 2015

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…