How to aggregate rows across tables?

Trying to create an app that tracks recipes/ingredients/orders/groceries:

  • Two tables, one for recipes and one for ingredients. each recipe says how many pounds it yields.
  • recipe-ingredients table to list all the ingredients in each recipe (and their quantities)
  • Order line items: orders come in each week and each line item is a recipe that’s ordered. ie: order A with 2x of recipe 1, 3x of recipe 2. order B with 1x of recipe 1, 1x of recipe 2. In this example, that’s 4 rows since each order has two line items.

Here’s where I’m stuck:

  • Each week I need to generate a grocery list that tells how many of each ingredient to order to make all the recipes to fill all the orders. In the example above, when we add up the orders we’d have 3x of recipe 1 and 4x or recipe 2. Then I need to use the recipe-ingredients table to see how much that works out to of each ingredient.
  • One other complicating factor: order line items are quantities of a “meal”. The recipe isn’t built for one meal. they each list a yield in pounds. It’s simple math to convert, but it has to be taken account in the final counts. Ie, 3x of recipe 1 x 8 ounces = 24 ounces, and recipe 1 is listed to yield 12 ounces. which means all the recipe 1 ingredient quantities need to be doubled (24/12).
  • Lastly this needs to be aggregated across all orders. Ie, we calculate that we need 4 onions for all the recipe 1 orders and 6 onions for recipe 2 orders, then the final list would be grouped to show one onions row with a quantity of 10.