php - How can I refactor this code to mitigate an N+1 query issue using Eloquent?
问题描述
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!
解决方案
为了避免额外的查询,您可以在控制器中加载关系。就像是
// 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,
];
}
推荐阅读
- python-3.x - Seaborn箱线图四分位数计算
- powerbi - 带有 Azure Data Lake Store 的 Power BI Desktop?
- elixir - Elixir - Phoenix - Ecto 术语
- google-cloud-dataflow - Apache Beam TextIO.read 然后合并成批处理
- ruby-on-rails - 在 Rails 5 中选择特定属性
- xml - GET_SEARCH_RESULTS 网络服务响应说 valid QueryText
- javascript - 在 REPL 中更改节点对象的表示
- ruby-on-rails - 如何使用 Google 地图和 jt-rails-address 向用户添加地址?
- r - 将列名称和标题(上图)设置为 R markdown 中带有 knitr、kableExtra 和 Latex 的表格的奇怪字符
- java - 将图像从 Objective-c 上传到服务器时出现损坏的文件