PostgreSQL, complex query for calculating ingredients by recipe

Question

I have to calculate food and ingredients of used food stored in PostgreSQL tables:

    table1 'usedfood'

food    food                    used    used
code    name                    qty     meas
----------------------------------------------
10      spaghetti               3       pcs
156     mayonnaise              2       pcs
173     ketchup                 1       pcs
172     bolognese sauce         2       pcs
173     ketchup                 1       pcs
10      spaghetti               2       pcs
156     mayonnaise              1       pcs

table2 'ingredients'

food    ingr.   ingredient      qty     meas    
code    code    name            /1      in 1
----------------------------------------------
10      1256    spaghetti rinf  75      gramm
156     1144    salt            0.3     gramm
10      1144    salt            0.5     gramm
156     1140    fresh egg       50      gramm
172     1138    tomato          80      gramm
156     1139    mustard         5       gramm
172     1136    clove           1       gramm
156     1258    oil             120     gramm
172     1135    laurel          0.4     gramm
10      1258    oil             0.4     gramm
172     1130    corned beef     40      gramm

result:

used
times   code    food/ingredient qty     meas
----------------------------------------------
5       1256    spaghetti rinf  375     gramm
8       1258    oil             362     gramm
2       1138    tomato          160     gramm
3       1140    fresh egg       150     gramm
2       1130    corned beef     80      gramm
3       1139    mustard         15      gramm
8       1144    salt            3.4     gramm
2       1136    clove           2       gramm
2       1135    laurel          0.8     gramm
2       173     ketchup         2       pcs    //haven't any ingredients

For now I do this by looping through table1 and queriying table2 for each row then adding results and so on (with C) what may be very slow on larger data.

Table1 contains food code, food name and used quantity. Table2 contains ingredients (in messy order) with code and used quantity for one peace of food and also code of food in which appears.
Used quantity from table1 should be multiplied with quantity from table2 according to every recipe and should be added to result of ingredients code.
So all ingredient rows which goes to food "spaghetti" starts with food spaghetti code (10).
Food without any ingredient should be calculated with quantity from table1 and showed with same name. That actually mean it is a final product (like beer bottle).
Here may be more complication but I'm, affraid to ask. For example in ingredinets list may be ingredient which is recipe by himself. For example mustard which contains from vinegar, salt, seed, etc... What then? In table2 of showed example mustard is used as ready product (component).

Is here any way for do such calculation and getting results fast with using just PostgreSQL which will give ready results to C program?
Maybe not too complex as seem's for me? How would that query look like?

peterm · Accepted Answer · 2013-06-12 08:51:06Z

1

Try

SELECT SUM(f.qty) used_times,
       COALESCE(i.ingr_code, f.food_code) code,
       COALESCE(i.name, f.name) name,
       SUM(COALESCE(i.qty, 1) * f.qty) qty,
       COALESCE(i.meas, f.meas) meas
  FROM usedfood f LEFT JOIN ingredients i
    ON f.food_code = i.food_code
 GROUP BY i.ingr_code, i.name

Output:

| USED_TIMES | CODE |           NAME | QTY |  MEAS |
----------------------------------------------------
|          2 |  173 |        ketchup |   2 |   pcs |
|          2 | 1130 |    corned beef |  80 | gramm |
|          2 | 1135 |         laurel | 0.8 | gramm |
|          2 | 1136 |          clove |   2 | gramm |
|          2 | 1138 |         tomato | 160 | gramm |
|          3 | 1139 |        mustard |  15 | gramm |
|          3 | 1140 |      fresh egg | 150 | gramm |
|          8 | 1144 |           salt | 3.4 | gramm |
|          5 | 1256 | spaghetti rinf | 375 | gramm |
|          8 | 1258 |            oil | 362 | gramm |

Here is SQLFiddle demo

edited Jun 12, 2013 at 8:51

answered Jun 12, 2013 at 8:42

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Wine Too Over a year ago

HI Peter, hard to belive but this seem's exactly what is needed. I will try to apply your query to my concrete schema/data. Thank you very much!

Wine Too Over a year ago

Sorry, It seem's that my PostgreSQL is a bit different (9.1 on windows 7) so with particular code I get set of errors: ERROR: syntax error at or near "name" LINE 1: ...code, f.food_code) code, COALESCE(i.name, f.name) name, SUM(... ERROR: column "f.food_code" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...LECT SUM(f.qty) used_times, COALESCE(i.ingr_code, f.food_cod... ERROR: column "f.name" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...(i.ingr_code, f.food_code) code, COALESCE(i.name, f.name) in...

Collectives™ on Stack Overflow

PostgreSQL, complex query for calculating ingredients by recipe

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related