CODEWITHSUNDEEP
phplaraveleloquentrelation
Admiral
Admiral

Reputation: 1908

Laravel Eloquent Sum of relation's column

I've been working on a shopping cart application and now I've come to the following issue..

There is a User, a Product and a Cart object.

Now to calculate the total products I can just call: Auth::user()->cart()->count().

My question is: How can I get the SUM() of prices (a column of product) of the products in cart by this User?
I would like to accomplish this with Eloquent and not by using a query (mainly because I believe it is a lot cleaner).

Upvotes: 171

Views: 543770

Answers (9)

zobay
zobay

Reputation: 106

For aggregate calculations in Eloquent relationships (Laravel 8+), leverage withSum for eager loading and loadSum for deferred loading.

// Eager loading with withSum
$user = Auth::user()->withSum('products', 'price')->get();
$totalPrice = $user->products_sum_price;

// Deferred loading with loadSum
$user = Auth::user();
$user->loadSum('products', 'price');
$totalPrice = $user->products_sum_price;

The updated official documentation can be found here.

Upvotes: 0

vstruhar
vstruhar

Reputation: 335

Since version 8, there is a withSum method on Eloquent, so you could use this.

Auth::user()->withSum('products', 'price')->products_sum_price;

This won't load all products into memory and then sum it up with collection method. Rather it will generate a sub query for the database, so it's quicker and uses less memory.

Upvotes: 13

Ray
Ray

Reputation: 47

For people who just want to quickly display the total sum of the values in a column to the blade view, you can do this:

{{ \App\Models\ModelNameHere::sum('column_name') }}

You can also do it for averages:

{{ \App\Models\ModelNameHere::avg('column_name') }}

Min:

{{ \App\Models\ModelNameHere::min('column_name') }}

Max:

{{ \App\Models\ModelNameHere::max('column_name') }}

To get the Count of a table:

{{ \App\Models\ModelNameHere::count() }}

Upvotes: 3

Cak Bud
Cak Bud

Reputation: 358

You can pass this as UserModel attribute. Add this code to UserModel.

public function getProductPriceAttribute(){
    return $this->cart()->products()->sum('price');
}

I assume something like this:

  • UserModel has a one to many relationship with a CartModel named cart
  • CartModel has a one to many relationship with ProductModel named products

And then you can get sum price of the product like this:

Auth::user()->product_price

Upvotes: 7

M Umer Yasin
M Umer Yasin

Reputation: 304

you can do it using eloquent easily like this

$sum = Model::sum('sum_field');

its will return a sum of fields, if apply condition on it that is also simple

$sum = Model::where('status', 'paid')->sum('sum_field');

Upvotes: 18

Ahmed Mahmoud
Ahmed Mahmoud

Reputation: 1832

Also using query builder

DB::table("rates")->get()->sum("rate_value")

To get summation of all rate value inside table rates.

To get summation of user products.

DB::table("users")->get()->sum("products")

Upvotes: 6

user1669496
user1669496

Reputation: 33048

Auth::user()->products->sum('price');

The documentation is a little light for some of the Collection methods but all the query builder aggregates are seemingly available besides avg() that can be found at http://laravel.com/docs/queries#aggregates.

Upvotes: 327

Abdur Rahman Turawa
Abdur Rahman Turawa

Reputation: 581

I tried doing something similar, which took me a lot of time before I could figure out the collect() function. So you can have something this way:

collect($items)->sum('amount');

This will give you the sum total of all the items.

Upvotes: 24

ahmad ali
ahmad ali

Reputation: 1245

this is not your answer but is for those come here searching solution for another problem. I wanted to get sum of a column of related table conditionally. In my database Deals has many Activities I wanted to get the sum of the "amount_total" from Activities table where activities.deal_id = deal.id and activities.status = paid so i did this. 

$query->withCount([
'activity AS paid_sum' => function ($query) {
            $query->select(DB::raw("SUM(amount_total) as paidsum"))->where('status', 'paid');
        }
    ]);

it returns

"paid_sum_count" => "320.00"

in Deals attribute.

This it now the sum which i wanted to get not the count.

Upvotes: 107

Related Questions