DataviewJS Table to summarize Nutritional Info

[jpfieber]

I’m tracking my nutritional information with an entry for each food item I consume using this format:
- [c] (meal:: Lunch): (item:: [[Bibigo Cooked Sticky White Rice]]) (1 Bowl) [cal:: 311], [fat:: 1.1], [carbs:: 71], [protein:: 6.1]

With the help of some CSS it is displayed something like this in my journal note:
🍴 Lunch: Bibigo Cooked Sticky White Rice (1 Bowl) cal = 311, fat = 1.1, carbs = 71, protein = 6.1

I’d like to include a table that summarizes everything using a dataviewjs script that I include at the bottom of every journal page. I’m imagining it would look something like this:

Meal	Calories	Fat	Carbs	Protein
Breakfast	423	15	35	17
Morning Snack	0	0	0	0
Lunch	640	31	80	12
Afternoon Snack	250	12	15	7
Dinner	750	21	45	25
Evening Snack	400	18	25	17
Total	2463	97	200	78

Ideally, if any of the 6 meals didn’t have any entries, it wouldn’t appear in the table.

I found a post at Dataview sum of all prices in table - #2 by smiggles that I thought I could adapt, as shown below, but I get a ‘Pages.map is not a function’ error:

```dataviewjs
let pages = dv.current()
let totals = pages.map((page, index) => [
   page.meal,
   dv.func.sum(page.cal),
   dv.func.sum(page.fat),
   dv.func.sum(page.carbs),
   dv.func.sum(page.protein)
]);
totals["values"][total.length] = [
   "**Total**",
   dv.func.default(dv.func.sum(pages.cal),0),
   dv.func.default(dv.func.sum(pages.fat),0),
   dv.func.default(dv.func.sum(pages.carbs),0),
   dv.func.default(dv.func.sum(pages.protein),0),
];
dv.table(["Meal", "Calories", "Fat", "Carbs", "Protein"], totals);
```

Am I on the right path? Any pointers to either get this script working, or an even better way of accomplishing my goal? I’m slowly understanding more DVJS with every project I tackle, but I’m still a long way from competent.

[NaraRaa]

let totals = pages.map((page, index) => [ does not make sense

1. index does not appear
2. .map is an array-only method, like [1, 2, 3].map(el => el + 1) will give you [2, 3, 4] as a result. dv.current() is an object I guess, you could dataview it to see the structure if needed.

list file
where file = this.file

[holroy]

The best way to do this is to split it up in two parts. First part is to build the table without the total, and second part is to use that query within a dataviewjs query to build the total and add that to the result of the previous result.

A similar approach has been used here:

How can I sum up multiple columns in a Dataviewjs query?

I’m running out of daylight, and got too much to do before going to bed, but check out the last query in the answer below. There I do sum up multiple columns and add them as a separate row under the other columns. Maybe it’ll help you on your way?

[jpfieber]

Thanks, very useful stuff. I used the links you included to get much further than where I was. I now have:

```dataviewjs
function highlight(value){
	return "**" + value + "**"
}
const result = await dv.query(`
TABLE WITHOUT ID
  item.meal as Meal,
  item.cal as Cal, 
  item.fat as Fat, 
  item.carbs as Carbs, 
  item.protein as Protein
WHERE file = this.file
FLATTEN file.tasks as item
WHERE contains("[c]", item.status)
`)
if(result.successful){
	let calsum = 0
	let fatsum = 0
	let carbssum = 0
	let proteinsum = 0
	dv.array(result.value.values)
	  .forEach(v => {
		calsum += v[1]
		fatsum += v[2]
		carbssum += v[3]
		proteinsum += v[4]
	})
	result.value.values.push([highlight("Total"), highlight(calsum), highlight(fatsum), highlight(carbssum), highlight(proteinsum)])

dv.table(result.value.headers, result.value.values)

} else
  dv.paragraph(`~~~~\n${result.error }\n~~~~\n`)
```

Which provides something like this:

So I’m getting the totals for the day, but it’s still a list of individual items. I’m struggling understanding how to par the list down to just one meal (WHERE item.meal = "Breakfast" doesn’t work, not that I’d know what to do after that), much less be able to subtotal all the meals and then provide a total. Any other tips to try and do that part?

[jpfieber]

I did more testing and was able to accomplish my goals! Here’s the code I ended up with, pure DVJS, no embedded Dataview (not sure if that’s good or bad when it comes to performance):

~~~dataviewjs
function highlight(value){
	return "**" + value + "**"
}
function getsums(result){
	let calsum = 0
	let fatsum = 0
	let carbssum = 0
	let proteinsum = 0
	dv.array(result.values)
	  .forEach(v => {
		calsum += v[1]
		fatsum += v[2]
		carbssum += v[3]
		proteinsum += v[4]
	})
	summary.push([highlight(result.values[0][0]), Math.round(calsum*10)/10, Math.round(fatsum*10)/10, Math.round(carbssum*10)/10, Math.round(proteinsum*10)/10])
	return summary
}

function buildarray(meal){
	const result = dv.current()
	  .file.tasks
	  .where(t => t.status == "c" && t.meal == meal)
	  .map(e => {return [e.meal, e.cal,e.fat,e.carbs,e.protein]})
	if(result.length > 0){getsums(result)}
}

let headers = ["Meal", "Calories", "Fat", "Carbs", "Protein" ]
let summary = []
let meals = ["Breakfast","Morning Snack","Lunch","Afternoon Snack","Dinner","Evening Snack"]

meals.forEach((meal) => {buildarray(meal)})

let calsum = 0
let fatsum = 0
let carbssum = 0
let proteinsum = 0
dv.array(summary)
  .forEach(v => {
	calsum += v[1]
	fatsum += v[2]
	carbssum += v[3]
	proteinsum += v[4]
})

summary.push([highlight("Total"), highlight(Math.round(calsum*10)/10), highlight(Math.round(fatsum*10)/10), highlight(Math.round(carbssum*10)/10), highlight(Math.round(proteinsum*10)/10)])


dv.table(headers, summary)
~~~

Thanks for the tips @holroy. I’m quite pleased with the results, but if anyone sees a more efficient way of accomplishing the same, let me know!

[holroy]

I’ve not looked too much into this, been a busy period for me lately, but couldn’t you accomplish much of the same using GROUP BY meal queries and sum(rows.cal) ?