I have some frontmatter structured as
serves: 0
time:
prep: 0
cook: 0
ingredients:
-
name: eggs
qty: 2
-
name: bacon
qty: 5
I also tried structuring the data as
serves: 0
time:
prep: 0
cook: 0
ingredients:
- {name: eggs, qty: 2}
- {name: bacon, qty: 5}
I’m trying to return a flat list of all items in the ingredients array to display as a flat list like:
How would I go about combining the ingredients array into a flat list?
How far have you gotten? Do you have a query which partially do what you want?
Have you written any Dataview query before? That’s a hint, that you most likely need the Dataview plugin for this.
Yes, I have dataview and wrote a list query that returns all ingredients, but I only got so far as to have it return:
This is with the query:
LIST WITHOUT ID
ingredient
Using ingredient.name is closer with:
But whenever I use string( ingredient.qty ) + ingredient.name it returns 2,5 egg,bacon
… sorry for leaving that out, I figured it was just a very basic query statement
but the source (the note link) isn’t relevant to the result?
Right now I’m just setting up a template. I’m going to use it to update ingredients on the same note and create a shopping list using links in the current note.
For now I only have two notes with the relevant front matter to test the query, and I’ve already figured out how to select the sources in the two different contexts separately once I solve this core need.
two queries to test: a list and a table (with different ways but both possible in each type)
LIST WITHOUT ID ING
WHERE ingredients
FLATTEN map(ingredients, (i) => i.qty + " " + i.name) AS ING
TABLE map(ingredients, (i) => i.qty + " " + i.name) AS Ingredients
WHERE ingredients
The thing to think when your data is in a list, is either to use map() like @mnvwvnm opted for, or use flatten to split your list into its separate parts.
```dataview
list without id item.qty + " " + item.name
flatten ingredients as item
```
No problem, but if more people included it, it would make it a lot easier for us to help, as we get to see how you’ve thought about the problem, or even if you even bothered to think about it, or just want us to do your work. I just wish more people included their attempts right from the start, but …
It’s almost working. The following code works perfectly when the source is only one file:
LIST WITHOUT ID cleanedItem
WHERE file.name = this.file.name
FLATTEN ingredients AS item
FLATTEN join(nonnull(item.qty, item.unit, item.name, item.optional), " ") AS cleanedItem
I used join to filter out any null values and combine them into one line. However, when I start combining ingredients from different files like:
LIST WITHOUT ID cleanedItem
FROM outgoing([[]])
FLATTEN file.frontmatter.ingredients AS item
FLATTEN join(nonnull(sum(rows.ingredients.qty), rows.ingredients.unit, rows.ingredients.name), " ") AS cleanedItem
GROUP BY item.name AS groupedItem
I’m only getting:
The parts of the code work independently just fine. For example:
LIST WITHOUT ID cleanedItem
FROM outgoing([[]])
FLATTEN file.frontmatter.ingredients AS item
FLATTEN join(nonnull(sum(item.qty), item.unit, item.name), " ") AS cleanedItem
is returning
and when I’m grouping items:
LIST WITHOUT ID groupedItem
FROM outgoing([[]])
FLATTEN file.frontmatter.ingredients AS item
GROUP BY item.name AS groupedItem
returns:
Grouping items seems to stop me from accessing the item data. I’ve tried using rows.qty and rows.frontmatter.qty and neither seem to work.
It’s late here. I can look to this tomorrow. But for now just one point: order matters. This means: after FROM , every command is sequential. And the first line of the code (where in LIST or TABLE you define what you want to see… the output) is the last thing to consider.
This to say
You can’t use the prefix rows. before the GROUP BY command, only after!
Didn’t you say you only wanted ingredients from one file at a time, and that you had it figured out how to differentiate the files?
You now need to specify how and why you want to combine ingredients, because it’s unclear what your trying to achieve. What is your end goal with this query?
Sorry if that wasn’t clear. There are two separate lists with similar usage. One is an ingredients list for a single recipe and the other is a shopping list that combines the lists from a meal plan.
The single recipe list works fine using file.name = this.file.name, but I’m having trouble with the meal plan list using outgoing([[]]) as a source.
I’m having trouble summing the qty from the files as one entry. I’ve done a similar thing in the past using sum(rows.cost), but that only seems to work if I have one “cost” per file.
As far as I understand, rows include everything from the files that are grouped, but summing the rows.ingredients.qty seems to undo the grouping and return all of the ingredients. For instance, I tried using
TABLE WITHOUT ID test
FROM outgoing([[]])
FLATTEN file.frontmatter.ingredients AS items
FLATTEN items AS item
GROUP BY item.name AS name
FLATTEN rows.ingredients AS test
and it returned
I also tried filtering to select only the ingredient with the name, but it returned the same as the list above. See example below
GROUP BY item.name AS name
FLATTEN filter(rows.ingredients, (n) => containsword(n.name, name))
Let me use this as a base for getting to your final result. Please expand one section at a time, and look through each of to get a greater understanding for how to deal with something like this.
What I tend to do when not sure on what to do is add more columns to my table. So change the first line into TABLE WITHOUT ID test, items, item, but since we also added a GROUP BY statemens, I know there is going to be some rows, so lets add rows.item, rows.items as well. This gives us this result:
Lets analyse this image:
test column is still combining all the stuff. Don’t want that.item and items has somehow lost any value (Secret: It’s due to the group by statement). Don’t like thatrows.item and rows.items seems to be the same, and they actually show all the different items in separate rows. PromisingNext step, lets remove the line with test, and ditch the item and items column, and let us also loose the FLATTEN ... item line (and with that goes the duplicate of rows.item. The query now looks like:
```dataview
TABLE WITHOUT ID name, rows.items
FROM outgoing([[]])
FLATTEN file.frontmatter.ingredients AS items
GROUP BY items.name AS name
```
And it outputs the following:
If only there was a way to sum the quantities. And someone whispered in my ear that there is no need for the file.frontmatter to get the field values.
So lets add those, and cleanup the names a little:
```dataview
TABLE WITHOUT ID name, sum(rows.item.qty)
FROM outgoing([[]])
FLATTEN ingredients AS item
GROUP BY item.name AS name
```
Which is looking a lot like christmas (or something like that):
So a final version using LIST instead of table, and combining the columns:
```dataview
LIST WITHOUT ID sum(rows.item.qty) + " " + name
FROM outgoing([[]])
FLATTEN ingredients AS item
GROUP BY item.name AS name
```
Which gives this output:
I might have gotten a little carried away, but these steps are quite close to what I actually did based on your original query which had all the information, but it was just a little hidden and convoluted. Hope this helps!
The problem is solved! Thank you for all the help, that troubleshooting breakdown was especially helpful. I’ll have to read more about how rows work, that was my main source of confusion.
For anyone interested, here is the final code:
Frontmatter Format
---
ingredients:
- {name: "Ingredient", qty: 0, unit: pound, optional: (optional)}
---
The on-page ingredients
```dataview
LIST WITHOUT ID cleanedItem
WHERE file.name = this.file.name
FLATTEN ingredients AS item
FLATTEN join(nonnull(item.qty, item.unit, item.name, item.optional), " ") AS cleanedItem
```
The Combined shopping list
```dataview
LIST WITHOUT ID cleanedItem
FROM outgoing([[]])
FLATTEN ingredients AS item
GROUP BY item.name AS name
FLATTEN rows.item.unit AS thisUnit
FLATTEN join(nonnull(sum(rows.item.qty), thisUnit, name), " ") AS cleanedItem
```
I used join with nonnull to remove null values, as not all items will have a unit or a quantity.
I still have no idea why rows.item.unit is returned as an array, but flattening it solved the issue (again, thanks to @holroy for the troubleshooting tips).
It’s due to the group by statement, which groups (aka making arrays) out of all the other values when the name (or group by element) is equal. So the name is just one, but qty, unit and optional are arrays, even though stuff like unit usually would be the same.
Just a little caveat related to this solution, if the unit changes from teaspoon to tablespoon to … it will fail. Or rather it’ll happily add the quantities together with no respect for the units.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.