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

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

Solution 2

You can get sql by using CDbCommandBuilder, like this:

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

Solution 3

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

Solution 4

You can see log directly on your page:

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

Solution 5

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

View more solutions

Comments

[itachi]

doesn't it seems DAO with direct sql command is much simpler/faster than using CDbcriteria?

[DCoder]

Depends on what you want to do with the result, I suppose. If you want to use the model functionality (e.g. validation), using AR is an obvious choice. Personally, I prefer AR and leave DAO only for more complex situations like update involving a join. This way I can use model scopes for common conditions and not have to manually review/update all direct commands when the schema or business logic changes. (Plus, using AR everywhere means a consistent way to access data contained in the results, without having to worry if the result row is an object or an array...)

[Motin]

This is a good solution for simple queries, but it does not consider "with" which was stated in the OP's example

[Xcoder]

I always use the second one