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 CartModel hasMany 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).
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.
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.
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.
collect()
collect()
is used to enrich an array into a collection object, but if you dont have a collection in the first place it might be better to just use the raw array
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');
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")
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.
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
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() }}
Success story sharing
$sum = Auth::user()->cart()->products()->sum('price');
or whatever the price column is on your products table.Cart
model is really just the pivot table, so we shouldn't have to include that, as that's taken care of in theUser
andProduct
model with your relationship methods.Auth::user()->products->sum('price');
. Also make sure you are logged in so the Auth class knows which user to grab. Your cart model probably shouldn't have theproducts()
function, as that belongs in theUser
model for this relationship. You really shouldn't even need a model forCart
.products->sum
implies it is call sum on a collection object, rather than on the builder object returned by products(). It's worth clarifying which one you mean here, and ideally provide a link to explain both....->products->...
will query the database to get all of the current model's related products and then work with that in-memory collection. Using...->products()->...
just modifies the query being build without executing it until something like->sum()
is called.The latter can be more efficient, as it avoids transferring unnecessary information from the database into memory.