I need to get the products that are ordered most often. I can get orders with ordered products this way:
$business->orders()->with('products')->get();
It returns the next collection (I just show you one order):
[
{
"id": "94567311-e554-4265-8753-db5d021f0544",
"business_id": "8ba6c772-8c2d-4f3b-a7d6-3f1f0648fa77",
"user_id": null,
"business_customer_id": null,
"customer_name": " Alona",
"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": "",
"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": "",
"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": "alona",
"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
}
]
},
....
]
Each order can have multiple products. So I need somehow to get a count of how many times each product was ordered. Something like this:
[
'count' => 37
'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
},
]
Any advice will be helpful. Thanks all!
CodePudding user response:
You can count a relation, so assuming you also defined the relation on products. This should be a good start:
Products::withCount('orders')->orderBy('orders_count', 'desc')->paginate(10);
CodePudding user response:
$business->orders()->with(['products'=>function($qu)
{
$qu->orderBy('orders_count', 'desc');
}])->get();
Try This