1

Scenario: a restaurant's PostgreSQL DB.

If I have a column called stock (in a table called ingredients) and I have another column called inStock (boolean column in a table called dish) and a dish is only in stock if all the ingredients that are associated with the dish are in stock. To check this I can perform a query.

Would it be possible to do the following: If the result of all the rows returned from the query is != 0 then set the dish's inStock to true else set it to false.

How would I do this?

Mark
  • 1,337
  • 23
  • 34
  • How would I go about doing it? – Mark Mar 21 '17 at 21:09
  • how is ingredients and dish `joined`? – Chris Moutray Mar 21 '17 at 21:11
  • There is a table called dishes_ingredients which maps dishes to ingredients using their IDs. The columns in dishes_ingredients are: dish_id | ingredient_id | ingredient_qty – Mark Mar 21 '17 at 21:13
  • Not sure if queries would be supported in formulas, but you could definitely create a view for this. Still, I'd prefer the write model to just be updated based on events. When the availability of some ingredients changes you could fire `IngredientOutOfStock` and `IngredientInStock` events. Some event handlers would be responsible for updating the dish availability that depends on these ingredients. Therefore, the business logic is kept in the write model and queries become very simple. – plalx Mar 21 '17 at 21:13
  • I like this idea. I'm haven't really used events etc. – Mark Mar 21 '17 at 21:18

1 Answers1

0

You could start by selecting out the ingredients that don't have stock

select distinct d.id
from dish as d
inner join dishes_ingredients as di on di.dish_id == d.id 
where di.ingredient_qty == 0

This would give you the dish ids that don't have all the stock.

... and then i guess use as a sub query to do an update

update dish
set instock = 0 // or false
where id in (
  select distinct d.id
  from dish as d
  inner join dishes_ingredients as di on di.dish_id == d.id 
  where di.ingredient_qty == 0
)

Haven't used postgres so the syntax might not be quite right

Chris Moutray
  • 18,029
  • 7
  • 45
  • 66
  • How would I let this work dynamically rather than me having to perform this constantly. The idea above of a listener/event handler seemed good. – Mark Mar 21 '17 at 21:33
  • this started out as a sql question so its unclear what the rest of you app looks like - perhaps this helps http://stackoverflow.com/a/8250729/81053 – Chris Moutray Mar 21 '17 at 21:41
  • tbh an event publish and handle implementation would work but it really depends on the scale of your app - lots of extra complexity when a simple update sql command would do for a small app - KISS – Chris Moutray Mar 21 '17 at 21:44