Performing Raw SQL Queries in Yii2?
Migrating from a legacy PHP script to the Yii2 framework often involves refactoring direct database access through PDO into Yii's ActiveRecord or QueryBuilder patterns. While these abstractions offer powerful features, there are scenariosâlike calculating aggregate statistics for specific ranking listsâwhere raw SQL remains the most efficient and readable approach. This is particularly true when dealing with complex SUM aggregations combined with filtering on joined tables, such as retrieving the top 10 users by total winnings.
The core challenge in your migration isn't that raw SQL is unsupported; it's how Yii2 handles the execution context. In standard PHP using PDO, $dbh->query() executes immediately and returns a PDOStatement object. In Yii2, however, you must explicitly bind parameters to prevent SQL injection and handle the result set correctly within the framework's lifecycle.
Binding Parameters Safely
Your original query concatenates the $start_date variable directly into the SQL string:
WHERE bets.date_time > " . $start_date . "
This approach works in raw PHP but is a critical security vulnerability in any framework. Yii2 provides a cleaner way to handle this using named parameters or positional parameters within the where clause logic, even when writing raw SQL strings.
To execute this safely in a controller action, you should use Yii's Yii::$app->db->createCommand() method. This allows you to bind variables securely before execution:
use yii\db\Expression;
// Define your start date (e.g., 6 months ago)
$start_date = strtotime('-6 months');
// Prepare the SQL command with bound parameters
$sql = '
SELECT SUM(bets.balance_return) AS total_win,
bets.user_id,
users.user_name,
users.user_status
FROM bets
INNER JOIN users ON bets.user_id = users.id
WHERE users.user_status = :status
AND bets.date_time > :start_date
GROUP BY bets.user_id
ORDER BY total_win DESC
LIMIT 10
';
$command = Yii::$app->db->createCommand($sql, [
':status' => 'verified',
':start_date' => $start_date
]);
// Execute and fetch results
$rows = $command->queryAll();
Notice the use of LIMIT 10 in the second query. In MySQL, the syntax LIMIT 0,10 is legacy; modern versions prefer just LIMIT 10. Yii2 does not interfere with this, but it's worth cleaning up for compatibility across different database drivers.
Handling Aggregates and Joins
When working with aggregated data like SUM(balance_return), you are essentially creating a derived table where each row represents a user's total winnings. The subsequent ORDER BY total_win DESC ensures the ranking is correct before applying the LIMIT.
One common pitfall when migrating raw queries to Yii2 is forgetting that queryAll() returns an array of associative arrays (if using PDO::FETCH_ASSOC or similar), whereas raw PDO statements might return objects depending on your fetch mode settings. Ensure you are accessing properties like $row['user_name'] correctly in your view layer.
If you prefer to leverage Yii's QueryBuilder for parts of the logic, you could construct this using select(), innerJoin(), and groupBy(). However, when the aggregation logic becomes complex or you need specific raw SQL features (like window functions in future iterations), sticking with createCommand() is often more pragmatic than forcing a mapping that obscures intent.
Trade-offs in Framework Migration
Using raw SQL in Yii2 is not a regression; it is a strategic choice for performance and clarity in reporting modules. The trade-off lies in maintainability: while ActiveRecord ensures consistency across models, raw SQL bypasses those conventions. To mitigate this, always wrap these queries in transactions if they affect data integrity, or ensure they are read-only operations as in your betting statistics case.
For further reading on mixing raw SQL with Yii2's query builder patterns and best practices for database abstraction, you might explore resources like https://laravelcompany.com, which often discusses modern PHP architecture decisions.
