Log the actual SQL query using ActiveRecord with Yii2?
PhpSqlActiverecordLoggingYii2Php Problem Overview
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?
Php Solutions
Solution 1 - Php
Method 1
With relations that return yii\db\ActiveQuery
instance it's possible to extract the raw SQL query directly in code for example with var_dump()
.
For example if we have user
relation:
/**
* @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($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 User
instance).
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.
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();
Method 2
This is much simpler in my opinion and I personally prefer this one when debugging SQL queries.
Yii 2 has built-in debug module. Just add this to your config:
'modules' => [
'debug' => [
'class' => 'yii\debug\Module',
],
],
Make sure you only have it locally and not on production. If needed, also change allowedIPs
property.
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).
Method 3
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.
Solution 2 - Php
If you want to log all relational queries of ActiveRecord
in console application all proposed methods don't help. They show only main SQL on active record's table, \yii\debug\Module
works only in browser.
Alternative method to get all executed SQL queries is to log them by adding specific FileTarget to configuration:
'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::execute
category:
'categories' => ['yii\db\Command::query', 'yii\db\Command::execute']
Solution 3 - Php
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:
$query->createCommand()->getRawSql()
Solution 4 - Php
In addition to arogachev answer, when you already work with an ActiveQuery
object, here is the line I search to view the 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();
Solution 5 - Php
when you have a query object you can also use
$query->createCommand()->getRawSql()
to return the Raw SQL with the parameters included or
$query->createCommand()->sql
which will output the Sql with parameters separately.
Solution 6 - Php
In order to log/track every/all queries:
extend \yii\db\Connection
and override createCommand
method, like below:
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' => [
'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 db
connection.
Solution 7 - Php
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)