A prototype of a simple service for healthy eating.

In the context of the article, the picture acquires a double meaning.

Disclaimer

In society, in principle, a lot of "superficial" regarding the "right" food, "diets", "lifestyle" and others. As a rule, these are simply manifestations of a relatively high level of wages in a particular region and a low level of literacy of people. My girlfriend and I set a very simple task for ourselves - to eat tasty, simple, cheap, correct and fast.

And it so happened that when choosing a tool for planning such a power, the eyes fell on PostgreSQL, which is on the home server. Similarly, you can easily do it in Excel or Google-tables, but in our case SQL turned out to be faster + there are open databases with ready data. This article can be considered by you as a “stock” for a similar SQL service or simply as an idea that you can take and apply for yourself.
Also pay attention - in order to use this to the full, you should at least be able to use a tabular processor (Excel).

Groups of people to whom the article “will enter”:

• People who know SQL at least at the level of the simplest queries (if you are a DBA - some simplifications are made here to save time, and not for the reason you thought about);
• People who want to control what they eat;
• People who want to eat are tasty, inexpensive, simple and healthy;
• People who want to try to dig into a SQL server or are simply confident in using this tool;

Groups of people who will not be "entered" article:

• Why does the function simply return a query? It should be 100 times harder!
• So the schemes only do ... / I eat everything that is horrible / why SQL - let's do it in general / who formats queries and so on;
• Cooking is not a lordly affair!
• It is necessary to normalize everything for another 10 rows!

Introduction:

It so happened that for some time I lived in Moscow. Moscow (in my world), as a rule, is characterized by:

The low quality of the available food or the high price of a little less affordable;
The total rubberiness of the products from the store (if you have been to Cyprus at least once - you will understand);
Lack of ways to eat at the same time correctly and cheaply and tasty without any efforts (I’m not talking about marketing services with a margin of 50-60%, where you still need to prepare);
The presence of markets where, in principle, everything is available at normal prices, but in the "raw" form;

TLDR for the impatient and advanced:

• Download a dump of the PostgreSQL database (9.5+) of the database by reference and unpack ;
• If you arrange presets that are there, then just type in the query:
  

  SELECT * FROM get_random_menu() 

• The request will give you json (if you’ve read it up here, you can easily change it to the format you need or pull the request with some driver) from the menu for a week (7 days) from the calculation:
• 2 adults - a man and a woman (recommended_daily_intake);
• Calculating the amount of food is simply an extrapolation through calorie content (why is described below);
• • Food 4 times a day
  
  
• • Multi-course breakfast;
  
  
• • Dinner;
  
  
• • Dinner;
  
  
• • Snack in the evening;
  
  
• The menu is selected randomly with each request;
• If you are not satisfied with the presets or you need to change the number of people (here it is set for a man and a woman with average needs):
• The function get_random_menu () will tell you what to change;
• Basic tables that are worth adjusting to themselves
• • recommended_daily_intake;
  
  
• • All tables containing the word dish;
  
  
• • The structure is a little complicated, but it will become clear if you read requests;
  
  

Result:

For this reason, we decided to make a mini-service for ourselves, where we brought simple dishes that we like. In general, we tried to work with the USDA base - but it turned out to be excessively complex. For this service, we tried to buy food for ~ 10 weeks , and found out from experience that :

• The algorithm had different bugs with proportions that we corrected (15 kilograms of sauerkraut a week is strong);
• On average, a week for 2 people takes about 4,000 rubles (!) + 1.5-2 hours to buy + 30-40 minutes for two people to cook daily. In a month it turns out around 8 000-10 000 rubles per person;
• The girl also significantly lost weight (a nice bonus);
• Both stopped spending money on food in general. And for lunch too;
• The most convenient format for working with the database - unloaded through any available program to the table, made a pivot table, uploaded to the phone;
• The easiest way to mark cooking is from the phone / computer in the table;
• Simple and tasty food and huge time savings on planning (10 hours max. On the development of this allows you to live almost 3 months without thinking about food);

