Level 104
What happens of the pizza price(s) change over time - you don't store the price with the order?
Mar 22, 2022
15
Level 1
Adding up unique users with total value
Hello, I have 3 tables
Users - id, name, email, phone Orders - id, user_id, pizza_id, small_pizza, medium_pizza, large_pizza Pizzas - id, name, description, small_pizza_price, medium_pizza_price, large_pizza_price.
I'm trying to make a view where I can display this table through a foreach: user - email - phone - total order value of all time
user - email - phone i can get through the Orders <-> Users models link as :
$order -> user -> name .
and I can get the total value of the orders through the Orders <-> Pizzas models
$order->small_pizza * $order->pizza->small_pizza_price +$order->mediu_pizza * $order->pizza->mediu_pizza_price +$order->large_pizza * $order->pizza->large_pizza_price
But now I want to sum them up somehow so I have unique clients and a total from all their orders. Right now I have clients with multiple orders and their total value instead of unique clients with total value of orders of all time.
Does anyone have any ideas/suggestions?
Level 1
@tykus no the price is saved in the pizzas table...and yes now that you said it if the price changes over time then it will mess things up.
Level 104
@claudio88 it will. I would consider an order_items pivot table with order_id, pizza_id, unit_price and quantity columns - this will give you order history with contemporaneous pricing
Level 1
The question still remains even if I have a table with non-change-able total values due to price a price change...I will still have multiple id/users and total values instead of one single total order value of all time assigned to unique clients.
Level 104
You can use joins if there is a relation between Order and Pizza - what do you put into the small_pizza, medium_pizza ,large_pizza columns on orders? Why is there only one pizza_id foreign key - does a customer only order one type of Pizza in different sizes and quantities?
Level 75
You can sum and group by clients.
Edit:
It's best to design some of this stuff with paper and pencil prior to coding so it will all make sense.
Level 51
$users = User::query()
->select(DB::raw('users.*, SUM(pizzas.price) AS total_price'))
->leftJoin('orders', 'users.id', '=', 'orders.user_id')
->leftJoin('pizzas', 'orders.pizza_id', '=', 'pizzas.id')
->groupBy('users.id')
->get();
You will get all user attributes with the total_price
I assumed you only have one price per pizza.
Level 104
@MohamedTammam how do you come by a price column on pizzas??? The problem starts with the schema; no point suggesting queries that do not address the underlying database design problem.
Level 51
@tykus I already mentioned 😅
I assumed you only have one price per pizza.
By reading the comments and you suggestion on how he should change the schema to cover changing the price case. I just added a query that can be a reference in the future.
Level 104
@MohamedTammam did you read the OP?
Level 1
What if I add a new value to orders table like archived_total_order and save in it the value of small pizza quantity * small pizza price + medium + large. How can i do a sum of that value and group by user_id from the orders table?
Level 104
@claudio88 that is a move in the right direction, and will allow you to calculate an accurate total. Using the built-it withSum Builder method (and assuming you now have a total column on the orders table)
$users = User::withSum('orders', 'total')->get();
In the resulting Collection, each User instance will have a orders_sum_total property automatically included.
To my mind, a join would be more performant:
$users = User::selectRaw('users.*, SUM(orders.total) as total_orders')
->leftJoin('orders', 'orders.user_id', 'users.id')
->groupBy('orders.user_id')
->get();
In the resulting Collection, each User instance will have a total_orders property included.
I still am of the opinion that a pivot table with order details (described previously) would be the better approach
1 like
Level 1
@tykus I like the first idea with the withsum(). I'm at work atm but when I get home I will try it and let you know how it turned out. Thanks in advance for your help 😊
Level 104
@claudio88 yeah, it looks good using withSum - just check the performance of both approaches against your actual data; the withSum query selects the sum from a sub-query which I believe will not be as performant as the join approach. This performance difference, if any, may not be tolerable as the dataset grows.
You can always make the join approach more readable by creating a local scope on the User model so that the query in the Controller could look something like:
$users = User::withOrderTotal()->get()
Level 1
@tykus Thanks so much for all your help in showing a beginner how to do something more complicated.
I tried the idea with the withSum() while adding an order_total field in the orders table. Now I can display the total value of all the client's orders and the value is not affected by an edit to the price of a pizza. Thanks so much again for the help.
Please or to participate in this conversation.