Search code examples
androidsqlsqliteandroid-roomfts4

Android room fts4 MATCH syntax AND OR

What is the proper syntax for more complex MATCH query using room and FTS4 I've found in the documentation following syntaxs which I'd like to use

SELECT docid FROM docs WHERE docs MATCH '("sqlite database" OR "sqlite library") AND linux';
SELECT * FROM docs WHERE docs MATCH 'title:linux problems';
query result
MATCH '("sqlite database" OR "sqlite library") AND linux' (x)
MATCH '("sqlite database" OR "sqlite library") linux' OK
MATCH 'content:("*sql*" OR "*lin*")' OK (partly, only last star works, will not find "mysql")
MATCH 'content:sql* id:123' OK
MATCH 'content:("*sql*" OR "*lin*") AND id:("*123*")' (x)
MATCH 'content:("*sql*" OR "*lin*") id:("*123*")' (x)

Seems like AND is not working. And I do something wrong with quotes or brackets,

Basically I'd like to get the equivalent of LIKE with AND OR and different values on different columns

Solution

  • I believe that the AND issue is that only the STANDARD query syntax is available not the ENHANCED query syntax.

    See (ENHANCED)

    versus (STANDARD)

    That is that the SQLite API/Libraries, which Room utilises islikely based upon an SQLite compilation that only includes the STANDARD syntax.

    The STANDARD syntax does not include the AND keyword and as such is taken to be the word itself. However AND is implied hence why MATCH '("sqlite database" OR "sqlite library") linux' returns the expected result.

    • as per Only the implicit version of the AND operator is supported. Specifying the string "AND" as part of a standard query syntax query is interpreted as a term query for the set of documents containing the term "and".

    In regard to MATCH 'content:("*sql*" OR "*lin*")' OK (partly, only last star works, will not find "mysql")

    This because FTS is trying to MATCH a term similar to an index if it were to start with a wild character then a full scan would be required rather than say jumping to s, then sq then sql and so on.

    • An analogy could be to try looking through a phone directory for a name that has an a in it as opposed to looking for a name that starts with a. The former would require looking at every single entry. The latter would only require looking at entries that start with a.

    As such I believe that you could/should use:-

    MATCH 'content:("sql*" OR "lin*" OR "mysql*")'