Choosing Between ActiveRecord and Query Builder in Yii 2.x for Complex Database Work

The Problem: Silent N+1 Queries

In a typical Yii 2.x application you start with Order::find()->with('customer')->all() and everything feels fast. Add a loop that accesses $order->items and the debug toolbar suddenly shows dozens of extra SELECT * FROM item WHERE order_id = … statements. That is the classic N+1 problem: lazy loading fires a separate query for each parent row. The fix is not a framework switch — it is a deliberate choice between ActiveRecord’s eager‑loading helpers and the Query Builder’s explicit joins.

When ActiveRecord Shines

• Entity‑centric workflows — creating, updating, or deleting a single record where model events (beforeSave, afterFind) must run.
• Relation navigation — hasOne, hasMany, and via give you a readable object graph.
• Automatic schema sync — migrations keep the model’s attribute list in step; enable 'enableSchemaCache' => true and 'schemaCacheDuration' => 3600 in config/db.php to avoid a cache flush after every column change.

ActiveRecord’s with() and joinWith() solve most eager‑loading needs. Use joinWith() when you need to filter or sort by a related column in a single query.

When Query Builder Takes Over

• Reporting / analytics — wide result sets that never become model instances.
• Bulk updates or deletes — yii\db\Query::createCommand()->update(...)->execute() avoids loading thousands of objects.
• Dialect‑specific features — index hints, FOR UPDATE, window functions, or custom EXPLAIN plans are only reachable through the builder or raw SQL.

Query Builder does not fire ActiveRecord events, so any business logic living in afterFind or beforeSave will be skipped. Plan accordingly.

Worked Example: Orders with Customers and Items

ActiveRecord approach

// controllers/ReportController.php
public function actionOrdersAr()
{
    $orders = Order::find()
        ->with([
            'customer',
            'items.product' // nested eager loading
        ])
        ->where(['status' => Order::STATUS_SHIPPED])
        ->orderBy('created_at DESC')
        ->limit(100)
        ->all();

    // $orders is an array of Order models with relations populated
    return $this->render('orders', ['orders' => $orders]);
}

Run this in a development controller (requires yii\web\Controller context). The debug toolbar will show two queries: one for orders + customers (joined) and a second for items + products (joined via via).

Query Builder equivalent

use yii\db\Query;

public function actionOrdersQb()
{
    $subQuery = (new Query())
        ->select('id')
        ->from('order')
        ->where(['status' => Order::STATUS_SHIPPED])
        ->orderBy('created_at DESC')
        ->limit(100);

    $rows = (new Query())
        ->select([
            'o.id',
            'o.created_at',
            'c.name AS customer_name',
            'i.id AS item_id',
            'p.name AS product_name',
            'i.quantity',
        ])
        ->from(['o' => 'order'])
        ->innerJoin('customer c', 'c.id = o.customer_id')
        ->leftJoin('item i', 'i.order_id = o.id')
        ->leftJoin('product p', 'p.id = i.product_id')
        ->where(['o.id' => $subQuery])
        ->orderBy('o.created_at DESC')
        ->all(); // returns array of arrays, no hydration

    return $this->render('orders-qb', ['rows' => $rows]);
}

This single statement pulls the same data without model overhead. Use asArray() on the ActiveRecord query if you only need arrays but still want relation loading.

Trade‑offs and Limits

• Hydration cost — benchmark shows measurable memory growth around 10 k rows. For larger exports switch to asArray() or Query Builder scalar fetches.
• Lazy loading surprise — disable globally in config/main.php with 'components' => ['db' => ['lazyLoading' => false]] or call joinWith() explicitly.
• Relation via() performance — junction tables without proper foreign‑key indexes can cause temporary tables. Run EXPLAIN on the generated SQL (copy from debug panel) to verify.
• Events bypassed — any audit logging in afterFind will not run for Query Builder results.

Actionable Checklist

1. Enable the debug module in config/main-local.php (dev only): 'modules' => ['debug' => ['class' => 'yii\debug\Module']]. Verify the SQL panel shows query count and duration.
2. Profile a representative endpoint — call both the ActiveRecord and Query Builder actions, note query count, rows examined, and execution time.
3. Run EXPLAIN on the slowest query: EXPLAIN SELECT … in your DB client. Look for Using temporary or filesort.
4. Benchmark hydration — create a console command that iterates 50 k rows with Order::find()->all(), Order::find()->asArray()->all(), and the Query Builder scalar fetch. Measure memory with memory_get_peak_usage() and time with microtime(true).
5. Decide per use‑case — default to ActiveRecord for CRUD and domain logic; reach for Query Builder when you need reporting, bulk DML, or dialect features.

Following this checklist keeps the ORM convenient where it belongs and lets you drop to SQL when the data shape demands it — without guessing which layer is slower.