In general, it may sometime reach the hands of such (a description of how to turn an algorithm into an application and a product ), but for now communication with colleagues and the market suggest that the "rich" will not cook (they will rather pay a margin of 50-80% to services) and the "poor" in Russia will not pay for the application.

Why "naive" calorie optimization? Why not squirrels?

Because it works, but there is still no mechanism for an ideal measurement of needs (or we do not know it).

The get_random_menu () function also gives perfect intake of calories, protein and carbohydrates - you can compare with your hands. I tried to apply linear and non-linear optimization algorithms in Python (I generated 10,000 menus randomly, tried to improve the weights for the “perfect” fit, didn’t achieve results in an hour or two, left), but most likely because of our set of dishes there is not it is possible to have a 100% hit - on average, proteins and carbohydrates are 15-20% less than the "ideal".

Description of the technical component, the base and functions:

In general, filling in the data structure and writing functions took about 3-4 hours on tables and 2-3 hours on functions and is well expressed by the ER scheme:

Note that:

• It is better to use PostgreSQL 9.5+, since uses several functions for working with json, which not so long ago appeared;
• There is the notion of a dish (dish), there is a notion of what meals it can be taken in (dish_serving_choice, dish_serving);
• dish_menu - an example of the log of purchased food, then it turned out to be easier to collect in Excel
• Not every dish can be eaten, for example, for breakfast there is dish_type;
• Meals consist of ingredients (dish_ingredient), but do not contain water (there are no calories in it - optimization will not work) - all proportions are calculated taking into account this fact;
• In fact, we made several assumptions:
• Calorie intake ratios for meals (dish_serving_choice);
• Calorie required (below);
• The composition of dishes (dish_contents);

There are also 2 tables describing people's needs, their modification is required to change the number of people.

The easiest way to understand (if you know SQL) is how it all works by looking at a couple of basic functions.

getPrimitiveMenu

• Basic function which simply creates a random menu;
• Creates a random menu for 7 days;
• Randomization is essentially done through ORDER BY random ();
• We create a week with a simple unnest construction (ARRAY [1,2,3,4,5,6,7]);
• The rest is trivial;

 CREATE OR REPLACE FUNCTION "usda28"."getPrimitiveMenu"() RETURNS SETOF "pg_catalog"."record" AS $BODY$ BEGIN RETURN QUERY SELECT raw_data1.week_day ::INTEGER as week_day, raw_data1.meal_order :: INTEGER as meal_id, raw_data1.meal :: VARCHAR as meal, raw_data1.balance ::NUMERIC as dish_share, raw_data1.dish_type :: VARCHAR as dish_type, d.title :: VARCHAR as dish_title, d.deliciousness :: INTEGER as dish_taste, dc.portion :: NUMERIC as proportion, di.id::INTEGER as dish_ingredient_id, di.title ::VARCHAR as di_title, di.calories :: INTEGER as calories_per_100, di.carbs :: INTEGER as carbs_per_100, di.fat :: INTEGER as fat_per_100, di.protein :: INTEGER as protein_per_100 FROM ( SELECT dsc.calorie_balance as balance, ds.title as meal, dsc.dish_serving_id, dsc.choice_id, ds.id as meal_order, dt.title as dish_type, presets.week_day as week_day, ( SELECT d."id" FROM usda28.dish d /* Checking that the dish has actual ingredients, otherwise errors are possible */ JOIN usda28.dish_contents dc ON dc.dish_id = d."id" WHERE d.dish_type_id = dsc.dish_type_id ORDER BY random() LIMIT 1 ) as dish_id FROM ( SELECT servings_count.dsc_id as dsc_id, trunc(servings_count.choice_count * random() + 1)::INTEGER as preset_choice, unnest(ARRAY[1,2,3,4,5,6,7]) as week_day FROM ( SELECT DISTINCT dsc.dish_serving_id as dsc_id, COUNT(DISTINCT dsc.choice_id) as choice_count FROM usda28.dish_serving_choice dsc GROUP BY dsc.dish_serving_id ) servings_count ORDER BY unnest(ARRAY[1,2,3,4,5,6,7]) ) presets JOIN usda28.dish_serving_choice dsc ON dsc.choice_id = presets.preset_choice AND dsc.dish_serving_id = presets.dsc_id JOIN usda28.dish_serving ds ON ds."id" = dsc.dish_serving_id JOIN usda28.dish_type dt ON dt."id" = dsc.dish_type_id ORDER BY presets.week_day ASC, dsc.dish_serving_id ASC ) raw_data1 JOIN usda28.dish d ON d."id" = raw_data1.dish_id JOIN usda28.dish_contents dc ON dc.dish_id = d."id" JOIN usda28.dish_ingredient di ON di."id" = dc.ingredient_id ORDER BY raw_data1.week_day ASC, raw_data1.meal_order ASC, d.title ASC; END $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100 ROWS 1000 ; 

