Need help with a Dataview query

[a2jc4life]

Can anyone help me format a couple of Dataview queries? I’ve tried the simpler of the two 16 ways to Sunday and I can’t seem to figure out the right syntax to make it work.

I want to use two similar, but separate, queries, to spit out lists of recipes that contain particular ingredients. We’ll use apples as an example.

In both cases, I’d like it to list the recipe name (field is called “name”), source (field is called “source”), and the folder location, alphabetically sorted by the name – and preferably to not include the filename, which will duplicate things.

Query #1
This first query should be fairly simple, if I had any idea what I’m doing. I have a field called “main_ingredient”. I enter this manually for every file, so it’s formatted pretty consistently. (e.g. “apples” will always be “apples,” never “apple.” The one potential complication is that some are multiple words (e.g. “boneless chicken breasts”).

I just want this query to spit out a list (or table, if that’s what I need for multiple columns) of all the recipes that have main_ingredient “apples”.

This is about as far as I’ve gotten, but no matter what I adjust, I keep getting errors and can’t figure out what I’m doing wrong:

Query #2
The second query is a little more complex, and I’m not sure if it’s possible. The primary ingredients list for this database produces ingredients that look like this:

[0.5, 'cup, ‘apples’, ‘sliced’, ‘1/2 cup apples, sliced’]

The first set of single quotes are the numerical measurement, the next is the unit, the third is the actual ingredient, the fourth is any preparation instructions or other details, and the last is the version to be displayed to the user.

There are two potential issues to this query.

1. I only need it to check against the actual ingredient section of the ingredient entry – not the preparation, the display, etc. (Otherwise I think I might get two of everything?)

2. I need to find, for instance, any ingredient listed as “apple” or “apples” but not “applesauce”.

If someone can show me what these should look like, I should be able to identify how they work and adjust accordingly for other searches. But I’m apparently not understanding the documentation I’m reading.

[holroy]

It’s hard to tell where you’re having typos and what are errors. In query #1 the b image shows a lack of a double quote after "Category

For the second query, I don’t understand your recipe list, and basically what your recipes look like.

So, I would like to see an example of two of your actual recipes, properly formatted. One way to ensure that is to enclose the markdown (or query) with a line of four backticks before and after your text.

[a2jc4life]

Oops. That is a typo I had caught and fixed before, but it crept back in. It still doesn’t work with that corrected, though, so that’s not the problem. Here’s the YAML data for one of my recipes:

---
name: "Maple Apple Crisp"
source: "Rachel Ramey"
ingredients: 
  - [5.0, '', 'apples', 'peeled (opt.)  & sliced', '5 apples, peeled (opt.) & sliced']
  - [0.25, 'cup', 'maple syrup', '(opt.)', '1/4 cup maple syrup (opt.)']
  - [1.0, 'stick', 'butter', 'melted', '1 stick butter, melted']
  - [2.5, 'Tbs', 'maple sugar', '', '2 1/2 Tbs maple sugar']
  - [0.5, 'cup', 'fresh pastry flour or oat flour', '', '1/2 cup fresh pastry flour or oat flour']
  - [0.25, 'tsp', 'salt', '', '1/4 tsp salt']
  - [0.41666666666666663, 'cup', 'rolled oats', '', '1/3- 1/2 cup rolled oats']
scale: 1
photo_thumbnail: None
image_url: None
total_time: 
description: |
rating: 0
servings: [4]
main_ingredient: apples
tags: 
  - TnT-Dessert
  - Cobblers
  - Gluten-Free
  - Egg-Free
  - Sugar-Free
---

(The rest of the recipe I’ve output manually in the main text of the notes, which is why there are no instructions or anything. I wanted to keep things fairly minimal and only use YAML/dataview for things I’d actually want to query.)

I don’t know if I just kept having different typos or what, but I’ve updated to what I thought I was using before, and the main_ingredient query is working.

```dataview
TABLE without ID name as "Recipe Name", source as "Source", file.folder as "Category"
WHERE contains(main_ingredient, "apples")
SORT name ASC
```

I still need the other ingredient search, though – the more complex one that will search for any recipes that include ingredients “apple” or “apples” but not “applesauce”. I don’t know how to tell it to look in the right place within that ingredient entry (or if that’s even possible), and I can’t figure out how to format the Boolean search to include/exclude the right things.

[Craig]

This query worked in my vault to show just the pages that had ingredients of “apples”. The first FLATTEN splits out the ingredients list into one row per ingredient. The second FLATTEN pulls out the food name, which seems to always be in array position 2 (counting from zero, as we software developers often do ):

```dataview
TABLE FoodName
WHERE file.name = this.file.name
FLATTEN ingredients as Ingredient
FLATTEN Ingredient[2] as FoodName
WHERE FoodName = "apples"
```

(The WHERE file.name = this.file.name is just to narrow the search to the current page – you probably don’t want that line in your query.)

[holroy]

@a2jc4life

the more complex one that will search for any recipes that include ingredients “apple” or “apples” but not “applesauce”.

In the excellent answer by @Craig , he shows how to target a single ingredient with a given name. If you want to extend this to allow for matching against multiple variants of the ingredient names, there are two main approaches as I see it:

WHERE (FoodName = "apples" OR FoodName = "apple")
WHERE econtains(list("apple", "apples"), FoodName)

Either of these would do strict equality tests, and not let the “applesauce” slip by (either way). If you used only contains(list("applesauce"), FoodName), then an apple would also be a match. Could be useful, but most likely not if you really want to only check for apple(s).

Do you think it’s likely that you run into case issues? In that case (pun intended), you might want to try variant like the following:

WHERE econtains(list("apple", "apples"), lower(FoodName))

Same caveat as for contains() applies to it’s case insensitive sibling of icontains(), so I’d say it’s safer to use the lower() function.

[a2jc4life]

So if my ingredients are already all on separate lines, I don’t need the first “flatten” instruction, right? And the array position refers to the series of items within each ingredient? So if I have measurement, then unit, then the actual ingredient, I want that to be 3, yes?

[a2jc4life]

Thank you! Regarding case issues, I hadn’t thought about that yet, but maybe. In theory, no, because I always enter ingredients as lowercase unless they’re proper names, but sometimes download/export software handles them oddly. At worst, I suppose I could include a lowercase and a title-case version of each one.

What does the lower() function do? Is it similar to the flatten function Craig mentioned?

[a2jc4life]

Thank you both! I got it working. Basically. But I’m a little confused. I tested the flatten option with both 2 and 3 (as per my reply to Craig above). I get results both ways, but they’re different results – and I can’t tell why they’re behaving differently.

This recipe outputs into the list if I use 2:

ingredients: 
  - [0.875, 'cup', 'apple juice', '(3/4 c. + 2 Tbsp.)', '7/8 cup (3/4 c. + 2 Tbsp.) apple juice']
  - [0, '', '-concentrate divided', '', '-concentrate divided']
  - [6.5, '', 'apples', '(about 5 c.)  peeled cored,  and sliced', '6 -7 apples']
  - [1.0, 'tsp', 'cinnamon', '', '1 tsp cinnamon']
  - [2.0, '', 'Tbs tapioca flour', '', '2 Tbs tapioca flour']
  - [1.0, '', 'batch', '', '1 baked single pie crust or 1 batch']
  - [2.0, '', '-crust pie', '', '-pastry for 2-crust pie']

This recipe outputs to the list if I use 3:

ingredients: 
  - [0, '', 'raw carob powder', '', 'raw carob powder']
  - [2.0, 'cup', 'raw almonds', '', '2 cups raw almonds']
  - [40.0, '', 'pitted dates', 'soaked 1 hour and drained', '40 pitted dates, soaked 1 hour and drained']
  - [1.0, '', 'apple', 'cored and chopped', '1 apple']
  - [1.0, '', 'apple', 'thinly sliced and dehydrated at 115 for 2-4 hours', '1 apple']
  - [1.0, 'tsp', 'ground cinnamon', '', '1 tsp ground cinnamon']
  - [0.5, 'tsp', 'nutmeg', '', '1/2 tsp nutmeg']
  - [0.25, 'tsp', 'Himalayan salt', '', '1/4 tsp Himalayan salt']
  - [0, '', "Kelly's Macadamia Whipped Cream", '', "Kelly's Macadamia Whipped Cream"]

Here I’ve run them back-to-back, so you can see what I mean about getting different results. These two queries are identical, except that one flattens on the second and one on the third item in the ingredient. And as far as I can tell, they’re all formatted the same, so that makes no sense.

[holroy]

You always need the first flatten to split ingredients into separate ingredients. Otherwise you don’t know if you’re working with the first or the last ingredient.

The second flatten is more for convenience to not have to refer to array indices later on. It could be removed, but then you would need to use ingredient[2] instead of FoodName.

The array indices start on 0. So 0 is the count/measurement, 1 is the unit , 2 is the name, 3 is the modification(?), 4 is the collation/total.

[holroy]

@a2jc4life

What does the lower() function do? Is it similar to the flatten function Craig mentioned?

It should just lowercase it parameters.

Normally one can quality control the base data, aka recipes, but not the user input, aka the query input. Therefore one often applies some function, like lower(), to conform the input to match the existing data. Does that make sense to you?

[Craig]

@a2jc4life

So if my ingredients are already all on separate lines, I don’t need the first “flatten” instruction, right?

No, you still need the first FLATTEN, because it splits out each ingredient in the YAML into a separate row of your table. (It’s not directly related to them being on separate lines in your YAML.)

@a2jc4life

And the array position refers to the series of items within each ingredient? So if I have measurement, then unit, then the actual ingredient, I want that to be 3, yes?

You’re right, the array position refers to the items within each ingredient. However, the counting starts at zero, so the first item is [0], the second is [1], the third is [2], and so forth. It seems very strange, but that’s how it works.

[a2jc4life]

Yes @holroy , that makes sense. @Craig , that bit about starting the counting with 0 helps! Thank you both!