Search code examples
javaandroidsqlitefts3fts4

How do I query an fts table in sqlite for one column matching and another NOT matching a particular query?

I have a Suggestions table(FTS4) that is used to provide suggestions for a query in Android action bar search. I need to query the table for suggestions where one column(suggest_text_1) matches the query text AND another column(data_type) does NOT match a particular query, say 2.

My current selection text is table_suggestions MATCH ? and I'm trying the following selection argument text to be substituted for the '?' - suggest_text_1:*S1* AND data_type:NOT 2.

The problem is, the table is providing me any suggestions even though the data is there. Could point me in the right way to perform this query?

Solution

  • The SQLite library in Android is typically not compiled with the enhanced query syntax, so you have to use the standard query syntax as something like suggest_text_1:S1* data_type:-2. In SQL, this would be:

    SELECT docid,
       suggest_text_1
FROM MyTable
WHERE MyTable MATCH 'suggest_text_1:' || ? || '* data_type:-' || ?

    FTS supports only prefix queries; you cannot use * at the beginning of a word.