0

I have 4 tables. (I have more columns for each table, but listing only the required once here)

order_header:

id
ship_method
order_status

ship_method:

id

status:

code

order_items:

header_id

This is my order_header model's relationship with order_items:

public function order_items()
    {
        return $this->hasMany('\App\Models\OrderProduct', 'header_id', 'id');
    }

This is my final query to get order_header data:

$orders = OrderHeader::select('order_header.*', 'shipping_method.name as shipping_method_name', 'status.description as status_name')
                            ->join('shipping_method', 'shipping_method.id', '=', 'order_header.ship_method')
                            ->join('status', 'status.code', '=', 'order_header.order_status')
                            ->with(['order_items', 'creator:id,username', 'updater:id,username']);

This returns the $orders as below with it's order_items:

ex: id 17

    "data": {
        "id": 17,
        "shipping_method_name": "shipping method 1 update",
        "status_name": "pending",
        "order_items": [
          {
            "id": 35,
            "header_id": 17,
            "order_quantity": 5,
            "total_line_value": "20.00",
          },
          {
            "id": 36,
            "header_id": 17,
            "order_quantity": 5,
            "total_line_value": "20.00",
          },
          {
            "id": 37,
            "header_id": 17,
            "order_quantity": 5,
            "total_line_value": "20.00",
          }
}

Now how can I sum the values of order_items' order_quantity and total_line_value and display? For example: at above id 17, I need to display two more lines as order_quantity_sum: 15 and total_line_value_sum: 60. How to achieve this please someone help me. Thanks in advance.

  • Does this answer your question? [Laravel Eloquent Sum of relation's column](https://stackoverflow.com/questions/21679678/laravel-eloquent-sum-of-relations-column) – Kamlesh Paul Sep 25 '20 at 05:44
  • thanks for the quick response, but no this answer didn't help me. much appreciate it if you could help me here. –  Sep 25 '20 at 05:45
  • I tried adding this line $orders->order_items->sum('order_quantity'); and I get an error message saying: Property [order_items] does not exist on the Eloquent builder instance. –  Sep 25 '20 at 05:54
  • 1
    check [this](https://stackoverflow.com/a/58668526/6274211). – iamab.in Sep 25 '20 at 05:57
  • thank you @iamab.in. by using the accepted answer I could achieve what I want, but it's returning the sum with deleted records as well. now looking for a solution for that. –  Sep 25 '20 at 06:40
  • are you using soft deletes? – iamab.in Sep 25 '20 at 06:53
  • yes, I added a condition in where clause. and it's returning with deleted_at null now –  Sep 25 '20 at 06:55

1 Answers1

1

You can use DB::raw('sum('order_items.order_quantity') as total_order_items') in your select. Same approach is for total line value

knubbe
  • 1,132
  • 2
  • 12
  • 21