I’m working with the newbie databases and this is what I can’t figure out. The headline probably didn’t make up correctly, but the point is

there is a table of orders

enter image description here

it has an idCake column - the id of the cake that was ordered, it also has a confirm column, so when I press the 'confirm order' button on the front, I change 0 to 1 and run the function that makes the query in table 2 which has fields with cake id and product id

enter image description here

As a result, I have an array of objects. I understand it will be something like [{id: 1, quantity200}, {id: 2, quantity2}] - how many ingredients do you need to write off from table 3 (warehouse)

enter image description here

And then there were 2 questions - according to the correct logic, I structure the work of the back-up and how is it more correct to write off the ingredients from the warehouse (just to put an array into a cycle and make many small queries or do you need to make one big request?)

  • What does Weght mean in the order table? - Alex78191
  • @ Alex78191 weight of the cake - you need to multiply the quantity of the ingredient by the weight of the cake - Goshka Tarasov
  • @ Alex78191 can be done like this - but I would like to understand exactly how to do such things correctly - Goshka Tarasov
  • @ GoshkaTaras was probably wrong - Alex78191

2 answers 2

You can somehow

 UPDATE Ингридиент as ingr SET quantity = quantity - (SELECT quantity * Weight FROM Торт WHERE idCake = {{cakeId}} AND idIngr = ingr.id) WHERE idIngr IN (SELECT idIngr from Торт WHERE idCake = {{cakeId}})

Or you can combine the Ингридиент and Торт tables instead of subqueries.

 UPDATE Ингридиент as I INNER JOIN ( SELECT quantity * Weight FROM Торт WHERE idCake = {{cakeId}} ) as T ON I.idIngr = T.idIngr SET I.quantity = I.quantity - T.quantity

Did not test the code for performance.

    Even on the most unremarkable hardware, 1000 queries to the database will be processed as if nothing had happened. I do not think that the site with the production of cakes will have 100k hits per day. I would make 2 requests: 1 to the order table, for confirmation, 2 to write off products. Plus, if the confirmation comes from the admin panel, then you don’t need to think about it at all, because 100 such hits will not even feel the DB.