Help identifying companion plants with dataview?

[flatline]

Things I have tried

I have searched both the forums and the help files on the dataview plugin. Intuitively I want to say that what I am doing is possible, but I haven’t figured it out yet.

Previous attempts include:

list
from #plantae and -"templates"
where companion.deters = any(this.conditions.vulnerable_to)

(returns no results)

and

list
from #plantae and -"templates"
where any(companion.deters) = any(this.conditions.vulnerable_to)

(results any file in which companion.deters is not empty - not actually sure why)

and

list
from #plantae and -"templates"
where contains(companion.deters,any(this.conditions.vulnerable_to))

(returns no results)

and

where filter([this.conditions.vulnerable_to], (x) => contains(companion.deters,x))

Returns results that do not even include the YAML keys I am querying.

What I’m trying to do

I am putting together a small compendium of plants and herbs. I would like to use dataview to show the interrelationships between these plants. I could solve this by writing the dataview query manually in every file, e.g.

list
from #plantae and -"templates"
where contains(companion.deters, "aphids") or contains(companion.deters, "some other pest")

but I would then have to update the query manually every time, for every plant. For a plant like broccoli, the query would also become incredibly long, as it has many pests it suffers from and many plants it works well with. I am wondering if there is a more scalable solution where I compare the contents of the vulnerable_to array in this file, to the contents of the deters array in other files, so that I can write the query once in my master template and have it run dynamically based on the YAML of the file in which it’s being executed.

Truncated YAML for the broccoli file below:

---
aliases: [Brassica oleracea]
kingdom: plantae
type: vegetable
lifecycle: biennial
conditions:
  vulnerable_to: [slugs, snails, birds, clubroot, caterpillars, aphids]
companion:
  traps: 
  deters:
  attracts:
  assists: [lettuce, swiss chard, radishes]
  inhibits: [tomatoes, pole beans, strawberries]
---

(I have included aphids as a test)

Truncated YAML for the Thyme file which it should, in theory, retrieve:

---
companion:
  deters: [cabbage worms, cabbage moths, cabbage loopers, beetles, aphids, blackflies]
---

Note that if I change the vulnerable_to field to only one value, e.g. “aphids” and then run the following query, it works perfectly:

---
conditions:
  vulnerable_to: aphids
---

list
from #plantae and -"templates"
where contains(companion.deters,this.conditions.vulnerable_to)

However, when I then change the vulnerable_to field back to an array, the condition above returns all sorts of unrelated results. Is there a way to write the above function so that it loops through all the keys in the vulnerable_to array?

If anyone has tips or suggestions they would be very much appreciated!

Thank you

[flatline]

Update: I have now written an inelegant working version of this query. Essentially, it assumes a maximum array length of 10 and checks whether each item is null. If it is not, it will do a contains query for that item and append it to the results:

list
from #plantae and -"templates"
WHERE
choice(this.conditions.vulnerable_to[0] != null, contains(companion.deters,this.conditions.vulnerable_to[0]),"") OR
choice(this.conditions.vulnerable_to[1] != null, contains(companion.deters,this.conditions.vulnerable_to[1]),"") OR
choice(this.conditions.vulnerable_to[2] != null, contains(companion.deters,this.conditions.vulnerable_to[2]),"") OR
choice(this.conditions.vulnerable_to[3] != null, contains(companion.deters,this.conditions.vulnerable_to[3]),"") OR
choice(this.conditions.vulnerable_to[4] != null, contains(companion.deters,this.conditions.vulnerable_to[4]),"") OR
choice(this.conditions.vulnerable_to[5] != null, contains(companion.deters,this.conditions.vulnerable_to[5]),"") OR
choice(this.conditions.vulnerable_to[6] != null, contains(companion.deters,this.conditions.vulnerable_to[6]),"") OR
choice(this.conditions.vulnerable_to[7] != null, contains(companion.deters,this.conditions.vulnerable_to[7]),"") OR
choice(this.conditions.vulnerable_to[8] != null, contains(companion.deters,this.conditions.vulnerable_to[8]),"") OR
choice(this.conditions.vulnerable_to[9] != null, contains(companion.deters,this.conditions.vulnerable_to[9]),"") OR
choice(this.conditions.vulnerable_to[10] != null, contains(companion.deters,this.conditions.vulnerable_to[10]),"")
SORT file.name asc
limit 5

If someone knows of an elegant for loop in dataviewjs that will do the same, I would be very grateful

[mnvwvnm]

Hi.
Sorry for this short answer but try this:

LIST
FROM #plantae and -"templates"
FLATTEN this.conditions.vulnerable_to AS V
WHERE contains(companion.deters, V)
GROUP BY file.link as File
SORT File ASC

The logic is:
1 - take all the values in this.conditions.vulnerable_to and flatten them - with this we get a “condition” per row - and rename this group as “V” (you can choose other name).
2 - then we apply the filter WHERE contains(companion.deters, V) - now we can check if a list (companion.deters) contains any value in “V”.
3 - finally, to not get multiple files with the same name (a file.link for each “true” in previous where), we group again all things by file.link.

(I made a short test, I’m not sure if it works with all your variants…)
(not a dataviewjs answer because I don’t know JS)

[flatline]

@mnvwvnm that is pretty much 5 million times better than what I had. Really elegant, thank you so much! I had not realised that FLATTEN could be used in this way, but it makes a ton of sense the way you describe it.

---

In case it helps anyone else, I further adapted @mnvwvnm’s solution to work in a table. I had a lot of help from tuxtlequino’s conversation with blacksmithgu on git. Final code below:

TABLE rows.companion.attracts[1] AS attracts, rows.companion.traps[1] AS traps, rows.companion.deters[1] AS deters, rows.companion.assists[1] AS likes, rows.companion.inhibits[1] AS dislikes, rows.conditions.potting_mix[1] AS prefers
FROM #plantae and -"templates" 
FLATTEN this.conditions.vulnerable_to AS V 
FLATTEN this.companion.assists AS A
WHERE contains(companion.deters, V) OR
icontains(file.name,A)
GROUP BY file.link
SORT File ASC

The snippet above:

• FLATTENs (cuts up) the original array and appends it to an object called V. Each row becomes a separate contains(companion.deters, array_entry) call. If there are 3 entries in your array, FLATTEN will create one row per entry.

• GROUP BY rolls all of the duplicate results back up into individual entries, so rather than seeing x# entries per document, you see one.

• GROUP BY takes the duplicate results and “groups” them into their own object called rows. A call like TABLE companion.attracts AS attracts will stop working in this context, because it’s looking for a set of files whereas Dataview has now abstracted all of these files into rows objects. To retrieve your data, you’ll therefore have to amend your query to look inside these rows like this: TABLE rows.companion.attracts AS attracts. This will retrieve the information you’re looking for, stored as an array:

• To remove the duplicate entries, tighten your query to only show the first value of the array, i.e. TABLE rows.companion.attracts[1] AS attracts. Here is a screenshot of the final code and the project so far: