CODEWITHSUNDEEP
androidsqlitefull-text-searchwhere-clausefts3
GaRRaPeTa
GaRRaPeTa

Reputation: 5598

Combine MATCH with OR clause in the WHERE statement

I want to perform a query in which the WHERE clausule has the following condition:

Example:

Say that I have two tables

I want to search all the books that either contain 'Dikjstra' in their content or are tagged with the 'algorithm' word. So I run this query:

SELECT * from books_fts               
LEFT OUTER JOIN books_tags ON books_fts.fk_id = books_tags.id
WHERE (books_fts MATCH 'content:%Dijkstra*')
OR (books_tags.tag = 'algorithm')

I think the query is right, and if I run it with either one of the OR clausules, it works. However, when running it with the two clausules I get the following error:

unable to use function MATCH in the requested context

Seems to me that I cannot combine a MATCH with a non MATCH in the WHERE clause, even if each of them apply to different tables (one FTS and another non FTS). Is this true? I cannot find information on it.

NOTE: if the causules are separated with AND instead of OR the query is valid.

Thanks.

Upvotes: 0

Views: 660

Answers (2)

DharmaTurtle
DharmaTurtle

Reputation: 8467

This SQL worked for me

select 
  *
from 
  "card" 
  inner join "note" on "card"."noteId" = "note"."id" 
  inner join "noteFtsTag" on "noteFtsTag"."rowid" = "note"."rowid" 
  inner join "cardFtsTag" on "cardFtsTag"."rowid" = "card"."rowid" 
where 
  (
    "noteFtsTag"."rowid" in (
      select 
        "rowid" 
      from 
        "noteFtsTag" 
      where 
        "noteFtsTag"."tags" match ?
    ) 
    or "cardFtsTag"."rowid" in (
      select 
        "rowid" 
      from 
        "cardFtsTag" 
      where 
        "cardFtsTag"."tags" match ?
    )
  )

Source: https://sqlite.org/forum/forumpost?udc=1&name=1a2f2ffdd80cf795

Upvotes: 0

GaRRaPeTa
GaRRaPeTa

Reputation: 5598

It seems it's a known issue in SQL:

http://sqlite.1065341.n5.nabble.com/FTS3-bug-with-MATCH-plus-OR-td50714.html

Upvotes: 1

Related Questions