Yii - How to print SQL used by findAll

Question

I have the following code to get some records from db

    $criteria = new CDbCriteria();
    $criteria->condition = 't.date BETWEEN "'.$from_date.'" AND "'.$to_date.'"';
    $criteria->with = array('order');

    $orders = ProductOrder::model()->findAll($criteria);

Is it possible to get the SQL that is used by the findAll? I know you can get it from the debug console. But I'm running the script in the background using yiic.php

Answer 1

You can log the executed queries in the application log and review that. Something like this in the config file:

'components' => array(
  'db'=>array(
    'enableParamLogging' => true,
  ),
  'log'=>array(
    'class'=>'CLogRouter',
    'routes'=>array( 
      array(
        'class'=>'CFileLogRoute',
        'levels'=>'trace,log',
        'categories' => 'system.db.CDbCommand',
        'logFile' => 'db.log',
      ), 
    ),
  ),
);

In some cases (e.g. when running tests), you will also need to call Yii::app()->log->processLogs(null); at the end of the process for this to work.

Of course, once you're there nothing's stopping you from writing your own log route that does something different with the logged messages, but mind that the logs are processed at the end of the request (or when you call processLogs), not every time you log something.

---

By the way, you should not build queries like that, with dynamic input right in the query. Use bind variables instead:

$criteria = new CDbCriteria();
$criteria->condition = 't.date BETWEEN :from_date AND :to_date';
$criteria->params = array(
  ':from_date' => $from_date,
  ':to_date' => $to_date,
);
$criteria->with = array('order');

$orders = ProductOrder::model()->findAll($criteria);