Using CDbCriteria in the Yii Framework

July 24, 2013 — 3 Comments
The Yii Book If you like my writing on the Yii framework, you'll love "The Yii Book"!

The CDbCriteria class let’s you customize queries through an object. The first thing you should do to become more comfortable with Active Record when using the Yii framework is master usage of CDbCriteria.

This is an excerpt from Chapter 8, “Working with Databases,” of “The Yii Book“.

To start, create a CDbCriteria instance:

$criteria = new CDbCriteria();

Then you can customize it by assigning values to various properties, the most important of which are listed in the following table.

Property Sets
condition The WHERE clause
limit The LIMIT value
offset The offset value in a LIMIT clause
order The ORDER BY clause
params The variables to be bound to the parameters
select The columns to be selected

There are also properties for grouping and aggregating results, discussed elsewhere in the book.

As an easy example to begin, the same findByPk() query can be accomplished in this manner:

$criteria = new CDbCriteria();
$criteria->condition = 'id=:id';
$criteria->params = array(':id'=>$id);
$model = Page::model()->find($criteria);

To perform the same query using find() without formally creating a CDbCriteria object, just pass find() an array equivalent to what you would do with CDbCriteria:

$model = Page::model()->find(array(
    'condition' => 'id=:id',
    'params' => array(':id'=>$id)
));

{NOTE} When you’re finding a record using the primary key, it makes the most sense to use the findByPk() method. These other examples are for simple, comparative demonstrations.

As another example, this code might be used as part of the login process:

$criteria = new CDbCriteria();
$criteria->select = 'id, username';
$criteria->condition = 'email=:email AND pass=:pass';
$criteria->params = array(':email'=>$email, ':pass'=>$pass);
$model = User::model()->find($criteria);

Retrieving Multiple Records

The find() method only ever returns a single row (at most). If multiple rows should be returned by a query, use findAll() instead. Its signature is the same:

The class specification for the findAll() method.

The class specification for the findAll() method.

The findAll() method will return an array of objects, if one or more records match. If no records match, findAll() returns an empty array. This differs from find(), which returns NULL if no match was found.

Counting Records

Sometimes, you don’t actually need to return rows of data, but just determine how many rows apply to the given criteria. If you just want to see how many rows would be found by a query, use Active Record’s count() method. It takes the criteria as the first argument and parameters as the second, just like find():

// Find the number of "live" pages:
$criteria = new CDbCriteria();
$criteria->condition = 'live=1';
$count = Page::model()->count($criteria);

This is equivalent to running a SELECT COUNT(*) FROM page WHERE live=1 query and fetching the result into a number.

If you don’t care how many rows would be returned, but just want to confirm that at least one would be, use the exists() method:

$criteria = new CDbCriteria();
$criteria->condition = 'email=:email';
$criteria->params = array(':email'=>$email);
if (User::model()->exists($criteria)) {
    $message = 'That email address has already been registered.';
} else {
    $message = 'That email address is available.';
}
If you enjoyed this post, then please consider following me using your favorite social media, the RSS feed, and/or by subscribing to my newsletter. Or go crazy, and buy one or more of my books . Thanks!

3 responses to Using CDbCriteria in the Yii Framework

  1. Instead of using the condition function, you can also use the compare function like $criteria->compare(‘email’, $email, true); And you can also combine using the CDbCriteria class with the CActiveDataProvider class. This combination is used for example when creating a $dataProvider for a grid view widget.

  2. Hi, there is something important when use CDbCriteria with addBetweenCondition or addInCondition then you use the $criteria->params variable, if you overwrite the params var, all parameters for the conditions above will be erased, for avoid this, use CMap::mergeArray, you can add many parameters and mantain all parameters from automatic conditions

Comments are great, but I'd strongly prefer any requests for assistance get made in the support forums. Thanks!