Laravel 9 Eloquent Sum of relation's column

Question

This is my very first question. And hope someone can help me.

I am trying to get all orders with there corresponding total amount of order items. And I manage to get the amount per order items.

How to return also in my select query the sum of amount per Order ?

    $orders = Order::join('user_category', 'user_category.user_id', '=', 'orders.purchaser_id')
      ->join('users', 'users.id', '=', 'user_category.user_id')
      ->with([
        'orderItems' => function($query) {
          $query->join('orders', 'orders.id', '=', 'order_items.order_id')->join('products', 'products.id', '=', 'order_items.product_id')
          ->select('order_items.order_id','order_items.quantity', 'products.price', DB::raw('order_items.quantity * products.price AS amount'));
        }
      ])
      ->select('orders.id', 'users.first_name', 'users.last_name')
      ->where('user_category.category_id', '=', 1)
      ->orderBy('orders.id', 'DESC')
      ->limit(5)
      ->get();

Hi and welcome :) Have you tried with: ->withSum('amount') — RossTsachev
– RossTsachev, Commented Apr 23, 2022 at 10:12
@Ross_102, yes I tried it. I even include ->withSum('amount') after the ->select() inside subquery, but still not working. I think withSum() will only work if its an actual column. Base on my query, amount is an alias — Kenjie
– Kenjie, Commented Apr 23, 2022 at 10:53

RossTsachev · Accepted Answer · 2022-04-23 13:28:21Z

1

"with" is a relation, while everything else you have are joined tables. You can modify your like that:

$orders = Order::join('user_category', 'user_category.user_id', '=', 'orders.purchaser_id')
      ->join('users', 'users.id', '=', 'user_category.user_id')
      ->leftJoin('order_items', 'order_items.order_id', '=', 'orders.id')
      ->join('products', 'products.id', '=', 'order_items.product_id')
      ->select('orders.id', 'users.first_name', 'users.last_name')
      ->addSelect(DB::raw('sum(order_items.quantity * products.price) as amount'))
      ->where('user_category.category_id', '=', 1)
      ->groupBy('orders.id', 'DESC')
      ->orderBy('orders.id', 'DESC')
      ->limit(5)
      ->get();

answered Apr 23, 2022 at 13:28

RossTsachev

9211 gold badge7 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kenjie Over a year ago

Thank you! I finally got it. I use addSelect as per your answer

Collectives™ on Stack Overflow

Laravel 9 Eloquent Sum of relation's column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related