Search code examples
androidandroid-roomandroidx

androidx room multiple MATCH

I have a problem with androidx.room. I'm trying to use multiple MATCH in query

@SuppressWarnings(RoomWarnings.CURSOR_MISMATCH)
@Query("SELECT * FROM tests WHERE task MATCH :words OR task MATCH :words2")
Maybe<List<Test>> getByWordsInTask(String words, String words2);

But it works only on android 8+, on 7 it shows an exception

E/SQLiteLog: (1) statement aborts at 7: [SELECT * FROM tests WHERE task MATCH ? OR task MATCH ?] unable to use function MATCH in the requested context
E/SQLiteQuery: exception: unable to use function MATCH in the requested context (code 1); query: SELECT * FROM tests WHERE task MATCH ? OR task MATCH ?

Any suggestions?

Solution

  • MATCH is special function/keyword and is not implemented other than for use in FTS(3 or 4 are supported by Room where MATCH can be used), as per

    The MATCH operator is a special syntax for the match() application-defined function. The default match() function implementation raises an exception and is not really useful for anything. But extensions can override the match() function with more helpful logic.

    https://sqlite.org/lang_expr.html#the_like_glob_regexp_and_match_operators

    You will need an alternative, perhaps the instr scalar function, perhaps LIKE.

    I use FTS 4, all is working good when I use single MATCH, but if I use OR and 2 MATCH operators (I need it for fast, but more extended search) it works only on Android 8 and higher. I can not understand.

    Try "SELECT * FROM tests WHERE task MATCH :allwords"

    • where allwords is all the words separated with OR (or appropriate operators) e.g. Say words was *Fred and words2 was *Mary then allwords would be Fred OR Mary;

    • if that works it might be a Room thing and if so perhaps report it as a bug