CDbCriteria In Yii

CDbCriteria is the one of best support in yii framework. CDbCriteria is used to assign the values or property in query. Using cdbcriteria property we can assign condition, order, limit, scopes etc for query. It have some methods to apply condition for model or sql table like addCondition(), addInCondition() etc .


Note: $model is the object of 'post' table class

alias

the alias name of the table

$criteria=new CDbCriteria;
$criteria->alias = 'tt';
//Apply To Model
$models = Modelname::model()->findAll($criteria);

OUTPUT

SELECT * FROM `post` `tt`

Condition

assign the sql query condition

$criteria = new CDbCriteria;
$criteria->condition = 'status =1 OR status=2';
//Apply To Model
$models = Modelname::model()->findAll($criteria);
//Apply To CActiveDataProvider
return new CActiveDataProvider($model, array(   'criteria'=>$criteria,  ));

Distinct

apply distinct row selection

$criteria = new CDbCriteria;
$criteria->condition = 'comments="P"';
$criteria->distinct=true;
//Apply To Model
$models = Modelname::model()->findAll($criteria);
//Apply To CActiveDataProvider
return new CActiveDataProvider($model, array(   'criteria'=>$criteria,  ));

Group

Group the result by assign the column name

$criteria = new CDbCriteria;
$criteria->group='postid';
//Apply To Model
$models = Modelname::model()->findAll($criteria);
//Apply To CActiveDataProvider
return new CActiveDataProvider($model, array(   'criteria'=>$criteria,  ));

Having

Assign the condition for group by filtered value

$criteria = new CDbCriteria;
$criteria->having='comments="P"';
$criteria->group='postid';
//Apply To Model
$models = Modelname::model()->findAll($criteria);
//Apply To CActiveDataProvider
return new CActiveDataProvider($model, array(   'criteria'=>$criteria,  ));

Join

Join one or more tables with current table

$criteria = new CDbCriteria;
//LEFT JOIN
$criteria->join='LEFT JOIN User c ON c.userid=t.userid';
// OR INNER JOIN
$criteria->join="INNER JOIN tbl_user as user ON(user.userid=t.user_id)";
//Apply To Model
$models = Modelname::model()->findAll($criteria);
//Apply To CActiveDataProvider
return new CActiveDataProvider($model, array(   'criteria'=>$criteria,  ));

Limit, Offset

Set the limit and offset values for result

$criteria=new CDbCriteria;
$criteria->limit=10;
$criteria->offset=10;	
//Apply To Model
$models = Modelname::model()->findAll($criteria);
//Apply To CActiveDataProvider
return new CActiveDataProvider($model, array(   'criteria'=>$criteria,  ));

Order by

sort the result of query

$criteria=new CDbCriteria;
$criteria->order = "userid";
//Apply To Model
$models = Modelname::model()->findAll($criteria);
//Apply To CActiveDataProvider
return new CActiveDataProvider($model, array(   'criteria'=>$criteria,  ));

Params

assign the parameter value

$criteria=new CDbCriteria;
$criteria->condition= "userid=:userid";
$criteria->params=array(':userid'=>$userid);
//Apply To Model
$models = Modelname::model()->findAll($criteria);
// OR Apply To CActiveDataProvider
return new CActiveDataProvider($model, array(   'criteria'=>$criteria,  ));

scopes

Assign condition or filter the recordes using scores method. scopes method is reusable one.

$criteria=new CDbCriteria;
//One Scope
$criteria->scopes='activepost';
//Many Scope
$criteria->scopes=array('activepost','inactivepost');
//Scope With Parameters
$criteria->scopes=array('activepost'=>array($params));
//Multiple Scope With Parameters
$criteria->scopes=array('activepost'=>array($params1),'inactivepost'=>array($params2));
//Apply To Model
$models = Modelname::model()->findAll($criteria);
// OR Apply To CActiveDataProvider
return new CActiveDataProvider($model, array(   'criteria'=>$criteria,  ));

Model For Scopes

model example with scope function

class Post extends CActiveRecord
{
    ......
    public function scopes()
    {
        return array(
            'activepost'=>array(
                'condition'=>'status=1',
                'order'=>'postid',
            ),
            'inactivepost'=>array(
                'condition'=>'status=0',
            ),
        );
    }
}

SELECT

give the columns name being selected

$criteria=new CDbCriteria;
$criteria->select='t.userid, t.username';
//Apply To Model
$models = Modelname::model()->findAll($criteria);
// OR Apply To CActiveDataProvider
return new CActiveDataProvider($model, array(   'criteria'=>$criteria,  ));

together

Using this we can join foreign table with primary key table

$criteria=new CDbCriteria;       
$criteria->together=true;

With

relational query criteria

$criteria=new CDbCriteria;
$criteria->with=array('user');
return new CActiveDataProvider($model, array(   'criteria'=>$criteria,  ));

OUTPUT

SELECT COUNT(DISTINCT `t`.`postid`) FROM `post` `t` 
LEFT OUTER JOIN `user` `user` ON
(`t`.`userid`=`user`.`userid`)

addBetweenCondition

Using this get the result between two values.

$criteria=new CDbCriteria;        
$criteria->addBetweenCondition("t.createdon",$this->date_after,$this->date_before,"AND");

OUTPUT

SELECT COUNT(DISTINCT `t`.`userid`) FROM `usermaster` `t` 
    WHERE (t.createdon BETWEEN :ycp0 AND :ycp1)

Compare

add comparison in query condition

$criteria=new CDbCriteria;
$criteria->compare('year(`EventStartdate`)','>=:'.$currentYear); 
$criteria->compare('year(`EventStartdate`)','>='.$currentYear); 
$criteria->compare('t.status',$this->status,true);

addCondition

add condition in query

$criteria = new CDbCriteria;
$criteria->addCondition('status=1','AND');
$criteria->addCondition("status='$this->status'");
//Criteria To Model
$models = Modelname::model()->findAll($criteria);

addInCondition

add IN condition in query

                
$criteria=new CDbCriteria;
$criteria->addInCondition('categoryid',$this->categoryid,true);                  
$criteria->addInCondition('id',array(1,2,3,4,5,6));

addNotInCondition

add NOT IN condition in query

                
$criteria->addNotInCondition('categoryid',$this->categoryid,true);                  
$criteria->addNotInCondition('id',array(1,2,3,4,5,6));

addSearchCondition

add LIKE condition in query

$criteria=new CDbCriteria;
$criteria->addSearchCondition('t.post','yii');

OUTPUT

SELECT COUNT(DISTINCT `t`.`postid`) FROM `post` `t` WHERE (t.post LIKE :ycp0)