1

tl;dr I made a SQLFiddle here http://sqlfiddle.com/#!9/031e71/1 with a minimal, complete, reproducible test case (though very specific to the problem domain of orders and products).

I have a Zen Cart ecommerce system on MariaDB and have a problem with the way order information is extracted into downstream order management systems. I have also looked into scripting in the order management system to modify the data, but it seems their API doesn't allow modification of orders at that stage (argh!) so I'm trying it via the SQL used as the orders are read from MariaDB into the order management system.

The scenario is where a coupon has been applied to an order, resulting in a discount, and I want that coupon's existence to appear in a resultset alongside the products in the order. So if you purchase Nuts for $100 and apply a $10 discount coupon, I want two rows in the resultset, one for Nuts at $80 and one for the coupon at $10, so the total equals $90 (the $100 cost less the $10 discount). The maths isn't the problem, the problem is that the existence of the products, and the existence of the coupon, are in two very different tables.

Zen Cart has three main tables:

  • orders - one record per Order
  • orders_totals - 4 or 5 records per Order, with things like Total, Sub-Total, VAT Content, Discount Coupon (this is the crux of my problem)
  • orders_products - one record per product in each order.

I want to join orders against both orders_totals and orders_products to get one row per product, and an extra row if a row exists in orders_totals for that order that is a coupon.

Is there an elegant way to solve this? I'm not sure if a cunning JOIN is enough, or if a View or PARTITION may be required.

** Edit ** I made a new fiddle which takes the coupon amount, divides it by the number of products, and subtracts that portion from each product amount, combined with the UNION suggestion. See http://sqlfiddle.com/#!9/27ad50/3 .. but this seems quite ugly with all the subselects going on in the joins in the first half of the UNION, I hesitate to say this is a good solution!

8
  • Doh! Of course I do. My SQL is pretty rusty, I don't seem to need UNION more than once every couple of years. Thank you, if you put that in an Answer I could accept it. – Neek Nov 27 '18 at 13:17
  • What would you do about the troublesome cost issue of wanting the product's price to be decremented by twice the discount amount, bearing in mind that there may be more than one product associated with the order? The problem is that the order management system requires the total cost of the products to exactly equal the orders_totals row with name 'total'. If it's different, it starts to create synthetic items to accommodate the difference. edit actually perhaps having 'nuts' stay at 100, and have the 'coupon' "product" be negative, may work.. I'll create an order like this and see. – Neek Nov 27 '18 at 13:20
  • Is there a way to convert Coupon in another product instead of an orders_total? – McNets Nov 27 '18 at 13:57
  • I'd love to make the coupon existence behave just like another product, but 1/ this would involve pretty deep hacking of Zen Cart and it's worth avoiding and 2/ it would still involve a negative value going into the order management system (e.g. price -10 for the coupon line item) which for reasons I must avoid. I made a new fiddle that seems to work, subtracting a fraction of the coupon cost from the product cost, so the totals still add up - see edit to the question. – Neek Nov 27 '18 at 14:10
  • 1
    It is not about ugliness, if it does what is supposed to do. dbfiddle.uk/… – McNets Nov 27 '18 at 19:00
1

In this case, and due Coupon is an specific entry of another table I'd use a UNION.

As a suggestion, I'd try to convert 'Coupon' entry of orders_total, into another product added as another row to the orders table.

SELECT orders_id, products_name as name, cost
FROM orders o 
JOIN  orders_products op on o.orders_id = op.fk_orders_id
WHERE o.orders_id = 1
UNION
SELECT fk_orders_id, name, total as cost
FROM   orders_totals
WHERE fk_orders_id = 1
AND   name = 'coupon'

Results:

| orders_id |   name | cost |
|-----------|--------|------|
|         1 |   Nuts |  100 |
|         1 | coupon |  -10 |

sqlfiddle here

1
  • This solves the problem as stated, except for the quirk in maths of wanting the Nuts product to appear to be worth only $90 instead of $100. See my edit to the original post with a proposed solution, but it seems ugly to me. – Neek Nov 27 '18 at 14:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.