10 ms·
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:
by AlexErrant 3y ago
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=1a2f2ffdd80cf795 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.
- Hakkin 3y agoI 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.
- shivawu 3y agoYeah this is exactly what I ended up doing, like two days ago. It’s quite a pitfall… (and not optimal in terms of performance)