Performance enhancements for order admin - Eager Loading

[justinholtweb]

Optimize Control Panel Order Detail Performance with Eager Loading

Problem

When viewing order details in the Craft Commerce Control Panel, the system generates excessive duplicate database queries due to missing eager loading. This creates significant performance issues, especially for orders with multiple line items.

Observed Performance Issues

Before optimization:

• Order with 8 line items: 1,229 total queries (972 duplicates)
• Load time: 11.6 seconds

After optimization:

• Same order: 293 total queries (76 duplicates)
• Load time: 2.2 seconds

Root Causes

1. OrdersController::actionEditOrder() doesn't use eager loading - When fetching an order for the CP detail view, the controller calls getOrderById() without eager loading relationships, causing N+1 queries for line items, transactions, adjustments, addresses, and customer data.

2. No purchasables eager loading - While withAll() exists for eager loading order relationships, it doesn't include purchasables for line items. Each line item's getPurchasable() call triggers a separate database query.

3. Missing eager loading support in getOrderById() - The service method doesn't accept parameters to enable eager loading, making it impossible for controllers to optimize queries.

Solution

I have a PR ready that adds comprehensive eager loading support for CP order detail views:

1. Enhanced Orders::getOrderById() with Eager Loading Parameter

File: src/services/Orders.php

Added optional $withAll parameter to enable eager loading:

public function getOrderById(int $id, bool $withAll = false): ?Order
{
    if (!$id) {
        return null;
    }

    $query = Order::find()->id($id)->status(null);

    if ($withAll) {
        $query->withAll();
    }

    return $query->one();
}

2. Updated OrdersController to Use Eager Loading

File: src/controllers/OrdersController.php

Modified actionEditOrder() to request eager loading:

$order = $plugin->getOrders()->getOrderById($orderId, withAll: true);

3. Added Purchasables Eager Loading to OrderQuery

File: src/elements/db/OrderQuery.php

Added $withPurchasables property and withPurchasables() method to enable purchasable eager loading:

public bool $withPurchasables = false;

public function withPurchasables(bool $value = true): OrderQuery
{
    $this->withPurchasables = $value;
    return $this;
}

Updated populate() to batch-load purchasables when enabled:

if ($this->withPurchasables === true || $this->withAll) {
    foreach ($orders as $order) {
        $lineItems = $order->getLineItems();
        if (!empty($lineItems)) {
            $siteId = $order->orderSiteId;
            $customerId = $order->customerId;
            $lineItems = Plugin::getInstance()->getLineItems()
                ->eagerLoadPurchasablesForLineItems($lineItems, $siteId, $customerId);
            $order->setLineItems($lineItems);
        }
    }
}

4. Implemented Batch Purchasable Loading

File: src/services/LineItems.php

Added eagerLoadPurchasablesForLineItems() method to batch-load purchasables by element type:

public function eagerLoadPurchasablesForLineItems(array $lineItems, ?int $siteId = null, int|false|null $forCustomer = null): array
{
    // Group purchasable IDs by element type
    $purchasableIdsByType = [];
    foreach ($lineItems as $lineItem) {
        if ($lineItem->purchasableId && $lineItem->type !== LineItemType::Custom) {
            $elementType = \Craft::$app->getElements()->getElementTypeById($lineItem->purchasableId);
            if ($elementType && class_exists($elementType)) {
                $purchasableIdsByType[$elementType][] = $lineItem->purchasableId;
            }
        }
    }

    if (empty($purchasableIdsByType)) {
        return $lineItems;
    }

    $siteId ??= \Craft::$app->getSites()->getCurrentSite()->id;

    // Batch load all purchasables by type
    $purchasablesById = [];
    foreach ($purchasableIdsByType as $elementType => $ids) {
        $query = \Craft::$app->getElements()->createElementQuery($elementType)
            ->id(array_unique($ids))
            ->siteId($siteId)
            ->status(null)
            ->drafts(null)
            ->provisionalDrafts(null)
            ->revisions(null);

        if ($forCustomer !== null && $query instanceof \craft\commerce\elements\db\PurchasableQuery) {
            $query->forCustomer($forCustomer);
        }

        foreach ($query->all() as $purchasable) {
            $purchasablesById[$purchasable->id] = $purchasable;
        }
    }

    // Assign purchasables to line items
    foreach ($lineItems as $lineItem) {
        if (isset($purchasablesById[$lineItem->purchasableId])) {
            $lineItem->setPurchasable($purchasablesById[$lineItem->purchasableId]);
        }
    }

    return $lineItems;
}

Performance Impact

Query Reduction

Scenario	Before	After	Improvement
Total queries	1,229	293	-76%
Duplicate queries	972	76	-92%
Load time	11.6s	2.2s	-81%

Specific Optimizations

• Line items: 1 batch query instead of N queries
• Purchasables: 1 batch query per element type instead of N queries
• Transactions: 1 batch query instead of N queries
• Adjustments: 1 batch query instead of N queries
• Addresses: 1 batch query instead of 2N queries
• Customer: Loaded once instead of multiple lookups

Backward Compatibility

100% Backward Compatible

• getOrderById() defaults to $withAll = false, maintaining existing behavior
• All new methods are additions, not modifications to existing APIs
• Existing code continues to work without changes
• Frontend queries unaffected (they can use ->with() explicitly)

Testing

Tested on:

• Craft CMS 5.8.17
• Commerce 5.4.6
• Orders with 1-50+ line items
• Both simple products and variants with catalog pricing

[lukeholder]

@justinholtweb

Thanks for this! Can you please make a PR, and we can collab on it from there? Thanks!