'Grouping, counting and sorting collection takes too long. How can I improve my Laravel code?
In my app, there are several businesses, each business can have multiple orders, and each order can have various products. To get all business orders, including products (relation), I use $orders = $business->orders()->with('products')->get(); I need to calculate the quantity how often the product is ordered, in other words, the number of times I see each product in my orders.
So after get(), I have the following results.
[
{
"id": "94567311-e554-4265-8753-db5d021f0544",
"business_id": "8ba6c772-8c2d-4f3b-a7d6-3f1f0648fa77",
"user_id": null,
"business_customer_id": null,
"customer_name": " Nitin",
"customer_email": "[email protected]",
"customer_phone_number": "123123",
"customer_street": "#16-31, 5A LORONG HOW SUN, Bartley Residences, SINGAPORE 536563",
"customer_city": "Singapore",
"customer_state": "Singapore",
"customer_postal_code": "536563",
"customer_country": "sg",
"customer_pickup": 0,
"channel": "store_checkout",
"currency": "sgd",
"automatic_discount_name": "amount discount",
"automatic_discount_amount": 500,
"line_item_price": 3000,
"line_item_discount_amount": 0,
"line_item_tax_amount": 0,
"automatic_discount_reason": null,
"additional_discount_amount": 0,
"business_shipping_id": "937d5b59-b449-4901-a685-e208460f426c",
"shipping_method": "Shipping Not Applicable",
"shipping_amount": 0,
"shipping_tax_name": null,
"shipping_tax_rate": "0.0000",
"shipping_tax_amount": 0,
"slot_date": null,
"slot_time": null,
"amount": 2500,
"coupon_amount": 0,
"tax_setting_id": null,
"reference": null,
"messages": null,
"remark": null,
"status": "requires_business_action",
"executor_id": null,
"request_ip_address": "116.86.78.44",
"request_user_agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.1.1 Safari/605.1.15",
"request_method": "post",
"request_url": "https://staging.myshop.sg/8ba6c772-8c2d-4f3b-a7d6-3f1f0648fa77/checkout",
"request_country": null,
"request_data": {
"device": {
"name": "Macintosh",
"type": "computer"
},
"browser": {
"name": "Safari",
"version": "14.1.1"
},
"platform": {
"name": "OS X",
"version": "10_15_7"
},
"referrer": "https://staging.myshop.sg/8ba6c772-8c2d-4f3b-a7d6-3f1f0648fa77/checkout",
"requests": {
"email": "[email protected]",
"discount": {
"name": "amount discount",
"amount": 500
},
"shipping": {
"option": "937d5b59-b449-4901-a685-e208460f426c",
"address": {
"city": "Singapore",
"state": "Singapore",
"street": "#16-31, 5A LORONG HOW SUN, Bartley Residences, SINGAPORE 536563",
"country": "sg",
"postal_code": "536563"
}
},
"last_name": "Nitin",
"phone_number": "123123"
}
},
"created_at": "2021-09-07 12:49:46",
"updated_at": "2021-09-07 12:50:07",
"expires_at": null,
"closed_at": null,
"products_count": 2,
"products": [
{
"id": "94567311-e669-4943-b90e-537669099bc2",
"business_order_id": "94567311-e554-4265-8753-db5d021f0544",
"business_product_id": "92df0ca8-762d-4caf-a3af-ea0359906338",
"stock_keeping_unit": null,
"name": "Black Tote Bag",
"description": "Small",
"weight": null,
"length": null,
"width": null,
"depth": null,
"variation_key_1": "Size",
"variation_value_1": "Small",
"variation_key_2": null,
"variation_value_2": null,
"variation_key_3": null,
"variation_value_3": null,
"quantity": 1,
"tax_name": null,
"tax_rate": "0.0000",
"unit_price": 1500,
"tax_amount": 0,
"discount_amount": 0,
"price": 1500,
"remark": null,
"business_image_id": "92516d93-e06c-4718-b49a-8d63844ce5e5",
"created_at": "2021-09-07 12:49:46",
"updated_at": "2021-09-07 12:49:46",
"deleted_at": null
},
{
"id": "94567311-e6bb-48b4-8bdf-ad282dcc6d72",
"business_order_id": "94567311-e554-4265-8753-db5d021f0544",
"business_product_id": "92df0ca8-7781-4d9a-859e-8296669a9926",
"stock_keeping_unit": null,
"name": "Black Tote Bag",
"description": "Medium",
"weight": null,
"length": null,
"width": null,
"depth": null,
"variation_key_1": "Size",
"variation_value_1": "Medium",
"variation_key_2": null,
"variation_value_2": null,
"variation_key_3": null,
"variation_value_3": null,
"quantity": 1,
"tax_name": null,
"tax_rate": "0.0000",
"unit_price": 1500,
"tax_amount": 0,
"discount_amount": 0,
"price": 1500,
"remark": null,
"business_image_id": "92516d93-e06c-4718-b49a-8d63844ce5e5",
"created_at": "2021-09-07 12:49:46",
"updated_at": "2021-09-07 12:49:46",
"deleted_at": null
}
]
},
{
"id": "94606929-ab27-49c1-b8da-21cefee04e7a",
"business_id": "8ba6c772-8c2d-4f3b-a7d6-3f1f0648fa77",
"user_id": null,
"business_customer_id": null,
"customer_name": " H",
"customer_email": "[email protected]",
"customer_phone_number": "0290839918",
"customer_street": "Dn",
"customer_city": "Dn",
"customer_state": "Dn",
"customer_postal_code": "550000",
"customer_country": "sg",
"customer_pickup": 0,
"channel": "store_checkout",
"currency": "sgd",
"automatic_discount_name": "amount discount",
"automatic_discount_amount": 500,
"line_item_price": 3500,
"line_item_discount_amount": 0,
"line_item_tax_amount": 0,
"automatic_discount_reason": null,
"additional_discount_amount": 0,
"business_shipping_id": "937d5b59-b449-4901-a685-e208460f426c",
"shipping_method": "Shipping Not Applicable",
"shipping_amount": 0,
"shipping_tax_name": null,
"shipping_tax_rate": "0.0000",
"shipping_tax_amount": 0,
"slot_date": null,
"slot_time": null,
"amount": 3000,
"coupon_amount": 0,
"tax_setting_id": null,
"reference": null,
"messages": null,
"remark": null,
"status": "requires_business_action",
"executor_id": null,
"request_ip_address": "14.185.167.6",
"request_user_agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:92.0) Gecko/20100101 Firefox/92.0",
"request_method": "post",
"request_url": "https://staging.myshop.sg/8ba6c772-8c2d-4f3b-a7d6-3f1f0648fa77/checkout",
"request_country": null,
"request_data": {
"device": {
"type": "computer"
},
"browser": {
"name": "Firefox",
"version": "92.0"
},
"platform": {
"name": "Windows",
"version": "10.0"
},
"referrer": "https://staging.myshop.sg/8ba6c772-8c2d-4f3b-a7d6-3f1f0648fa77/checkout",
"requests": {
"email": "[email protected]",
"discount": {
"name": "amount discount",
"amount": 500
},
"shipping": {
"option": "937d5b59-b449-4901-a685-e208460f426c",
"address": {
"city": "Dn",
"state": "Dn",
"street": "Dn",
"country": "sg",
"postal_code": "550000"
}
},
"last_name": "H",
"phone_number": "0290839918"
}
},
"created_at": "2021-09-12 11:40:21",
"updated_at": "2021-09-12 11:41:38",
"expires_at": null,
"closed_at": null,
"products_count": 2,
"products": [
{
"id": "94606929-ad05-4c67-9491-644e8d3b434b",
"business_order_id": "94606929-ab27-49c1-b8da-21cefee04e7a",
"business_product_id": "92df0ca8-762d-4caf-a3af-ea0359906338",
"stock_keeping_unit": null,
"name": "Black Tote Bag",
"description": "Small",
"weight": null,
"length": null,
"width": null,
"depth": null,
"variation_key_1": "Size",
"variation_value_1": "Small",
"variation_key_2": null,
"variation_value_2": null,
"variation_key_3": null,
"variation_value_3": null,
"quantity": 1,
"tax_name": null,
"tax_rate": "0.0000",
"unit_price": 1500,
"tax_amount": 0,
"discount_amount": 0,
"price": 1500,
"remark": null,
"business_image_id": "92516d93-e06c-4718-b49a-8d63844ce5e5",
"created_at": "2021-09-12 11:40:21",
"updated_at": "2021-09-12 11:40:21",
"deleted_at": null
},
{
"id": "94606929-ad66-4a69-93ae-1e291c2d7e53",
"business_order_id": "94606929-ab27-49c1-b8da-21cefee04e7a",
"business_product_id": "93253bf2-ce63-4a9a-b4a6-8ecaff439028",
"stock_keeping_unit": null,
"name": "Bynd Artisan Tote Bag Duplicate",
"description": "Blue / Small",
"weight": null,
"length": null,
"width": null,
"depth": null,
"variation_key_1": "Color",
"variation_value_1": "Blue",
"variation_key_2": "Size",
"variation_value_2": null,
"variation_key_3": null,
"variation_value_3": null,
"quantity": 1,
"tax_name": null,
"tax_rate": "0.0000",
"unit_price": 2000,
"tax_amount": 0,
"discount_amount": 0,
"price": 2000,
"remark": null,
"business_image_id": "93253bf2-e5c2-4a22-8605-81c245bcf86b",
"created_at": "2021-09-12 11:40:21",
"updated_at": "2021-09-12 11:40:21",
"deleted_at": null
}
]
},
{
"id": "94606a50-2a40-476e-bd71-50c096270174",
"business_id": "8ba6c772-8c2d-4f3b-a7d6-3f1f0648fa77",
"user_id": null,
"business_customer_id": null,
"customer_name": " nitin",
"customer_email": "[email protected]",
"customer_phone_number": "9000123123",
"customer_street": "test",
"customer_city": "sg",
"customer_state": "singapore",
"customer_postal_code": "600123",
"customer_country": "sg",
"customer_pickup": 0,
"channel": "store_checkout",
"currency": "sgd",
"automatic_discount_name": "% discount",
"automatic_discount_amount": 200,
"line_item_price": 2000,
"line_item_discount_amount": 0,
"line_item_tax_amount": 0,
"automatic_discount_reason": null,
"additional_discount_amount": 0,
"business_shipping_id": "937d5b59-b449-4901-a685-e208460f426c",
"shipping_method": "Shipping Not Applicable",
"shipping_amount": 0,
"shipping_tax_name": null,
"shipping_tax_rate": "0.0000",
"shipping_tax_amount": 0,
"slot_date": null,
"slot_time": null,
"amount": 1800,
"coupon_amount": 0,
"tax_setting_id": null,
"reference": null,
"messages": null,
"remark": "test",
"status": "requires_business_action",
"executor_id": null,
"request_ip_address": "116.86.78.44",
"request_user_agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.1.1 Safari/605.1.15",
"request_method": "post",
"request_url": "https://staging.myshop.sg/8ba6c772-8c2d-4f3b-a7d6-3f1f0648fa77/checkout",
"request_country": null,
"request_data": {
"device": {
"name": "Macintosh",
"type": "computer"
},
"browser": {
"name": "Safari",
"version": "14.1.1"
},
"platform": {
"name": "OS X",
"version": "10_15_7"
},
"referrer": "https://staging.myshop.sg/8ba6c772-8c2d-4f3b-a7d6-3f1f0648fa77/checkout",
"requests": {
"email": "[email protected]",
"remark": "test",
"discount": {
"name": "% discount",
"amount": 200
},
"shipping": {
"option": "937d5b59-b449-4901-a685-e208460f426c",
"address": {
"city": "sg",
"state": "singapore",
"street": "test",
"country": "sg",
"postal_code": "600123"
}
},
"last_name": "nitin",
"phone_number": "9000123123"
}
},
"created_at": "2021-09-12 11:43:34",
"updated_at": "2021-09-12 11:44:02",
"expires_at": null,
"closed_at": null,
"products_count": 1,
"products": [
{
"id": "94606a50-2bac-48b8-860f-eebec27aff8d",
"business_order_id": "94606a50-2a40-476e-bd71-50c096270174",
"business_product_id": "93253bf2-ce63-4a9a-b4a6-8ecaff439028",
"stock_keeping_unit": null,
"name": "Bynd Artisan Tote Bag Duplicate",
"description": "Blue / Small",
"weight": null,
"length": null,
"width": null,
"depth": null,
"variation_key_1": "Color",
"variation_value_1": "Blue",
"variation_key_2": "Size",
"variation_value_2": null,
"variation_key_3": null,
"variation_value_3": null,
"quantity": 1,
"tax_name": null,
"tax_rate": "0.0000",
"unit_price": 2000,
"tax_amount": 0,
"discount_amount": 0,
"price": 2000,
"remark": null,
"business_image_id": "93253bf2-e5c2-4a22-8605-81c245bcf86b",
"created_at": "2021-09-12 11:43:34",
"updated_at": "2021-09-12 11:43:34",
"deleted_at": null
}
]
},
{
"id": "9466c9d9-2a57-4551-b36b-294b64fb9fe4",
"business_id": "8ba6c772-8c2d-4f3b-a7d6-3f1f0648fa77",
"user_id": null,
"business_customer_id": null,
"customer_name": " Test",
"customer_email": "[email protected]",
"customer_phone_number": "092028028",
"customer_street": "DN",
"customer_city": "DN",
"customer_state": "DN",
"customer_postal_code": "550000",
"customer_country": "sg",
"customer_pickup": 0,
"channel": "store_checkout",
"currency": "sgd",
"automatic_discount_name": "% discount",
"automatic_discount_amount": 200,
"line_item_price": 2000,
"line_item_discount_amount": 0,
"line_item_tax_amount": 0,
"automatic_discount_reason": null,
"additional_discount_amount": 0,
"business_shipping_id": "937d5b59-b449-4901-a685-e208460f426c",
"shipping_method": "Shipping Not Applicable",
"shipping_amount": 0,
"shipping_tax_name": null,
"shipping_tax_rate": "0.0000",
"shipping_tax_amount": 0,
"slot_date": null,
"slot_time": null,
"amount": 1800,
"coupon_amount": 0,
"tax_setting_id": null,
"reference": null,
"messages": null,
"remark": null,
"status": "requires_business_action",
"executor_id": null,
"request_ip_address": "14.185.201.146",
"request_user_agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/93.0.4577.63 Safari/537.36",
"request_method": "post",
"request_url": "https://staging.myshop.sg/8ba6c772-8c2d-4f3b-a7d6-3f1f0648fa77/checkout",
"request_country": null,
"request_data": {
"device": {
"name": "WebKit",
"type": "computer"
},
"browser": {
"name": "Chrome",
"version": "93.0.4577.63"
},
"platform": {
"name": "Windows",
"version": "10.0"
},
"referrer": "https://staging.myshop.sg/8ba6c772-8c2d-4f3b-a7d6-3f1f0648fa77/checkout",
"requests": {
"email": "[email protected]",
"discount": {
"name": "% discount",
"amount": 200
},
"shipping": {
"option": "937d5b59-b449-4901-a685-e208460f426c",
"address": {
"city": "DN",
"state": "DN",
"street": "DN",
"country": "sg",
"postal_code": "550000"
}
},
"last_name": "Test",
"phone_number": "092028028"
}
},
"created_at": "2021-09-15 15:45:40",
"updated_at": "2021-09-15 15:46:08",
"expires_at": null,
"closed_at": null,
"products_count": 1,
"products": [
{
"id": "9466c9d9-3131-43f7-99f3-c41ccd3308d1",
"business_order_id": "9466c9d9-2a57-4551-b36b-294b64fb9fe4",
"business_product_id": "93253bf2-ce63-4a9a-b4a6-8ecaff439028",
"stock_keeping_unit": null,
"name": "Bynd Artisan Tote Bag Duplicate",
"description": "Blue / Small",
"weight": null,
"length": null,
"width": null,
"depth": null,
"variation_key_1": "Color",
"variation_value_1": "Blue",
"variation_key_2": "Size",
"variation_value_2": null,
"variation_key_3": null,
"variation_value_3": null,
"quantity": 1,
"tax_name": null,
"tax_rate": "0.0000",
"unit_price": 2000,
"tax_amount": 0,
"discount_amount": 0,
"price": 2000,
"remark": null,
"business_image_id": "93253bf2-e5c2-4a22-8605-81c245bcf86b",
"created_at": "2021-09-15 15:45:40",
"updated_at": "2021-09-15 15:45:40",
"deleted_at": null
}
]
}
]
Using the following code, I achieved exactly what I wanted:
$orders = $business->orders()->with('products')->get();
foreach ($orders as $order){
foreach ($order->products as $product)
$subset[] = $product;
}
$groupedOrderedProducts = collect($subset)->groupBy('business_product_id');
$topProducts = $groupedOrderedProducts->map(function ($product){
return ['product' => $product->first(),'count'=>$product->count()];
})->sortByDesc('count');
Results:
{
"93253bf2-ce63-4a9a-b4a6-8ecaff439028": {
"product": {
"id": "94606929-ad66-4a69-93ae-1e291c2d7e53",
"business_order_id": "94606929-ab27-49c1-b8da-21cefee04e7a",
"business_product_id": "93253bf2-ce63-4a9a-b4a6-8ecaff439028",
"stock_keeping_unit": null,
"name": "Bynd Artisan Tote Bag Duplicate",
"description": "Blue / Small",
"weight": null,
"length": null,
"width": null,
"depth": null,
"variation_key_1": "Color",
"variation_value_1": "Blue",
"variation_key_2": "Size",
"variation_value_2": null,
"variation_key_3": null,
"variation_value_3": null,
"quantity": 1,
"tax_name": null,
"tax_rate": "0.0000",
"unit_price": 2000,
"tax_amount": 0,
"discount_amount": 0,
"price": 2000,
"remark": null,
"business_image_id": "93253bf2-e5c2-4a22-8605-81c245bcf86b",
"created_at": "2021-09-12 11:40:21",
"updated_at": "2021-09-12 11:40:21",
"deleted_at": null
},
"count": 4
},
"92df0ca8-762d-4caf-a3af-ea0359906338": {
"product": {
"id": "94567311-e669-4943-b90e-537669099bc2",
"business_order_id": "94567311-e554-4265-8753-db5d021f0544",
"business_product_id": "92df0ca8-762d-4caf-a3af-ea0359906338",
"stock_keeping_unit": null,
"name": "Black Tote Bag",
"description": "Small",
"weight": null,
"length": null,
"width": null,
"depth": null,
"variation_key_1": "Size",
"variation_value_1": "Small",
"variation_key_2": null,
"variation_value_2": null,
"variation_key_3": null,
"variation_value_3": null,
"quantity": 1,
"tax_name": null,
"tax_rate": "0.0000",
"unit_price": 1500,
"tax_amount": 0,
"discount_amount": 0,
"price": 1500,
"remark": null,
"business_image_id": "92516d93-e06c-4718-b49a-8d63844ce5e5",
"created_at": "2021-09-07 12:49:46",
"updated_at": "2021-09-07 12:49:46",
"deleted_at": null
},
"count": 3
},
"92df0ca8-7781-4d9a-859e-8296669a9926": {
"product": {
"id": "94567311-e6bb-48b4-8bdf-ad282dcc6d72",
"business_order_id": "94567311-e554-4265-8753-db5d021f0544",
"business_product_id": "92df0ca8-7781-4d9a-859e-8296669a9926",
"stock_keeping_unit": null,
"name": "Black Tote Bag",
"description": "Medium",
"weight": null,
"length": null,
"width": null,
"depth": null,
"variation_key_1": "Size",
"variation_value_1": "Medium",
"variation_key_2": null,
"variation_value_2": null,
"variation_key_3": null,
"variation_value_3": null,
"quantity": 1,
"tax_name": null,
"tax_rate": "0.0000",
"unit_price": 1500,
"tax_amount": 0,
"discount_amount": 0,
"price": 1500,
"remark": null,
"business_image_id": "92516d93-e06c-4718-b49a-8d63844ce5e5",
"created_at": "2021-09-07 12:49:46",
"updated_at": "2021-09-07 12:49:46",
"deleted_at": null
},
"count": 1
}
}
I feel that my code is not the best solution. How can I improve it?
Solution 1:[1]
You can define a hasManyThrough relationship for business and products, that way you can select the products of the business directly and group them.
$groupedOrderedProducts = $business->products()->groupBy('business_product_id')->get();
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | John Zwarthoed |