Page 1 of 1

Possible unnecessary double iteration

Posted: Thu Jun 26, 2008 6:51 am
by jayshields
Picture this: I've got an orders table and an orders description table.

Code: Select all

orders:
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| order_id     | int(5)       | NO   | PRI | NULL    | auto_increment |
| table_id     | int(5)       | NO   |     | NULL    |                |
| total_price  | decimal(6,2) | NO   |     | NULL    |                |
| time_ordered | datetime     | NO   |     | NULL    |                |
| completed    | char(1)      | NO   |     | N       |                |
+--------------+--------------+------+-----+---------+----------------+
 

Code: Select all

order_lists:
+--------------+--------+------+-----+---------+-------+
| Field        | Type   | Null | Key | Default | Extra |
+--------------+--------+------+-----+---------+-------+
| order_id     | int(5) | NO   | PRI | NULL    |       |
| menu_item_id | int(5) | NO   | PRI | NULL    |       |
| quantity     | int(5) | NO   |     | NULL    |       |
+--------------+--------+------+-----+---------+-------+
 
The order_lsits table contains all the details of each order.

The price for each menu_item is kept in the menu_items table, so originally I thought it would be fine for working out total prices with a couple of joins. Then I realised that if I wanted to look at how much money I'd made last week, and I'd put all the prices of the menu items up by a £1 since then, it would give misleading results. So I figured it would be a better idea to log the total prices for each order as they were received.

Now to the problem. At the moment I insert a new row into the orders table first, then iterate through each menu item on the order, inserting them into order_lists using LAST_INSERT_ID() - this doesn't take into account the total_price property.

So, now, the only method I can think of to accomplish this properly is to iterate through all the menu items on the order (and their quantities) fetch their prices (multiply them by quantity) and work out the total price. Then run the insert query for the orders table. Then iterate through all the menu items on the order again for the order_lists table.

Surely there's a better method than this?

Thanks for any input.

EDIT: Just to mention a solution to the problem which I think is infeasible is to keep a menu_item price change log and scrap the total_price field in the orders table. Then everytime a menu item has it's price changed the old price is stored along with the ID of the item and the date/time in the change log. This way I could check if menu items prices had changed before working out how much the order acdtually came to. This would just create an over complex set of queries though.

EDIT(2): OK, I've had a rethink. I don't think this is the best way to solve it but it's the best I can think of. I've decided to change the table structures to:

Code: Select all

 
mysql> DESCRIBE `restaurant`.`orders`;
+--------------+----------+------+-----+---------+----------------+
| Field        | Type     | Null | Key | Default | Extra          |
+--------------+----------+------+-----+---------+----------------+
| order_id     | int(5)   | NO   | PRI | NULL    | auto_increment |
| table_id     | int(5)   | NO   |     | NULL    |                |
| time_ordered | datetime | NO   |     | NULL    |                |
| completed    | char(1)  | NO   |     | N       |                |
+--------------+----------+------+-----+---------+----------------+
 
mysql> DESCRIBE `restaurant`.`order_lists`;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| order_id     | int(5)       | NO   | PRI | NULL    |       |
| menu_item_id | int(5)       | NO   | PRI | NULL    |       |
| quantity     | int(5)       | NO   |     | NULL    |       |
| total_price  | decimal(6,2) | NO   |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
So basically all I'm doing now is storing the menu_item's price * quantity ordered in the order_lists table. Then if I ever want to fetch the order total price I can do a simple query using SUM(). I came to this decision because I couldn't work out how to write a query to insert the correct total price of a whole order into the orders table given the menu item id's and the quantities. If there was no notion of quantity it would be OK.

Another idea I almost went with would be to calculate the total price of the whole order using a SELECT query for each menu item on the order during the same loop that INSERTs into the order_lists table. Then I INSERT into the orders table afterwards, but doing this would mean require (different menu items * 2) + 1 total queries (and I would have to use LAST_INSERT_ID() + 1 when referencing the un-entered order).

Any better sugestions will be welcomed with open arms!

Re: Possible unnecessary double iteration

Posted: Thu Jun 26, 2008 9:24 am
by dml
There's a concept of a given menu item being offered at a given price at a given time, let's say it's called an "offering". You could have an `offering` table, with (menu_item_id, price, is_active). Raising the price of a menu item involves setting is_active to false on the existing offering, and inserting an offering with the new price. Then the order_lists table has an offering_id instead of a menu_item_id.

