I have a collection of notes of the Type:: Food and added infos like Calories:: 100 Fat:: 50 etc. They have individual names like Food_1.
I want to create a second collection of notes of the Type::Dish where i flexibly add ingredients with values as amounts like Food_1:: 5 Food_2: 3.
With dataview, i’d like to sum up or calculate the nutrition infos for the whole dish. Generally this would be a calculation of Amount(Food_1) * Info(Food_1) where the amounts are supposed to be collected in the Dish-Notes and the Infos in the Foods notes.
What I tried
Asked ChatGPT, looked in the docs and tried a lot of stuff with my current knowlege, but didn’t come up with a function that serves my purpose of retrieving this exact information. The calculation part might not be hard, but how do i query the key::value pairs of my current note and then retrieve infos that are in (the values of) the key-notes (food) to recalculate them with the values of the main (dish) note? I need some kind of subquery approach.
The tricky part of such a query, when using Dataview at least, is to separate out which fields are actually food items, and which might be something else. So therefore I’m going to propose a slightly different syntax, which will make the query easier to work with.
---
type: Dish
---
Required food items
- (amount:: 5) [[Food_1]]
- (amount:: 3) [[Food_2]]
## Nutritional info
### All the information
```dataview
TABLE WITHOUT ID food.file.name AS "Food item",
item.amount as Amount,
food.calories as "Item calories",
food.fat as "Item fat",
calories as "Total calories",
fat as "Total fat"
FLATTEN file.lists as item
FLATTEN item.outlinks[0] as food
FLATTEN item.amount * food.calories as calories
FLATTEN item.amount * food.fat as fat
WHERE file.path = this.file.path
```
### Summed information
```dataview
TABLE WITHOUT ID sum(rows.calories), sum(rows.fat)
FLATTEN file.lists as item
FLATTEN item.outlinks[0] as food
FLATTEN item.amount * food.calories as calories
FLATTEN item.amount * food.fat as fat
WHERE file.path = this.file.path
GROUP BY true
```
It’s only the latter query which is really needed, but I give you the first query as well to look into what’s the base data for the second query.
The concept is that each list item holds one ingredient/food item, and that the first outgoing link from that item points to the food item itself. We flatten these items, and for each info part you need, you need to add another line similar to the lines for ... as calories and ... as fat.
In the final query we then simply call the sum() functions of the rows.calories (or similar) to tally up the overall totalt for that piece of information. Note that this query intentionally limits to run related to just the current file. If you want to query the nutrition info for multiple files you should change the WHERE/GROUP BY lines to something like:
WHERE type = "Dish"
GROUP BY file.link
And potentially consider doing an ordinary TABLE, and not TABLE WIHTOUT ID which makes more sense on the actual dish note.
Wow thanks a lot, that solves it. You understood the request and the main problem perfectly and the suggestion works too. I still need to understand the syntax, thanks to which function the “( )” around the amount are necessary. Also, I think I need an auto-refresh on/off shortcut, since this operation seems to be a bit more computationally sophisticated after all.
But it solves my problem quite wonderfully, thanks!
