Yii2 Query Builder vs Active Record Reports

When to use Query Builder versus Active Record in Yii2 for reporting queries, with a comparison table, trade-offs, and a monthly sales report example.

Yii2 Reporting Strategy: Active Record vs Query Builder

When building reports in Yii2, the choice between Active Record and Query Builder often comes down to the complexity of the data retrieval. For simple create-read-update-delete operations, Active Record provides rapid development with built-in hydration and validation. However, when reports require multiple joins, aggregations, or dynamic filtering, Active Record can generate inefficient SQL and hide performance bottlenecks. The decision must balance development speed against query performance and maintainability.

Constraints for this choice include: working within Yii2 2.0+, keeping code readable, avoiding N+1 query patterns, and ensuring the solution remains testable across environments.

\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n

Aspect	Active Record	Query Builder
Setup	Model class, automatic hydration	DB connection, manual row mapping
Performance	Slight overhead from hydration	Lower overhead, direct SQL
Flexibility	Limited to defined relations and scopes	Full SQL control (joins, unions, sub-queries)
Maintenance	Encapsulates business logic	SQL scattered unless abstracted

Trade-offs

Active Record accelerates development. You get automatic validation, easy relation traversal, and hydration that converts rows into objects. The trade-off is that the framework abstracts SQL, making it harder to tune complex reports. Query Builder puts you in control of every clause. You can write precise joins, GROUP BY, HAVING, and UNIONs. The cost is more boilerplate: you manually map rows to data structures, and you must guard against SQL injection by using parameterized placeholders. For teams comfortable with SQL, QB offers predictable performance; for teams prioritizing rapid feature delivery, AR may suffice when reports are simple.

Concrete Implementation: Monthly Sales Report with Query Builder

The following example builds a monthly sales summary, grouping by product category and filtering by date range. It uses Yii2's query builder with parameterized inputs to remain safe and performant.

\nselect(['category', 'SUM(amount) as total_sales'])\n    ->from('order')\n    ->join('INNER JOIN', 'product', 'product.id = order.product_id')\n    ->where(['>=', 'order.created_at', ':start_date'])\n    ->andWhere(['<=', 'order.created_at', ':end_date'])\n    ->groupBy('category')\n    ->params([':start_date' => '2024-01-01', ':end_date' => '2024-01-31'])\n    ->all();\n?>>

Validating the Result with Yii Model Validation

To ensure the returned data conforms to expected types before further processing, you can map the rows into a model and call its validate() method. Suppose you have a simple report model:

\nclass SalesReport extends yii/base/Model\n{\n    public ?string $category = null;\n    public ?float $total_sales = null;\n\n    public function rules(): array\n    {\n        return [\n            [['category', 'total_sales'], 'string', 'except' => [total_sales]],\n            [['total_sales'], 'number'],\n        ];\n    }\n}\n

After fetching the report rows, you can validate each row:

\n$validated = [];\nforeach ($report as $row) {\n    $model = new SalesReport([\n        'category' => $row['category'],\n        'total_sales' => (float) $row['total_sales'],\n    ]);\n    if ($model->validate()) {\n        $validated[] = $model->attributes;\n    }\n}\n

Verification and Practical Checks

Run the Query Builder snippet against a test database (e.g., SQLite in memory) and confirm the generated SQL includes the expected SELECT, WHERE with placeholders, and GROUP BY clause.
Pass the returned data array through the SalesReport::validate() method; successful validation means the row keys match the model's declared attributes.
Use Yii's debug panel (run with YII_DEBUG true) to measure execution time. Compare the Query Builder version against an equivalent Active Record query with eager loading for the same date range and grouping. In typical scenarios, the QB version shows lower request-time overhead because it avoids hydration of unnecessary attributes.
Always inspect the produced SQL in the debug toolbar when user inputs are involved; verify that placeholders are used and no string concatenation occurs.

Cautions

Avoid mixing Active Record and Query Builder in the same service layer without clear separation, as it can lead to inconsistent state and harder debugging.
Remember to parameterize all user-input values in Query Builder. Yii's query builder does this automatically when using placeholders, but manual string concatenation bypasses safety and risks SQL injection.

Yii2 Active Record vs Query Builder for Reports