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

• First way(Official way):
  In your main.php config file add these two parameters in your log section and you can see log messages at the end of your page or FireBug Console in your browser. do not forget to set necessary parameters in db section.

  'components' => array( 'db'=>array( 'enableProfiling'=>true, 'enableParamLogging' => true, ), 'log'=>array( 'class'=>'CLogRouter', 'routes'=>array( array( 'class'=>'CWebLogRoute', 'showInFireBug' => true, ), array( 'class'=>'CProfileLogRoute', 'levels'=>'profile', 'enabled'=>true, ), ), ), );

• Second way:
  In your code just change the spelling of one of your columns to something incorrect and you will get an error message contains full SQL query in your error page(you should be in YII_DEBUG mode true). something like this:
  (I have changed t.date to t.wrong_date, when you refresh your page, you will see the generated SQL which was executed in your database)

$criteria = new CDbCriteria(); $criteria->condition = 't.wrong_date BETWEEN "'.$from_date.'" AND "'.$to_date.'"'; $criteria->with = array('order'); $orders = ProductOrder::model()->findAll($criteria);

in the both ways, have YII_DEBUG true in index.php

defined('YII_DEBUG') or define('YII_DEBUG',true);

Answer 2

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);

Answer 3

You can see log directly on your page:

'log'=>array(
    'class'=>'CLogRouter',
    'routes'=>array(
        array(
            'class'=>'CWebLogRoute',
        ),
    ),
),

Answer 4

If you don't want to execute the query before seeing the SQL, this isn't actually as easy as you might hope.

It's as dirty as wrong but, when in development only, I have in the past taken to adding a deliberate a deliberate error in the criteria and relying on the resulting Exception to give the SQL attempted.

e.g.

$criteria = new CDbCriteria();
$criteria->condition = 't.date_fgjhfgjfgj 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);

I have found Ilya's method to be unreliable (don't know why, but sometimes the criteria is ignored using this method).

Answer 5

You can get sql by using CDbCommandBuilder, like this:

ModelClassName::model()-> getCommandBuilder()-> createFindCommand('tableName', $criteria)->text;