php 使用 ActiveRecord 和 Yii2 记录实际的 SQL 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27389146/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Log the actual SQL query using ActiveRecord with Yii2?
提问by diabetesjones
I'm doing this:
我这样做：
$students = Student::find()->all();
return $this->render('process', array('students' => $students));
and then this in the view:
然后在视图中：
foreach($students as $student)
{
echo $student->name . ', ';
echo $student->getQuizActivitiesCount(); ?> <br /> <?php
}
i would like to see the sql query being performed. a student "has many" quiz activities, and the query performs perfectly, but i need to see the raw SQL. is this possible?
我想看到正在执行的 sql 查询。一个学生“有很多”测验活动，并且查询执行得很好，但我需要查看原始 SQL。这可能吗？
回答by arogachev
Method 1
方法一
With relations that return yii\db\ActiveQueryinstance it's possible to extract the raw SQL query directly in code for example with var_dump().
使用返回yii\db\ActiveQuery实例的关系，可以直接在代码中提取原始 SQL 查询，例如使用var_dump().
For example if we have userrelation:
例如，如果我们有user关系：
/**
* @return \yii\db\ActiveQuery
*/
public function getUser()
{
return $this->hasOne(User::className(), ['id' => 'user_id']);
}
You can then var_dump()the raw SQL like that:
然后var_dump()，您可以像这样使用原始 SQL：
var_dump($model->getUser()->prepare(Yii::$app->db->queryBuilder)->createCommand()->rawSql);
exit();
Note that you should call it like that and not $model->user->...(the latter returns Userinstance).
请注意，您应该这样称呼它而不是$model->user->...（后者返回User实例）。
But in your case it's not possible because count()immediately returns int. You can var_dump()partial query without count(), but I think it's not convenient.
但是在您的情况下这是不可能的，因为count()立即返回int. 您可以var_dump()不使用进行部分查询count()，但我认为这不方便。
Note that you can use this method for dumping generated SQL of any ActiveQueryinstances (not only those that were returned by relation), for example:
请注意，您可以使用此方法转储任何ActiveQuery实例（不仅是关系返回的实例）的生成 SQL ，例如：
$query = User::find()->where(['status' => User::STATUS_ACTIVE]);
var_dump($query->prepare(Yii::$app->db->queryBuilder)->createCommand()->rawSql);
exit();
Method 2
方法二
This is much simpler in my opinion and I personally prefer this one when debugging SQL queries.
这在我看来要简单得多，我个人在调试 SQL 查询时更喜欢这个。
Yii 2 has built-in debug module. Just add this to your config:
Yii 2 有内置的调试模块。只需将此添加到您的配置中：
'modules' => [
'debug' => [
'class' => 'yii\debug\Module',
],
],
Make sure you only have it locally and not on production. If needed, also change allowedIPsproperty.
确保你只在本地拥有它，而不是在生产中。如果需要，还可以更改allowedIPs属性。
This gives you functional panel at the bottom of the page. Find the DBword and click on either count or time. On this page you can view all executed queries and filter them.
I usually don't filter them in Grid and use standard browser search to quickly navigate through and find the necessary query (using the table name as keyword for example).
这为您提供了页面底部的功能面板。找到DB单词并单击计数或时间。在此页面上，您可以查看所有已执行的查询并对其进行过滤。我通常不会在 Grid 中过滤它们，而是使用标准浏览器搜索来快速导航并找到必要的查询（例如使用表名作为关键字）。
Method 3
方法三
Just make an error in query, for example in column name - cityyinstead of city. This will result as database exception and then you can instantly see the generated query in error message.
只需在查询中出错，例如在列名中 -cityy而不是city. 这将导致数据库异常，然后您可以立即在错误消息中看到生成的查询。
回答by ezze
If you want to log all relational queries of ActiveRecordin console application all proposed methods doesn't help. They show only main SQL on active record's table, \yii\debug\Moduleworks only in browser.
如果您想ActiveRecord在控制台应用程序中记录所有关系查询，所有建议的方法都无济于事。它们仅显示活动记录表上的主要 SQL，\yii\debug\Module仅在浏览器中有效。
Alternative method to get all executed SQL queries is to log them by adding specific FileTargetto configuration:
获取所有执行的 SQL 查询的替代方法是通过将特定的FileTarget添加到配置来记录它们：
'log' => [
'targets' => [[
...
], [
'class' => 'yii\log\FileTarget',
'logFile' => '@runtime/logs/profile.log',
'logVars' => [],
'levels' => ['profile'],
'categories' => ['yii\db\Command::query'],
'prefix' => function($message) {
return '';
}
]]
]
UPDATE
更新
In order to log insert/update/delete queries one should also add yii\db\Command::executecategory:
为了记录插入/更新/删除查询，还应该添加yii\db\Command::execute类别：
'categories' => ['yii\db\Command::query', 'yii\db\Command::execute']
回答by Stack user
you can try this, assume you have a query given like:
你可以试试这个，假设你有一个这样的查询：
$query = new Books::find()->where('author=2');
echo $query->createCommand()->sql;
or to get the SQL with all parameters included try:
或获取包含所有参数的 SQL 尝试：
$query->createCommand()->getRawSql()
回答by Mat
In addition to arogachev answer, when you already work with an ActiveQueryobject, here is the line I search to view the rawsql.
除了 arogachev 答案，当您已经使用ActiveQuery对象时，这是我搜索以查看 rawsql 的行。
/* @var $studentQuery ActiveQuery */
$studentQuery = Student::Find();
// Construct the query as you want it
$studentQuery->where("status=3")->orderBy("grade ASC");
// Get the rawsql
var_dump($studentQuery->prepare(Yii::$app->db->queryBuilder)->createCommand()->rawSql);
// Run the query
$studentQuery->all();
回答by ChrisB
when you have a query object you can also use
当你有一个查询对象时，你也可以使用
$query->createCommand()->getRawSql()
to return the Raw SQL with the parameters included or
返回包含参数的原始 SQL 或
$query->createCommand()->sql
which will output the Sql with parameters separately.
这将分别输出带有参数的Sql。
回答by Ali MasudianPour
In order to log/track every/allqueries:
为了记录/跟踪每个/所有查询：
extend \yii\db\Connectionand override createCommandmethod, like below:
扩展\yii\db\Connection和覆盖createCommand方法，如下所示：
namespace app\base;
class Connection extends \yii\db\Connection {
public function createCommand($sql = null, $params = array()) {
$createCommand = parent::createCommand($sql, $params);
$rawSql = $createCommand->getRawSql();
// ########### $rawSql -> LOG IT / OR DO ANYTHING YOU WANT WITH IT
return $createCommand;
}
}
Then, simply change your db connection in your db config like below:
然后，只需在您的 db 配置中更改您的 db 连接，如下所示：
'db' => [
'class' => 'app\base\Connection', // #### HERE
'dsn' => 'pgsql:host=localhost;dbname=dbname',
'username' => 'uname',
'password' => 'pwd',
'charset' => 'utf8',
],
Now, you can track/read/... all queries executed by dbconnection.
现在，您可以跟踪/读取/...db连接执行的所有查询。
回答by Adil Abbasi
Try like,
尝试像，
$query = Yii::$app->db->createCommand()
->update('table_name', ['title' => 'MyTitle'],['id' => '1']);
var_dump($query->getRawSql()); die();
$query->execute();
Output:
输出：
string 'UPDATE `table_name`
SET `title`='MyTitle' WHERE `id`='1'
' (length=204)
