4

I am trying to do this query in android FTS4 table and this works perfectly:

SELECT * from table WHERE table MATCH 'description: paint* OR alias: paint*'

I need to match multiple words in multiple columns like this:

SELECT * from table WHERE table MATCH 'description: seal* AND paint* OR alias: seal* OR paint*'

This doesn't work in android but works in any DB browser.

I have tried many combinations such as below, they all work in the browser but not in android. 

SELECT * from table WHERE table MATCH '(description: seal* AND paint*) OR (alias: seal* OR paint*)'

SELECT * from table WHERE table MATCH 'description: (seal* AND paint*) OR alias: (seal* OR paint*)'

The documentation of sqlite3 doesn't specify any solution for multiple columns with multiple words.

Also in this question the query works in the current android environment as mentioned above in my first line of code. Maybe it didn't work in the past but now it works. My problem is regarding multiple values with OR/AND as described not multiple columns.

Is there any way to achieve this thing in Android?

Aayush Thakur
  • 634
  • 1
  • 10
  • 22
  • Possible duplicate of [SQLite enhanced query syntax on Android](https://stackoverflow.com/questions/18672777/sqlite-enhanced-query-syntax-on-android) – Shawn Nov 11 '19 at 15:04
  • That's an older answer, but AFAIK the situation hasn't improved any since then. – Shawn Nov 11 '19 at 15:06
  • 1
    The possible duplicate of this question now works in android as the question is very old but my query is regarding multiple values, single values in multiple columns seem to work in current android. – Aayush Thakur Nov 12 '19 at 06:58
  • You've confirmed that the Android version was compiled with `ENABLE_FTS3_PARENTHESIS` like that answer suggests doing? – Shawn Nov 12 '19 at 08:34
  • This pragma is deprecated. Check this [Link](https://www.sqlite.org/pragma.html#pragma_compile_options) – Aayush Thakur Nov 12 '19 at 08:54
  • No... No, it's not deprecated. And did you verify that option is being used? – Shawn Nov 12 '19 at 08:57
  • It is clearly mentioned in the documentation that this should not be used especially in new applications such as mine which runs on the android pie SDK. And I am not sure on how to check this ENABLE_FTS3_PARENTHESIS . – Aayush Thakur Nov 12 '19 at 09:02
  • You *are* looking at the `compile_options` pragma documentation, right, and not the next one down, which *is* deprecated? You execute it (or a function equivalent) on your Android system like any other query and look at the resulting rows... – Shawn Nov 12 '19 at 09:09
  • As u suggested I tried this query, localCursor = localSQLiteDatabase.rawQuery("pragma compile_options",null) But this returns 0 rows. – Aayush Thakur Nov 12 '19 at 09:19
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/202194/discussion-between-aayush-thakur-and-shawn). – Aayush Thakur Nov 12 '19 at 09:20
  • That sounds like it was built with [SQLITE_OMIT_COMPILEOPTION_DIAGS](https://www.sqlite.org/compile.html#omit_compileoption_diags). Google doesn't like to make anything easy. – Shawn Nov 12 '19 at 09:36
  • So, I think there is no solution besides using a union search or 2 different searches instead of the above query. – Aayush Thakur Nov 12 '19 at 09:39

0 Answers0