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)