php Yii - 如何打印 findAll 使用的 SQL

Yii - How to print SQL used by findAll

提问by Adrian Gunawan

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

是否可以获取 findAll 使用的 SQL？我知道您可以从调试控制台获取它。但我在后台使用 yiic.php 运行脚本

回答by DCoder

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.

在某些情况下（例如在运行测试时），您还需要Yii::app()->log->processLogs(null);在流程结束时调用以使其工作。

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.

当然，一旦你到达那里，就没有什么能阻止你编写自己的日志路由，它对记录的消息做一些不同的事情，但请注意，日志是在请求结束时（或调用时processLogs）处理的，而不是每次你记录一些东西。

---

---

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

回答by Mohammad Eghlima

• First way(Official way):
  In your main.phpconfig file add these two parameters in your log sectionand you can see log messages at the end of your page or FireBug Consolein your browser. do not forget to set necessary parameters in dbsection.

  '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_DEBUGmode true). something like this:
  (I have changed t.dateto t.wrong_date, when you refresh your page, you will see the generated SQL which was executed in your database)

• 第一种方式（官方方式）：
  在您的main.php配置文件中添加这两个参数，您log section可以在页面末尾或FireBug Console浏览器中看到日志消息。不要忘记在db节中设置必要的参数。

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

• 第二种方式：
  在您的代码中，只需将其中一列的拼写更改为不正确的内容，您将在错误页面中收到一条包含完整 SQL 查询的错误消息（您应该处于YII_DEBUGtrue 模式）。像这样：（
  我已更改t.date为t.wrong_date，当您刷新页面时，您将看到在您的数据库中执行的生成的 SQL）

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

$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_DEBUGtrue in index.php

在这两种方式中，都为YII_DEBUG真index.php

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

回答by Ilya Malyutin

You can get sql by using CDbCommandBuilder, like this:

您可以使用 CDbCommandBuilder 获取 sql，如下所示：

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

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

回答by Arth

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

如果您不想在看到 SQL 之前执行查询，这实际上并不像您希望的那样容易。

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.

它与错误一样肮脏，但是，仅在开发中时，我过去曾在标准中故意添加一个故意错误，并依靠由此产生的异常来尝试 SQL。

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

我发现 Ilya 的方法不可靠（不知道为什么，但有时使用这种方法会忽略标准）。

回答by gSorry

You can see log directly on your page:

您可以直接在您的页面上查看日志：

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