Using CDbCriteria in the Yii Framework

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.

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.

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 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.';
}