Hacker Newsnew | past | comments | ask | show | jobs | submitlogin

I had a... suboptimal experience with SQLite's FTS, so here it is for others: match queries don't work with `OR` conditions. You have to do this work around:

    WHERE
        fa.rowid IN (SELECT rowid FROM address_fts WHERE address_fts MATCH 'google')
        OR
        ta.rowid IN (SELECT rowid FROM address_fts WHERE address_fts MATCH 'google')
Source https://sqlite.org/forum/forumpost?udc=1&name=1a2f2ffdd80cf7...

I'm building a search feature for my app, that parses a user's search query (a la Github search) and returns relevant results. Generating the above workaround SQL on the fly is just... ulgh.



I encountered similar issues with FTS5, I found you can clean it up slightly with a CTE

  WITH matches(id) AS (
    SELECT rowid FROM address_fts WHERE address_fts MATCH 'google'
  )
  ...
  WHERE
    fa.rowid IN matches
    OR
    ta.rowid IN matches
You can also have more complex queries, such as if you want to match multiple domains (MATCH 'google OR gmail').

It makes it slightly easier to generate queries since you only have to generate the CTE and then you can just use the CTE name everywhere else.


Yeah this is exactly what I ended up doing, like two days ago. It’s quite a pitfall… (and not optimal in terms of performance)




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: