使用 ActiveRecord 和 Yii2 记录实际的 SQL 查询?

问题描述

我正在这样做:

$students = Student::find()->all();
    return $this->render('process', array('students' => $students));

然后在视图中显示:

foreach($students as $student)
    {
        echo $student->name . ',  ';
        echo $student->getQuizActivitiesCount(); ?> <br /> <?php
    }

我想看到正在执行的 sql 查询.一个学生有很多"测验活动，查询执行得很好，但我需要查看原始 SQL.这可能吗?

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?

推荐答案

方法一

使用返回 yiidbActiveQuery 实例的关系，可以直接在代码中提取原始 SQL 查询，例如使用 var_dump().

With relations that return yiidbActiveQuery instance it's possible to extract the raw SQL query directly in code for example with var_dump().

例如，如果我们有 user 关系:

For example if we have user relation:

/**
 * @return yiidbActiveQuery
 */
public function getUser()
{
    return $this->hasOne(User::className(), ['id' => 'user_id']);
}

然后你可以像这样var_dump()原始SQL:

You can then var_dump() the raw SQL like that:

var_dump($model->getUser()->prepare(Yii::$app->db->queryBuilder)->createCommand()->rawSql);
exit();

请注意，您应该这样调用它，而不是 $model->user->...(后者返回 User 实例).

Note that you should call it like that and not $model->user->... (the latter returns User instance).

但在您的情况下这是不可能的，因为 count() 立即返回 int.可以var_dump()部分查询，不用count()，但我觉得不方便.

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.

请注意，您可以使用此方法转储任何 ActiveQuery 实例(不仅是关系返回的实例)的生成 SQL，例如:

Note that you can use this method for dumping generated SQL of any ActiveQuery instances (not only those that were returned by relation), for example:

$query = User::find()->where(['status' => User::STATUS_ACTIVE]);
var_dump($query->prepare(Yii::$app->db->queryBuilder)->createCommand()->rawSql);
exit();

方法二

在我看来这要简单得多，我个人在调试 SQL 查询时更喜欢这个.

This is much simpler in my opinion and I personally prefer this one when debugging SQL queries.

Yii 2 有内置的调试模块.只需将此添加到您的配置中:

Yii 2 has built-in debug module. Just add this to your config:

'modules' => [
    'debug' => [
        'class' => 'yiidebugModule',
    ],
],

确保你只在本地拥有它，而不是在生产中.如果需要，还可以更改 allowedIPs 属性.

Make sure you only have it locally and not on production. If needed, also change allowedIPs property.

这会在页面底部为您提供功能面板.找到 DB 词并单击计数或时间.在此页面上，您可以查看所有已执行的查询并对其进行过滤.我通常不会在 Grid 中过滤它们，而是使用标准浏览器搜索来快速导航并找到必要的查询(例如使用表名作为关键字).

This gives you functional panel at the bottom of the page. Find the DB word 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).

方法三

只需在查询中出错，例如列名 - cityy 而不是 city.这将导致数据库异常，然后您可以立即在错误消息中看到生成的查询.

Just make an error in query, for example in column name - cityy instead of city. This will result as database exception and then you can instantly see the generated query in error message.

这篇关于使用 ActiveRecord 和 Yii2 记录实际的 SQL 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！