Using to query with dataview

What I’m trying to do

I am creating a database of foods and dishes consisting of foods to (sub)query data about the nutrients and prices of my dishes. I use the following structure (which is in german).
The raw data are the foods and nutritional stats that I collect in notes like this:

Then in notes for the dishes i query the data in this form:

round(number(Calories * (number(Tomatensoße)/100)),2) AS Kalorien,
round(number(Protein * (number(Tomatensoße)/100)),2) AS Protein,
round(number(Price * (number(Tomatensoße)/Amount)),2) AS Preis,
Tomatensoße AS Menge

The remaining manual steps I now have are 1) that i have to calculate the sums (of the nutritional values) of the dishes with a calculator while I would like to install a grouping/sum in my dishes template. Thats something I can fix myself I guess. 2) Secondly, I need to fill in the name of the dish i want to query on on my food notes manually. In this case it is “Tomatensoße” = tomato sauce.

Things I have tried

Simply using or just shows the according file names in my dataview. What i need is first the extraction of and then using this variable to look it up on the notes i am searching on. I don’t know if this will lead to a feature request or if there is a simple functional or architectural fix. I am happy for any hint!

Thank you! :slight_smile:

I’m a little uncertain on how you type up your recipe, so I can’t give a proper query, but I can answer some of your questions, I think.

When you do a TABLE query each row corresponds to a file, in your case an ingredient, and you can get that file’s properties by typing the property name directly. So give a file list of the correct ingredients, you should be able to just do Calories to get the calory count of that ingredient.

To access the current files property, you need to prepend this.. So with the examples you’ve given, we could have written this query:

TABLE this.Calories, Calories
WHERE = "Karotten"

And it would return a one line table with the values 2269,6 in the first column, and 27 in the second column. If you add more rows, the value in the first column would remain constant.

Regarding the unclear part, I’m unclear on how in your “Tomatensoße” file how you declare how many carrots you need, and potentially how many portion of sauce you’re preparing. These two number I reckon should/would affect what you want as output.

I’m wondering whether your properties should be handled slightly different in order to get better output. I’m thinking something along the lines of:

- name: "[[Karotten]]"
  amount: 10
- name: "[[Tomaten]]"
  amount: 100
- name: "[[Zviebelen]]"
  amount: 10

(The amounts are just made up, I don’t know how much is needed in your recipe :slight_smile: ) The amount corresponds to whatever unit you’ve used in the ingredient definitions, so if you’ve listed calories for a 100 gram, and the recipe calls for 200 gram, you’d enter the amount of 2. (This you could of course correspond to some proper units for each ingredient. Like you might want to list for 1 gram in the ingredient definition, so you could list 200 to denote 200 gram. Similar you could list in the definition the values for one piece of that ingredient, and use amounts to denote how many pieces. Like if you wanted to use 5 eggs )

Hi holroy, at first: thank you very much for your answer. I see that my original post might have been a little confusing/missing information.
But what I already constructed with the foods and recipes is very similar to what you suggested. I’ll post again an english and simple example, then my remaining question.

  1. First, i got 1 to n “food” Notes (for which i have a standard template). I manually fill those food notes with everything that is yellow, i.e. the nutritional values and the amounts in gram that are used in each recipe, like you said.

  1. Secondly, i got the notes (with templates) for my recipes/dishes. What i fill out manually there are the yellow properties of the whole recipe (which i use in other dataviews later, e.g. to compare/overview the recipes). So far i think i can’t automate this step cause there is no subquery concept in dataview.

I already precalculate everything i want to know on the “recipe-level” using these two dataview queries:

