Choosing Between with() and joinWith() in Yii ActiveRecord: A Practical Guide

The N+1 Problem You Actually Face

You're loading a list of orders with their items. The naive Order::find()->all() followed by $order->items in a loop executes one query for orders and N queries for items — the classic N+1 problem. Yii's ActiveRecord offers two eager-loading methods: with() and joinWith(). They solve the same problem but generate different SQL, and picking the wrong one breaks pagination or inflates memory.

How with() Works: Separate IN Queries

with() executes a primary query for the main model, then one additional query per relation using WHERE id IN (...). For Order::find()->with('items')->all(), Yii runs:

SELECT * FROM `order`;
SELECT * FROM `order_item` WHERE `order_id` IN (1,2,3,...);

This approach keeps the main model row count accurate. Each Order object gets its items array populated without duplicating order rows. It works reliably for hasMany and many-to-many relations, and it respects limit()/offset() on the primary query.

How joinWith() Works: Single JOIN Query

joinWith() builds a single SQL statement with JOIN clauses. The same relation produces:

SELECT `order`.*, `items`.*
FROM `order`
LEFT JOIN `order_item` `items` ON `items`.`order_id` = `order`.`id`;

This can be faster for hasOne or belongsTo relations because the database does the work in one round trip. It also lets you filter or sort by related columns: Order::find()->joinWith('items')->where(['items.status' => 'shipped']). However, a hasMany join multiplies main model rows — one order with three items becomes three result rows. Yii hydrates duplicate Order objects unless you handle it carefully.

Worked Example: Paginated Orders with Items

Consider an Order model with hasMany items. You want page 2, 10 orders per page.

// Correct: with() preserves pagination
$orders = Order::find()
    ->with('items')
    ->limit(10)
    ->offset(10)
    ->all();
// Executes two queries, returns exactly 10 Order objects

// Broken: joinWith() multiplies rows
$orders = Order::find()
    ->joinWith('items')
    ->limit(10)
    ->offset(10)
    ->all();
// LIMIT 10 applies to joined rows, not orders.
// You may get 3 orders (with 3, 4, 3 items) or fewer.

Run this in a test project with Yii 2.0.45+ and enable query logging (Yii::debug() or debug toolbar). Compare the SQL and count the returned Order instances. The with() version returns 10 distinct orders; the joinWith() version returns however many orders fit in the first 10 joined rows.

Trade-offs and Limitations

• Column collisions: joinWith() does not auto-prefix columns. If both tables have created_at, you must alias: ->select(['order.*', 'items.created_at AS item_created']).
• Memory: with() loads all related rows into PHP arrays. For wide tables, add ->select(['id', 'name']) on the relation query to reduce overhead.
• Caching: queryCache works with both, but invalidation must cover all related tables. A change in order_item should invalidate the cached order query.
• Inverse relations: Declare inverseOf('order') on the items relation. Then $order->items[0]->order reuses the same Order object without an extra query.
• Batch iteration: each() and batch() do not support eager loading. For memory-efficient processing of large datasets with relations, use manual joins or separate queries inside the batch loop.

Decision Checklist

1. Is the relation hasMany or many-to-many? Use with().
2. Do you need pagination on the primary model? Use with().
3. Do you filter/sort by related columns? Use joinWith() (with hasOne/belongsTo).
4. Is the related table narrow and the dataset small? joinWith() may be simpler.
5. Always verify with EXPLAIN and the debug toolbar query count in your environment.

Start with with() for safety. Switch to joinWith() only when profiling shows a measurable benefit and you've confirmed pagination isn't affected.