Have you considered using INTERSECT instead? Then you could use one MATCH per subquery
I may be misunderstanding you, but this would still mean I have to reduce to conjunctive normal form (rather than disjunctive normal form), right?
FWIW, I’ve got the SQL generator working…
Well, I assumed you have multiple AND conditions that’s why I suggested INTERSECT but I think if you rewrite the generating code to use INTERSECT for AND clause and UNION ALL for OR clause, you don’t need to convert to any normal forms and use it as is. But obviously my idea requires at least performance check
1 Like
Yeah, if UNION turns out to have horrible performance, I will certainly try INTERSECT. Thanks!
Goodness knows that I’ve been surprised by seemingly equivalent SQL that was 10x+ faster (especially when query planner wasn’t very sophisticated).
1 Like
Have you tried to do UNION ALL and then DISTINCT at the end of the query? They say UNION ALL has better performance than just UNION
1 Like
@mrm I’ve checked the documentation for your search feature and it looks really awesome. I suggest to consider more convenient syntax for date ranges which, in my opinion, going to be one of the most used filters.
Please also consider some libraries for parsing dates like date:"last Friday"
For example Sugar - Home or GitHub - wanasit/chrono: A natural language date parser in Javascript
I would be nice if you also think how to fix one annoying issue all DAMs have. If you have hierarchical keywords a/b/c and you look for a/b there should be a way to distinguish cases when you want to find exactly that keyword without its descendants. Maybe it could be solved by kw:a/b AND -kw:a/b/* but it would be nice to have neater syntax for that case
This is interesting to me, because I actually don’t think I’ll use date ranges very much. I’m much more interested in searching by people and keywords, especially in combination.
Obviously neither of us is right or wrong, but it does underscore that there are lots of different preferred use cases.
1 Like
Thanks for those links!
I thought non-ISO-formatted dates would be neat, too, and did a bit of looking for a date parser that supported internationalization, but (like most of npm), were either English-only, had no tests, had been abandoned, or all of the above.
I think just a could regexs would get year, month, week, and day parsing. Here’s a google sheet: please ask for edit access if you’d like to add a language or other examples.
Would tag:/a/b/c suffice (where /a/b/c is the entire path of the tag)?
Just as more feedback… I do want to deal with date ranges a lot, but personally never seem to use the “natural language” type date formats (last week, etc.). Maybe it’s the programmer in me that finds them too ambiguous (or I’m just too old :-)).
Anyhow, I certainly don’t mind if they are there for folks that like them, I can see the use, but just to toss in my 2 cents…
Sorry, I didn’t explain better about tags
What if I want to find tag:/a/b but I don’t want to include tag:/a/b/c or any other descendants of tag:/a/b
Real use case example kw:dog but you want to exclude kw:dog/Fido, kw:dog/Rex etc. So basically, you want to find only unknown dogs.
Hope this example explains my concern better
I’m also a programmer and yyyy-MM-dd is the only thing that I prefer most of the time, but as your product’s supposed main audience is non-IT, I assume date:"last summer" is more preferable to type than after:2020-06-01 AND before:2020-09-01
BTW, relative dates you can find even in a very IT stuff
--date=relative
1 Like
Ah! I already added operators for date terms: perhaps tag:=/keywords/dog means only look at that tag, and not match descendant tags: would that work?
Edit 20210410: deleted the remainder of the post, as it has been superseded by the documentation.
1 Like
I think this example is not accurate enough as
fs:=a/b/c matches /photos/a/b/c/file.jpg assuming that /photos is a single root of your photo library. But I think you support having multiple roots so it will also match /other/a/b/c/file.jpg and that makes := to mean what it actually isn’t.
Moreover, with that logic how can you find fs:=photos/a/b/c when you want to filter only the photos root folder? How to make the syntax not ambiguous so it won’t mistakenly include /other/photos/a/b/c/file.jpg?
Ah, good point, I’ll add these as examples.
The solution is to provide an “absolute path” to the tag your want to match.
tag:=foo/bar means match all assets directly associated to tags that end with foo/bar. I think it will be helpful for who:=michael to only match first names.
tag:=/foo/bar means match all assets directly associated to tags whose path exactly matches /foo/bar. You have to include the entire path.
So to avoid files in /other/a/b/c/, you’d use fs:=/a/b/c.
Note that the fs: search terms currently hit the tag fts index. I’ll be adding an asset fts index as well, which will include title, description, and metadata terms at some point in the future.
1 Like
Then we need an example how to use absolute path on Windows that has \ path delimiter
And if := actually means ends with tag so maybe you should consider changing syntax to something like old school jQuery style $=
https://api.jquery.com/category/selectors/attribute-selectors/
I guess I could change it to
:=means “tag exactly equals”, and:$=means “ends with”?
In that case, is tag:=a/b/c equivalent to tag:=/a/b/c?
Right. I’ll add a note that windows users must convert all “\” to “/”.
1 Like
So it’s fs:"C:/Folder with space/a/b/c"?