How can I refactor this code to mitigate an N+1 query issue using Eloquent?

Question

I'd like to understand if there's an efficient way to mitigate an N+1 query problem in this particular circumstance with Eloquent. The context is that I'm currently building an API using Laravel 8.

Summary

For the purpose of this question, the example is Store -> has many -> Orders -> many-to-many -> Products, ie: a store has many orders, an order has many products, but a product can belong to many different stores. There is a product_orders pivot table, which also tracks a couple of additional values such as unit_price and quantity etc.

My goal is to create an API endpoint which lists all of a store's orders with the products that were on that particular order.

{
    "data": [
        {
            "id": "6531a4d4-b066-4673-ad59-8b4a49c0d675",
            "object": "order",
            "comments": "Sequi perspiciatis quasi dolorum rerum. Rem dolore nihil sint magnam totam explicabo. Non officia est ut inventore modi.",
            "order_items": [
                {
                    "product_id": "3c6400f5-fde9-47cb-ad05-ef164e00583e",
                    "product_name": "omnis inventore",
                    "unit_price": "17.15",
                    "quantity": 8
                },
                {
                    "product_id": "c208c8dd-df71-4331-909d-0615ec763555",
                    "product_name": "accusantium omnis",
                    "unit_price": "81.88",
                    "quantity": 3
                },
                {
                    "product_id": "05129518-882b-4558-b68f-df06c804a6d3",
                    "product_name": "voluptatem vitae",
                    "unit_price": "52.58",
                    "quantity": 4
                },
            ],
            "supplier_invoice_no": ABC1234,
        }
    ],
    }
}

For this task, I'm leveraging Eloquent API resources, so my code is as follows:

routes/api.php

Route::apiResource('/stores/{store}/orders', 'Api\Stores\StoreOrderController');

StoreOrderController.php

I'm retrieving the Store model using a Route Model binding.

public function index(Store $store)
    {
        return StoreOrdersResource::collection($store->orders);
    }

StoreOrdersResource.php

And this API resource is filtering the JSON response to just the relevant data, and this is where my issue lies I believe. For each Order passed through this StoreOrdersResource I'm retrieving $this->products aka $order->products, so a duplicate query is made, querying the Order's products for every one of the Store's orders.

 public function toArray($request)
    {
        return [
            'id'                    => $this->id,
            'object'                => "order",
            'comments'              => $this->comments,
            'order_items'           => $this->products,
            'supplier_invoice_no'   => $this->supplier_invoice_no,
        ];
    }

Perhaps there's a better way of writing this full-stop, but my assumption is that I'll have to somehow pull the Store's Orders with their related products, without pulling all Orders, or all products.

Any help would be much appreciated!

aleksejjj aleksejjj · Accepted Answer · 2020-09-14T20:50:57

To avoid additional queries you can load relations in your controller. Something like

// Models/Order.php
public function items()
{
    return $this->belongsToMany(Product::class)
                ->withPivot('unit_price', 'quantity');
}


// StoreOrderController.php
public function index(Store $store)
{
    $store->load('orders.items');

    return StoreOrdersResource::collection($store->orders);
}

// StoreOrdersResource.php
public function toArray($request)
{
    return [
        // ...
        'order_items' => $this->items,
    ];
}