Laravel Eloquent Sum of relation's column
PhpLaravelEloquentRelationPhp Problem Overview
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.
- The Cart table only contains the following columns:
id
,user_id
,product_id
and timestamps. - The UserModel
hasMany
Carts (because a user can store multiple products). - The CartModel
belongsTo
a User and CartModelhasMany
Products.
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).
Php Solutions
Solution 1 - Php
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.
Solution 2 - Php
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.
Solution 3 - Php
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.
Solution 4 - Php
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');
Solution 5 - Php
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")
Solution 6 - Php
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
Solution 7 - Php
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.
Solution 8 - Php
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() }}