Say I've got a database that consists of the next:
orders(id, total, address_id) line_items(id, product_id, order_id, quantity, price) products(id, name, price) addresses(id, address)
I have to give a are convinced that consists of the next:
Order #123 Line Items 3 x Product 1 @ $3 = $9 2 x Product 2 @ 1 = $2 Total: $11 Shipping: 123 Main Street Order #124 Line Items 1 x Product 1 @ $3 = $3 1 x Product 2 @ 1 = $1 Total: $4 Shipping: 456 Jones Ave
So, a listing of orders, for every order, the id, their email list of line products, an order total, and also the shipping address have to be displayed.
What's the easiest method to do that without having done a question for every to fetch the road products and also the shipping information?
Should I've got a view that consists of the next:
order_id, line_item_quantity, line_item_price, product_name, line_item_total, shipping_address
After which group by order id within the application code? This is the best I'm able to develop, however it appears sloppy (not to mention, within the real life, there'd considerably more details in every table and much more tables).
You have to flatten the connection, and sort it by order ID:
select O.id, L.quantity, P.name, P.price, A.address, L.quantity * P.price subtotal, ( select sum(L1.quantity * P1.price) from line_items L1 inner join products P1 on L1.product_id = P1.id where L1.order_id = O.id ) ordertotal from orders O inner join line_items L on O.id = L.order_id inner join products P on L.product_id = P.id inner join addresses A on O.address_id = A.id order by O.id, L.id
Then for the way you construct your report, attempt to identify alterations in order ID when looping with the resulting rows. Once the order ID changes, it's time to setup a brand new group of order.
For instance, if you are using very reviews, you just need to set a brand new grouping on order ID, and it'll instantly segment orders.