get_random_menu

• A function that weighs the menu by calories and gives a random menu;
• If you do not know about the window functions - read;
• In essence, the function takes what the previous function did + considers the number of calories as a subquery (...) nut;
• The conventionally complex formula is essentially just a school proportion;

 CREATE OR REPLACE FUNCTION "usda28"."get_random_menu"() RETURNS "pg_catalog"."json" AS $BODY$ SELECT to_json(array_agg(a)) FROM ( SELECT (SELECT md5(''||now()::text||random()::text) as menu_uuid), raw_data.week_day as week_day, raw_data.meal_id as meal_id, raw_data.meal as meal, raw_data.dish_type as dish_type, raw_data.dish_title as dish_title, raw_data.dish_ingredient_id as dish_ingredient_id, raw_data.ingredient_title as ingredient_title, raw_data.dish_share, raw_data.proportion, raw_data.calories_per_100, raw_data.carbs_per_100, raw_data.fat_per_100, raw_data.protein_per_100, trunc( raw_data.proportion * raw_data.dish_share * raw_data.calories * 100 / SUM(raw_data.stat_weight) OVER (PARTITION BY raw_data.week_day, raw_data.meal_id, raw_data.meal, raw_data.dish_type, raw_data.dish_title ) )as grams_guesstimate FROM ( SELECT menu.week_day as week_day, menu.meal_id as meal_id, menu.meal as meal, menu.dish_type as dish_type, menu.dish_title as dish_title, menu.dish_ingredient_id as dish_ingredient_id, menu.di_title as ingredient_title, menu.dish_share, menu.proportion, menu.calories_per_100, menu.carbs_per_100, menu.fat_per_100, menu.protein_per_100, nut.calories, menu.proportion * menu.calories_per_100 as stat_weight FROM ( SELECT week_day, meal_id, meal, dish_share, dish_type, dish_title, dish_taste, proportion, dish_ingredient_id, di_title, calories_per_100, carbs_per_100, fat_per_100, protein_per_100 FROM usda28."getPrimitiveMenu"() ) menu JOIN ( SELECT SUM (rdi.carbs) * mlp.proportion as carbs, SUM (rdi.fat) * mlp.proportion as fat, SUM (rdi.protein) * mlp.proportion as protein, SUM (rdi.calories) * mlp.proportion as calories, ml.title as meal_title, ml."id" as meal_id FROM usda28.recommended_daily_intake rdi JOIN usda28.activity_types atp ON atp."id" = rdi.activity_type_id AND atp."id" = 1 JOIN usda28.meal_proportions mlp ON 1=1 JOIN usda28.dish_serving ml ON ml.id = mlp.meal_id GROUP BY ml.title, mlp.proportion, ml."id" ) nut ON nut.meal_id = menu.meal_id ) raw_data ORDER BY raw_data.meal_id ASC, raw_data.week_day ASC, raw_data.dish_type ASC ) a $BODY$ LANGUAGE 'sql' VOLATILE COST 100 ; 

If you like it - write in a personal.