SQLite support for operators AND, OR

[hannesherold]

Different string operators for the search query are currently not documented and probably not supported for SQLite.

We have, let’s say, two fields, author and title. A search string with a combination of the two results in no hits. For example: The search string “chasper rimas” contains an author name “chasper” and a title of a book called “rimas”. The search result is empty, though.

It would be nice if the search method of the plugin would accept differnt operators like AND or OR. $site->search('query', 'operator')

This feature would improve the applicability of the plugin in places where a more flexible handling of the search is necessary, for example with larger catalogs.

[distantnative]

Ok, so first thing to keep in mind is that this plugin uses the FTS5 plugin of Sqlite. It does a lot of black magic but that also means you aren't as free to just type a normal SQL query.

However, in theory FTS also supports operators such as AND, OR and NOT.

And in researching this, I have realized that I have been using the search a bit wrong probably. Nevertheless, it's a bit tricky: for a better experience I need to insert * to the end of each search word/token to support matching to partial words. This will work well if I can assume converting

fox brow

to

fox* OR brow*

But I need to be carful to not convert an already qualified query such as

fox AND brow NOT cam

to a false query like

fox * OR AND* OR brow* OR NOT* OR cam*

I think I could approach this as followed:

• Only insert operator if the query string doesn't already contain any AND, OR or NOT
• Append * to all tokens/words except AND, OR and NOT
• Allow specifying the operator to insert (default OR) as you suggested via $site->search('term1 term2', 'AND')

Would be glad to hear your thought on these suggestions?

[distantnative]

✅ This should work now on the develop branch with the Sqlite provider.

// OR
$site->search('term1 term2')
$site->search('term1 OR term2')
$site->search('term1 term2', 'OR')

// AND
$site->search('term1 term2', 'AND')
$site->search('term1 AND term2')
$site->search('term1 term2', ['operator' => 'AND'])

// more complex
$site->search('term1 AND term2 NOT term3')

[hannesherold]

Excellent, thank you! In our use case it's working like a charm. Personally, I think this is a huge step for larger sites. Happy days!