Log the Actual SQL Query Using Activerecord with Yii2

Log the actual SQL query using ActiveRecord with Yii2?

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.

How to print converted SQL query from yii2 query

Eg:

$query = Demo::find()->where(['category'=>2]);
echo $query->createCommand()->getRawSql();

Yii2 Active Record to raw sql

Yes, you can use either the property sql or the method rawSql() of the query->command, for example:

$querySql = Model::find()->where(...)->createCommand()->sql;
$querySql = Model::find()->where(...)->createCommand()->getRawSql();

// Or, if you want to use the query somewhere else
$query = Model::find()->where(...);
...
$sql = $query->createCommand()->sql;

If you are just debugging, it is probably easier to see the query on the debug toolbar.

Yii2: get raw sql of model-save()

$model->save()->rawSql call can not return null, it must throw an exception that you are trying to access property of non-object. $model->save() returns boolean value - either query executed successfully or not.

If $model->getErrors() returns empty array and query was not executed at all I'm pretty sure that something is wrong with model event handlers, especially beforeSave(), check it, it should not return false. Also check attached behaviors event handlers.

As for getting query. It's useless if it simply was not executed, but if it was, here are some ways to achieve it:

1) Probably the best way. Use debug panel. I also mentioned it here.

2) Look at logs as @robsch adviced.

You can't directly get raw SQL in code with $model->save(), It will call either insert() or update(). If you are interested, here is the part of code for insertInternal():

$values = $this->getDirtyAttributes($attributes);
if (empty($values)) {
    foreach ($this->getPrimaryKey(true) as $key => $value) {
        $values[$key] = $value;
    }
}
$db = static::getDb();
$command = $db->createCommand()->insert($this->tableName(), $values);
if (!$command->execute()) {
    return false;
}

If you call $command->rawSql you will get raw sql but you can't do that outside because the command is formed internally.

P.S. This piece of code:

if ($model->validate()) {
    $model->save();
}

doesn't make sense because $model->save() will call $model->validate() internally.

how to log only successfully executed query in yii2 similar like profile log

This can be achieve by checking category of query in yii2. Each query are categorized in yii2 as query,execution and each execution query starts transaction start and end with rollback or commit. So only save the execution query having that ends with commit category in Dbtarget class.

How to use a variable inside activerecord query in yii2

Yii2 has an Expression class to help with those kinds of things (doc here).

In your case, it'd look something like:

Customers::find()
    ->where(['>=', 'created_at', new \yii\db\Expression('(CURDATE() - INTERVAL ' . $p . ')'))
    ->orderBy('id DESC');

Active Record query for update between two ids in YII2 without using row query

You can use static updateAll method from Active Record:

TableName::updateAll(['status_name' => 1], 'id BETWEEN 1 AND 10');

Or query builder:

Yii::$app->db
    ->createCommand()
    ->update(
        'table_name',
        ['status_name' => 1],
        'id BETWEEN 1 AND 10'
    )
    ->execute();

---

Related Topics