Log the actual SQL query using ActiveRecord with Yii2?

Extracting raw SQL

In cases where you need to peek into the raw query to iron out issues or improve performance, Yii2's ActiveQuery gives you the power to dig deeper:

$query = User::find();  // Finding users
$sql = $query->createCommand()->getRawSql();  // Converting the pretzel to plain salt 😄
echo $sql;


By invokingcreateCommand and getRawSql methods, you can see the raw SQL string prepared for execution. This can be incredibly useful when debugging and refining complex queries.

Keep the genie in the bottle

When dealing with larger datasets and hefty queries, be extra cautious when using var_dump() or similar functions. Count()ing in the wrong place could unluckily summon all the query results, possibly resulting in a memory overload. Always extract only the SQL string in such instances.

Debug like a Ninja: Invisibly

Ensuring the debug module should only be enabled locally is important. Wielding it improperly can lead to performance issues and security vulnerability. Don't forget to control access by setting the allowedIPs parameter accordingly.

Fine-grained logging with Yii2

Query and Execute: Two Sides of the Coin

Using Yii2, you can also choose to monitor specific kinds of SQL actions. For picking up data (SELECT statements), log under the category 'yii\db\Command::query'. For altering data (INSERT, UPDATE, DELETE etc.), use 'yii\db\Command::execute'. Selective logging keeps the log clutter-free and more efficient:

'components' => [
    'log' => [
        // ...
        'targets' => [
            // more targets here...
            [
                'class' => 'yii\log\FileTarget',
                'levels' => ['info'],
                'categories' => ['yii\db\Command::query'],
                'logFile' => '@runtime/logs/query.log', // For your SELECTive memory!
            ],
            [
                'class' => 'yii\log\FileTarget',
                'levels' => ['info'],
                'categories' => ['yii\db\Command::execute'],
                'logFile' => '@runtime/logs/execute.log', // Execute Order 66! 😆
            ],
        ],
    ],
],


Forensic Logging: A route to optimization

Logging is more than just about badge hunting bugs—it's also a guided path to performance optimization. Regularly auditing your SQL logs can throw light on inefficient queries that could be refactored or indexed for significant performance gains. Understanding the under-the-hood activities provides you with an upper hand in sculpting a swift application.

Cracking the Code of ActiveRecord

One major benefit of logging is that it helps understand how ActiveRecord translates your commands into actual SQL. This learning can be a major milestone in writing more predictable and efficient queries.