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 ...


Jumpstart Yii2 ... Coming Soon!

I'm in the process of planning a new course on Yii2 and want your feedback.

What is your preferred format? What did you think of my Yii 1.x video course "Beginning Yii"?

What would you like to see different?

How much would you pay?

One comment

  • facebook_alex.alexandrov.7503
    23/08/2012

    thank you very helpful

Leave a Comment

twitterfacebookgooglelinkedin https://me.yahoo.com