Getting the string representation from CDbCriteria

Question

Is there any way to the get the string representation of the query from CDbCriteria? For testing and debugging purposes.

Answer 1

You can use logging and profiling configuring your main.php like this:

'components'=>array(
    'log'=>array(
        'class'=>'CLogRouter',
        'routes'=>array(
            array(
                'class'=>'CWebLogRoute',
                'categories'=>'system.db.CDbCommand',
                'showInFireBug'=>true,
            ),
        ),
    ),
    'db'=>array(
        'enableProfiling'=>true,
        'enableParamLogging'=>true,
    ),              
),

Answer 2

I spend a lot amount of time for finding the answer to this question, so thought of sharing it with you guys. Hope this saves your precious time.

As mentioned by Jon above: CDbCriteria does not aggregate enough information to construct the full query, you have to use the model class information also on which you will put the query constraints.

As the example given in Yii docs for CDbCriteria; this is how basically you use it-

$criteria=new CDbCriteria(); 
$criteria->compare('status',Post::STATUS_ACTIVE); 
$criteria->addInCondition('id',array(1,2,3,4,5,6)); 

$posts = Post::model()->findAll($criteria);

Here Post is the name of the model on which you execute the query condition.

So if you want to get the text representation of the query written in CDbCriteria, you have to involve the model information also i.e. Post.

This is how you can do it -

$model = new Post();
$query = $model->getCommandBuilder()->createFindCommand($model->getTableSchema(), $criteria)->getText();

When you print the value in $query variable it prints the raw query.

Hope this helps.