Re: Possible unnecessary double iteration

Posted: Thu Jun 26, 2008 9:57 am
by jayshields
That's essentially the same as my price change log idea, because your suggestion of an active flag would work better if there was some notion of insertion order, because the most recent one is always going to be the active one. It also has the same pitfall - the query for inserting into the orders table with a single query would be incredibly complex, if not impossible. Meaning that you'd probably have to execute a series of queries instead of just one. Which makes it more complicated that my current idea of moving the total price property into the order_lists table and calculating each order total price by doing a SUM().

This makes me rethink an earlier decision though. I just thought it was impossible to write a single insert query that would insert into the orders table the total price, but the total price is fetched from looking at the order_lists table, and getting every menu_item which is associated with a preset order_id, then looking at the menu_items table and fetching all the prices associated the the fetched menu_items, and then multiplying each of those menu_item prices by a preset quantity which will be different depending on which menu_item it is.

If I'm right, the above is impossible in a single query, but if it wasn't for the different quantity multipliers, it is possible.

Re: Possible unnecessary double iteration

Posted: Thu Jun 26, 2008 10:22 am
by dml
What I had in mind for getting the total for a given order with one query was:

Code: Select all

 
SELECT
  sum(offerings.price*order_lists.quantity)
FROM
  order_lists
  JOIN offerings ON order_lists.offering_id = offerings.id
WHERE 
  order_lists.order_id = 12345
 

Re: Possible unnecessary double iteration

Posted: Thu Jun 26, 2008 10:54 am
by jayshields
Thanks! I don't know why I didn't think of doing that query in the first place, because using something very similar to that I can just work out the the total order price and put that in the orders table.

I modified your query to:

Code: Select all

 
SELECT sum( menu_items.price * order_lists.quantity )
FROM order_lists
JOIN menu_items ON order_lists.menu_item_id = order_lists.menu_item_id
WHERE order_lists.order_id =1;
but somehow it produces 2809.00 from the following test data:

Code: Select all

 
order_lists:
+----------+--------------+----------+
| order_id | menu_item_id | quantity |
+----------+--------------+----------+
|        1 |            1 |        3 |
|        1 |            2 |        5 |
|        1 |            3 |       45 |
+----------+--------------+----------+
 
menu_items:
+--------------+-------+
| menu_item_id | price |
+--------------+-------+
|            1 |  7.50 |
|            2 |  6.50 |
|            3 |  8.00 |
+--------------+-------+
The total should be: 22.5 + 32.5 + 360 = 415.

Can you see what's wrong there? I've been staring at it for 10 minutes now...

EDIT: God I'm stupid. The JOIN clause was wrong. Sorted now. I may as well just do it like that and scrap the change log idea. Thanks alot.

Re: Possible unnecessary double iteration

Posted: Thu Jun 26, 2008 11:26 am
by jayshields
OK, I've finally sorted it. It's messy, but it works in the minimal amount of queries.

Here's what happens...

I loop through every menu item on the order executing this query:

Code: Select all

 
                        INSERT INTO
                            `order_lists`
                                (`order_id`, 
                                `menu_item_id`, 
                                `quantity`) 
                        VALUES
                        (
                            IFNULL((
                                SELECT
                                    MAX(`order_id`) 
                                FROM
                                    `orders`
                            ) + 1, 1), 
                            (
                                SELECT 
                                    `menu_item_id` 
                                FROM 
                                    `menu_items`
                                WHERE 
                                    `name` = '$itemname' 
                            ), $quantity
                        )
Then I execute this:

Code: Select all

 
INSERT INTO 
                        `orders` 
                            (`table_id`,
                            `time_ordered`, 
                            `total_price`)
                    VALUES 
                    (
                        $tableNumber,
                        NOW(), 
                        (
                            SELECT 
                                SUM(`menu_items`.`price` * `order_lists`.`quantity`)
                            FROM 
                                `order_lists` 
                            JOIN 
                                menu_items 
                            ON 
                                `order_lists`.`menu_item_id` = `menu_items`.`menu_item_id`
                            WHERE 
                                `order_lists`.`order_id` =
                                (
                                    SELECT 
                                        MAX(`order_id`) 
                                    FROM 
                                        `order_lists`
                                )
                        )
                    )
 
Sorry for the horrible indentation, I had to pick this out of a Java program (I changed the variables to PHP style...) and remove all the quotes and plus symbols.