Dataview lookup tables: comparing properties with multiple values

[Simok]

Hello guys, I need help with this because it is driving me crazy haha. I’m trying to create a sort of “search engine” for my notes collection, where the lookup table needs to compare properties with multiple values in each one.

Structre

We have two types of notes: “tag notes” where the Dataview tables live to search related books, and the other ones are the books to be queried. All properties contain [[internal links]] and both note types contain the same properties to compare them.

What I need to achieve

If I fill a property field in the “tag note” and a book matches, it should appear in the table. Also, it should ignore if the “tag note” field is empty or contains fewer values than the book note since making the table and book match perfectly would defeat the point of a search engine.

This code works, but only if all fields match perfectly.

TABLE 
	Poster, Rating, Feeling, Link, file.cday, split((Relationship + ", " + Identity), ", ")
FROM 
	#Book AND [[]]
WHERE 
	this.SEARCH = true
	AND this.Rating = Rating
	AND this.Feeling = Feeling
	AND this.Relationship = Relationship
	AND this.Identity = Identity
	AND Poster != null
SORT file.cday desc

[Simok]

I think I’m getting closer, but it still doesn’t work.
I implemented contains() and tried to combine AND with OR

TABLE 
	Poster, Rating, Feeling, Link, file.cday, split((Relationship + ", " + Identity), ", ")
FROM 
	#Book AND [[]]
WHERE 
	this.SEARCH = true
	AND (contains(Rating, this.Rating) OR this.Rating = null)
	AND (contains(Feeling, this.Feeling) OR this.Feeling = null)
	AND (contains(Relationship, this.Relationship) OR this.Relationship = null)
	AND (contains(Identity, this.Identity) OR this.Identity = null)
	AND (contains(Rating, this.Rating) OR this.Rating = null)
	AND Poster != null
SORT file.cday desc

[holroy]

Could you show some examples of a search/tag note, and some of the books to be queried. I think you’re close with that second variant, but it could possibly be written in a slightly easier/different way.

The gist of the solution would be for each element to either match against that element or return a true value if it’s not to be included. And this seems to be what you’re trying to do, but it’s hard to help you detect any errors when we don’t know your actual note setup for the search/tag note vs books.

[Simok]

Yeah, I was really close. I’ve just solved the issue and I’m setting all up to share it in case of someone needs it

[Simok]

AFTER 2 DAYS OF PURE PAIN, IT’S WORKING!!

Why it didn’t work at first?

In the second version of the code I implemented contains() and OR, but I wasn’t quite yet. The problem was that using contains() to compare two lists returns true if one string of the second list matches with one of the first one, not only when all strings from the second list match with the first one.

How did I solve it?

I asked in the Obsidian Discord about how to compare strings and the user Dovos gave me the answer: to what I had at the moment, I also needed to implement all() and a lambda.
(all(this.Rating, (L) => contains(Rating, L)) OR this.Rating= null)

How does the working code looks like?

First of all, it looks messy but is awesome. Here it is in case you want to create a search engine for your notes.

TABLE 
	Poster, Rating, Feeling, Link, file.cday, split((Relationship + ", " + Identity), ", ")
FROM 
	#Book AND [[]]
WHERE 
	this.SEARCH = true
	AND (all(this.Rating, (L) => contains(Rating, L)) OR this.Rating= null)
	AND (all(this.Feeling, (L) => contains(Feeling, L)) OR this.Feeling= null)
	AND (all(this.Relationship, (L) => contains(Relationship, L)) OR this.Relationship= null)
	AND (all(this.Identity, (L) => contains(Identity, L)) OR this.Identity= null)
	AND Poster != null
SORT file.cday desc