Yii - CDbCriteria->compare as list

Sometimes you may want to restrict your a cgridview to only display records with a list of values.

The CDbCriteria class reference page stipulates that the allowed values are:

the column value to be compared with. If the value is a string, the aforementioned intelligent comparison will be conducted. If the value is an array, the comparison is done by exact match of any of the value in the array. If the string or the array is empty, the existing search condition will not be modified.

For example, I am building a bug/issue tracker and I want the admin grid to only display outstanding bugs by default.

I have a list of valid statuses in my options table and I'm going to say that anything with a sort_order < 10 is outstanding, anything greater is completed. The obvious place to do this is in the Issue->search() function.

$criteria->compare('status', array(...));  //here place an array of valid statuses

Firstly, what is a nice quick and easy way to extract the array of statuses from the Options table.  I've seen a number of posts about this including using listData on a model, which is very wasteful.

I think this is probably the most elegant solution


$ostatuses = Yii::app()->db->createCommand('SELECT option_code FROM options WHERE option_name=\'ISSUE-STATUS\' and sort_order<10')->queryAll();
$ost=array();
foreach ($ostatuses as $or) {
      $ost[]=$or['option_code'];
}
criteria->compare('status',$ost);

  1. Query the Options table to extract the "outstanding" status codes But note that the array returned is a 2 dimensional array - one that the compare function does not like.  It looks something like this:

Array
(
[0] => Array
(
[option_code] => CLOSED
)
[1] => Array
(
[option_code] => CUST
)
[2] => Array
(
[option_code] => DEV
)
[3] => Array
(
[option_code] => INV
)
[4] => Array
(
[option_code] => READY
)
[5] => Array
(
[option_code] => REP
)
)

  1. Define our new empty array 3.4.5. Loop each first dimension and add the 'option_code' element to our new array

One more thing

Now that I have limited my search to only display outstanding bugs, it will always do this.  So I've added a property to the Issue model that I can set if I want to display all statues.

At the top of the model declare a public variable


class Issue extends CActiveRecord
{
        public $os_only=true;

So, by default, the variable is set to true

Then in the search function add a check for the value of this property


                if ($this->os_only) {
>findAll('option_name=:op',array(':op'=>'ISSUE-STATUS')), 'option_name', '');
                    $ostatuses = Yii::app()->db->createCommand('SELECT option_code FROM options WHERE option_name=\'ISSUE-STATUS\' and sort_order<10')->queryAll();
                    $ost=array();
                    foreach ($ostatuses as $or) {
                        $ost[]=$or['option_code'];
                    }
                    $criteria->compare('status',$ost);
    
                } else {

                    $criteria->compare('status',$this-Status);
                }

I guess a slightly better way to do this might be using scopes, so watch this space for that ...


Did you know you can hire me?

I take on projects of all sizes. From Consulting to large Development Projects.

If you're starting a new Yii project and would like some help to get setup and running or you need some help with a particular module or you just need someone to develop the whole dang thing, then just ask ...


Beginning Yii ...

I'm very excited about a new Yii Training course that has just been published.

The course is designed for anyone wanting to learn Yii, whether new to frameworks or converting from another.

Eight chapters, each having five videos of between 3 and 5 minutes, take you through the basics of the Yii Framework, building a web application step-by-step.

Save 40% - introductory offer $20.99 / £13.19 / €16.19 / A$20.99

One comment

  • facebook_alex.alexandrov.7503
    23/08/2012

    thank you very helpful

Leave a Comment

twitterfacebookgooglelinkedin https://me.yahoo.com