Full text search on subset of fields in SQLite 3.7.4

Question

Context

I have a table specified as

CREATE VIRTUAL TABLE EmailAttachmentSearch USING 
fts4(entityId Varchar(255), name, subject, type, group, from, json);

Requirement

I need to allow users to search over name, subject, type, group and from.

Conditions

I am aware of the notindexed option but due to multiplatform compatibility we are limited to SQLite 3.7.4 on which notindexed is not supported.

Problem

I am trying to construct a query that will be en equivalent to MATCH 'word1* word2*' but will exclude entityId and json fields.

My attempts always result in either no data or too much data.

Invalid example

SELECT json
FROM EmailAttachmentSearch
WHERE EmailAttachmentSearch MATCH 
     '(name:word1* 
    OR type:word1* 
    OR subject:word1* 
    OR from:word1*)
AND   (name:word2* 
    OR type:word2* 
    OR subject:word2* 
    OR from:word2*)'

FTS does not have suffix searches; you cannot put `*` at the beginning of a search term. — CL., Aug 27 '15 at 11:00

score 0 · Answer 1 · edited May 23 '17 at 12:22

Looks like the SQLite query engine doesn't support such complex conditions in the MATCH section. I've done more research and found that it is working pretty well when you use subqueries.

 SELECT json FROM EmailAttachmentSearch WHERE ROWID IN (
     SELECT ROWID FROM EmailAttachmentSearch WHERE EmailAttachmentSearch MATCH 'name:word1* OR type:word1* OR subject:word1* OR from:word1*'
     AND ROWID IN (
         SELECT ROWID FROM EmailAttachmentSearch WHERE EmailAttachmentSearch MATCH 'name:word2* OR type:word2* OR subject:word2* OR from:word2*'
     )
 );

Original answer here:

How do you match multiple column in a table with SQLite FTS3?

Full text search on subset of fields in SQLite 3.7.4

1 Answers1