Yii Examples of Using CDbCriteria

Examples of Using CDbCriteria
Basic Usage

$Criteria = new CDbCriteria();
$Criteria->condition = "price > 30";
$Products = Product::model()->findAll($Criteria);

OR

//An example using the constructor to populate the properties.
$Criteria = new CDbCriteria(array('condition' => 'price > 30'));
$Products = Product::model()->findAll($Criteria);

Personally, I like to go with the first approach. I think it’s generally easier to read, but that’s just my personal preference.
Adding A Limit

$Criteria = new CDbCriteria();
$Criteria->condition = "price > 30";
$Criteria->limit = 1;
$Products = Product::model()->findAll($Criteria);

Limit with Offset

$Criteria = new CDbCriteria();
$Criteria->condition = "price > 30";
$Criteria->limit = 1;
$Criteria->offset = 1;
$Products = Product::model()->findAll($Criteria);

Ordering Results

$Criteria = new CDbCriteria();
$Criteria->condition = "price > 30";
$Criteria->limit = 1;
$Criteria->offset = 1;
$Criteria->order = "name ASC";
$Products = Product::model()->findAll($Criteria);

Limiting Selected Fields

$Criteria = new CDbCriteria();
$Criteria->condition = "price > 30";
$Criteria->limit = 1;
$Criteria->offset = 1;
$Criteria->order = "name ASC";
$Criteria->select = "id, name";
$Products = Product::model()->findAll($Criteria);

Example relation with :

$criteria = new CDbCriteria;
$criteria->with = array('foreign_table1',
                        'foreign_table2', 
                        'foreign_table2.foreign_table3');
$criteria->together = true; // ADDED THIS
$criteria->select = array('id');
$criteria->condition = "foreign_table1.col1=:col_val AND 
                        foreign_table3.col3=:col_val2";
$criteria->params = array(':col_val' => some_val, ':col_val2' => other_val);
$criteria->order = 'foreign_table3.col5 DESC';
$criteria->limit = 10;

Getting Maximum ID through Criteria : 

$criteria=new CDbCriteria;                                                                                      
$criteria->select = 'max(id) as id';                                                                            
$criteria->condition = 'inwno=:param and iyear=:param2';                                          
$criteria->params = array(':param' => $inwno,':param2' => $inwyr);                            
$transId=InwTrans::model()->find($criteria);                                                             

$max=$transId['id'];                                                                                               

You have to supply the member id(s) array itself to the

addInCondition

, 

$criteria = new CDbCriteria;
$criteria->compare('project.ID',$project_id);
$criteria->addInCondition('t.REF_USER',$members);

$user2groups = User2group::model()
                        ->with('group')
                        ->with('group.project')
                        ->findAll($criteria);

These options all result in the exact same query: SELECT (..) WHERE (t.id=:ycp0). 

Choose the one that you like best:

$criteria = new CDbCriteria;
// Classic method
$criteria->addCondition('t.id = :id');
$criteria->params = array(':id' => Yii::app()->user->id);
// Often used in search functions. Note: if passed value is empty, the WHERE is not added!
$criteria->compare('t.id', Yii::app()->user->id);
// This is my current favorite
$criteria->addColumnCondition(array('t.id' => Yii::app()->user->id));
// A bit weird here, but you can also do this
$criteria->addInCondition('t.id', array(Yii::app()->user->id));

In my case, I was trying to return the results of my query in a descending fashion, depending on a foreign table's column. So I used code like


$criteria = new CDbCriteria;
$criteria->with = array('foreign_table1',
                        'foreign_table2', 
                        'foreign_table2.foreign_table3');
$criteria->select = array('id');
$criteria->condition = "foreign_table1.col1=:col_val AND 
                        foreign_table3.col3=:col_val2";
$criteria->params = array(':col_val' => some_val, ':col_val2' => other_val);
$criteria->order = 'foreign_table3.col5 DESC';
$criteria->limit = 10;



So, my SQL would crash because I would get a column not found error, or an access error. I spent a day focusing on my condition statement, when the problem was actually with the use of the limit command. Without the LIMIT command, I got my results. With the LIMIT command, an error

So, based upon looking around in the forum, the reason was that I should have used TOGETHER, as so:


$criteria = new CDbCriteria;
$criteria->with = array('foreign_table1',
                        'foreign_table2', 
                        'foreign_table2.foreign_table3');
$criteria->together = true; // ADDED THIS
$criteria->select = array('id');
$criteria->condition = "foreign_table1.col1=:col_val AND 
                        foreign_table3.col3=:col_val2";
$criteria->params = array(':col_val' => some_val, ':col_val2' => other_val);
$criteria->order = 'foreign_table3.col5 DESC';
$criteria->limit = 10;



Because TOGETHER links together your foreign tables and allows you to execute one sql statement, this is what is needed not only to make LIMIT for a foreign table's column to work





If your model has a HAS_MANY in relations and you want to join it up (in case for example you want to search in the related table as well), then next to setting up the with, you also need to set the together to true. So in your model you have:


class MyFirstTable extends CActiveRecord {
...
    public function relations() {
        return array(
            'mySecondTable' => array(self::HAS_MANY, 'MySecondTable', 'second_table_id'),
        );
    }
...
}



Then to be able to search in the second table data as well you need to have the search method like this:


public function searchWithRelated() {
        $criteria = new CDbCriteria;
 
        $criteria->together = true; //without this you wont be able to search the second table's data
        $criteria->with = array('mySecondTable');
        $criteria->compare('id', $this->id, true);
        $criteria->compare('mySecondTable.column', $this->mySecondTable_column, true);
 
        return new CActiveDataProvider($this, array(
            'criteria' => $criteria,
            'sort'=>array(
                'defaultOrder'=>'t.id DESC',
            ),
            'pagination' => array(
                'pageSize' => 100,
            ),
        ));
    }