Log the actual SQL query using ActiveRecord with Yii2?

by

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.

More Related Contents:

  1. How to Update one column (point) in php sql [duplicate]
  2. How can create Database using url or other method [duplicate]
  3. Problame with files on SQL
  4. Im trying to create some temporary tables in mysql using php
  5. How can I prevent SQL injection in PHP?
  6. SQL injection that gets around mysql_real_escape_string()
  7. How to include a PHP variable inside a MySQL statement
  8. How to apply bindValue method in LIMIT clause?
  9. Use global variables in a class
  10. Examples of SQL Injections through addslashes()?
  11. Achieve hierarchy, Parent/Child Relationship in an effective and easy way
  12. How to debug PDO database queries?
  13. How to search for slash (\) in MySQL? and why escaping (\) not required for where (=) but for Like is required?
  14. How to create Codeigniter batch insert array
  15. MySQLi prepared statements with IN operator [duplicate]
  16. php return 500 error but no error log
  17. MYSQL Select from tables based on multiple rows
  18. How to print SQL statement in codeigniter model
  19. How to execute raw queries with Laravel 5.1?
  20. How to populate HTML dropdown list with values from database
  21. How do you do multiple SQL statements in one mysql_query?
  22. Yii2 disable Bootstrap Js, JQuery and CSS
  23. MySQL/SQL retrieve first 40 characters of a text field?
  24. How to get last insert Id in SQLite?
  25. Get orders total purchases amount for the day in Woocommerce
  26. how do I get month from date in mysql
  27. php/mysql zip code proximity search
  28. Php & Sql Injection – UTF8 POC
  29. Escaping SQL queries in Codeigniter
  30. Aggregated query without GROUP BY

Leave a Comment