jeudi 27 août 2015

Full text search on subset of fields in SQLite 3.7.4

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*)'

Aucun commentaire:

Enregistrer un commentaire