Logging Actual SQL Queries in Yii2 ActiveRecord
When debugging a complex relationship like Student having many QuizActivity, seeing the final rendered output isn't enough. You need to inspect the raw SQL generated by the framework to understand how joins are constructed, which tables are being accessed, and why performance might be degrading. In your specific scenario, the query fetches all students and then calls getQuizActivitiesCount() for each one in the view. While this works functionally, it often triggers an N+1 query problem if not handled correctly, and without visibility into the SQL, diagnosing execution plans becomes a guesswork exercise.
Yii2 provides a built-in mechanism to log these queries directly to your application output or database logs without requiring external libraries. The core feature you are looking for is the debug profile handler or the native logging support available in the framework's bootstrap process. To see the SQL, you must ensure that Yii2 is configured to log query events to a destination you can inspect.
The most straightforward approach involves configuring the application bootstrap or checking your config/web.php. When using the default console or web applications, Yii2 automatically registers debug tools if you enable them in your environment configuration. Specifically, you need to look at the components section where you define the logger and the query loggers.
Add a QueryLogger component to your application configuration:
'components' => [
// ... other components
'db' => [
'class' => \yii\db\Connection::class,
'dsn' => 'mysql:host=localhost;dbname=yii2advanced',
'username' => 'root',
'password' => '',
'charset' => 'utf8',
// Enable logging for this specific database connection
'enableQueryLog' => true,
],
]
Once enableQueryLog is set to true, you can access the query log within your request lifecycle. However, simply enabling it doesn't automatically print the SQL to the screen during development unless you have a debug toolbar or logger configured to output those messages.
For immediate visibility in a browser environment without setting up a full debug toolbar, you can extend the Yii2 Application class or use a simple event handler to dump the log after the request completes. Here is how you can manually inspect the logs programmatically:
use yii\base\Application;
class MyApplication extends Application
{
public function shutdown()
{
$this->end(); // Continue normal shutdown
// Access the query log from your database component
/** @var \yii\db\Connection $db */
$db = $this->get('db');
if ($db && isset($db->queryLog)) {
echo "<pre>";
foreach ($db->queryLog as $log) {
echo "Query: " . htmlspecialchars($log['sql']) . "\n";
echo "Time: " . $log['duration'] . " ms\n";
echo "---\n";
}
echo "</pre>";
}
}
}
This approach captures the SQL executed for your Student::find()->all() and subsequently for every getQuizActivitiesCount(). If you notice that a new query is generated inside the loop in your view, you have confirmed an N+1 issue. The raw SQL will likely show a repeated join or subquery structure for each student record, which is inefficient.
Alternatively, if you are using Yii2's debug toolbar (often included via yii-debug-toolbar), you can simply enable it in your config and click the "Query Log" tab in the browser. This provides an interactive way to inspect the SQL without writing custom shutdown logic. The toolbar aggregates all queries executed during the current request, showing the raw SQL, execution time, and parameters.
A common pitfall when analyzing these logs is assuming that the displayed SQL represents the final optimized query plan. While enableQueryLog shows the exact string sent to the database, it does not show the internal index usage or execution plan unless you explicitly run EXPLAIN. To get deeper insights, you can wrap your queries in a profiling block or use the built-in profiler events if you are running in a development environment with Xdebug enabled.
If you prefer a more automated solution for production monitoring, consider integrating a query logger that writes to a dedicated log file rather than memory. This ensures that even long-running scripts do not exhaust server memory by holding massive arrays of queries. You can configure the Logger component to write these specific debug messages to a file with a specific level, such as DEBUG.
'components' => [
'db' => [
// ... connection settings
'enableQueryLog' => true,
],
'log' => [
'class' => \yii\log\Logger::class,
'targets' => [
[
'class' => \yii\log\FileTarget::class,
'levels' => ['error', 'warning'],
// Add a custom target for query logs if needed
],
],
],
],
By enabling query logging and inspecting the raw SQL, you gain the visibility necessary to refactor your codebase. In your case, moving the count calculation into an eager loading relationship using with() will reduce multiple queries to a single optimized join, which the logged SQL will immediately reveal as more efficient.