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

 PHP | 
 copy code |
?
 
1
$ostatuses = Yii::app()->db->createCommand('SELECT option_code FROM options WHERE option_name=\'ISSUE-STATUS\' and sort_order<10')->queryAll();
2
$ost=array();
3
foreach ($ostatuses as $or) {
4
      $ost[]=$or['option_code'];
5
}
6
criteria->compare('status',$ost);
7

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:

 PHP | 
 copy code |
?
 
01
Array
02
(
03
[0] => Array
04
(
05
[option_code] => CLOSED
06
)
07
[1] => Array
08
(
09
[option_code] => CUST
10
)
11
[2] => Array
12
(
13
[option_code] => DEV
14
)
15
[3] => Array
16
(
17
[option_code] => INV
18
)
19
[4] => Array
20
(
21
[option_code] => READY
22
)
23
[5] => Array
24
(
25
[option_code] => REP
26
)
27
)

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

 PHP | 
 copy code |
?
 
1
class Issue extends CActiveRecord
2
{
3
        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

 SQL | 
 copy code |
?
 
01
                IF ($this->os_only) {
02
>findAll('option_name=:op',array(':op'=>'ISSUE-STATUS')), 'option_name', '');
03
                    $ostatuses = Yii::app()->db->createCommand('SELECT option_code FROM options WHERE option_name=\'ISSUE-STATUS\' and sort_order<10')->queryAll();
04
                    $ost=array();
05
                    foreach ($ostatuses AS $or) {
06
                        $ost[]=$or['option_code'];
07
                    }
08
                    $criteria->compare('status',$ost);
09
 
10
                } ELSE {
11
 
12
                    $criteria->compare('status',$this-STATUS);
13
                }

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