table without Id sum(rows.Calories) AS Calories,
round(sum(rows.Protein),2) AS Protein,
round(sum(rows.Price),2) AS Price
flatten round(number(Calories * (number(Recipe1)/100)),2) AS Calories
flatten round(number(Protein * (number(Recipe1)/100)),2) AS Protein
flatten round(number(Price * (number(Recipe1)/Amount)),2) AS Price
group by Type
round(number(Calories * (number(Recipe1)/100)),2) AS Calories,
round(number(Protein * (number(Recipe1)/100)),2) AS Protein,
round(number(Price * (number(Recipe1)/Amount)),2) AS Price,
Recipe1 AS Menge

The last remaining manual step I want to eliminate is that each time i do create a new recipe, i need to replace “Recipe1” (or my general placeholder) in the dataview queries.

Therefore my question, to which also the title relates, is, if there is a possibility to use the name of the recipe file and then use this within my dataview query to refer to the property “recipe” of the food notes? Precisely, i would like to use the outcome/variable of to query on other notes.

Let’s do a simple case study. Our recipe is “Egg and bacon”, which only uses “Egg” and “Bacon”. No seasoning or any other stuff. Lets keep it simple. If I understood you correctly this would result in these files:

  • “Egg” - A food/Lebesmittel (from now on called ingredient) note also with references to “Egg and bacon” and some amount
  • “Bacon” - A ingredient note also with references to “Egg and bacon” with an amount
  • “Egg and bacon” - A recipe file, with no references to which ingredients it actually consists of… But it’s listed by text from the ingredients file…

Change recipe structure?

To me this structure seems backwards. I’d much rather have the ingredients listed in the recipe, instead of listing the recipes it is used in at each and every ingredient. With current structure when you add another recipe you need to edit every single ingredient of that recipe, and if you change the recipe you would also need to remember to remove/add it to those ingredients being changed.

To me a better structure would look something like:

type: recipe
- name: "[[Egg]]"
  amount: 2
- name: "[[Bacon]]"
  amount: 5

And then use that in a query to calculate all the other stuff. Just for the sake of it with some random calories noted in the ingredients file, I could use this query:

TABLE WITHOUT ID as Ingredient,
  i.amount as Amount, * i.amount as "Calories" 
WHERE file = this.file
FLATTEN ingredients as i

With an output of:

Using text recipe names in ingredients

Now onto what you really asked about, which wasn’t my ramblings on a better structure. You’d need to loop through every ingredients in your vault, expand the file lists into recipes, and then pick out those matching your current recipes. In query form this look like this:

TABLE WITHOUT ID as Ingredient,
  amount as Amount, 
  amount * calories as Calories 

WHERE type = "Lebensmittel"
FLATTEN file.lists as recipe
WHERE startswith(recipe.text,
FLATTEN recipe[] as amount

Here we use the [ ... ] to pick out a given field from an object, since we most assuredly do have space and what not in the recipe name.

Change to links for the recipes?

Another note regarding this structure, sorry if it’s too much comments on that, but as long as you don’t have the recipes linked from the ingredients, if you decide to change the name of a recipe you’ll have to manually go through your ingredients list and correct the recipe name. Sadly you can’t just switch to it being a link either, as then it can’t be used as an inline field.

You could potentially use something like:

- [[Boiled egg]] [amount:: 1]

Which would require this change to the ingredients query:

TABLE WITHOUT ID as Ingredient,
  amount as Amount, 
  amount * calories as Calories 
FROM [[]]
FLATTEN file.lists as recipe
WHERE recipe.amount AND contains(recipe.outlinks,
FLATTEN recipe.amount as amount

This has the following advantages over the middle query:

  • It only queries on files linked to this recipe, so it doesn’t run through the entire ingredients list
  • It allows for recipes to be renamed, where Obsidian will change all occurrences of the link automatically

My summary thoughts

In summary, I think it would be better to switch around so that the recipe holds the ingredients, instead of the ingredients having the recipes. It just makes more sense in my head.

If you continue with the ingredients having the recipes, I would strongly suggest to switch to a syntax similar to: - [[recipe 1]] [amount: 3] to allow for better handling of recipe renames and simpler queries without having to scan all of the ingredients.

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.