How to Match Multiple Column in a Table with SQLite Fts3

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

I don't think you can use multiple MATCH operators in a single FTS query. Use column_name:target_term to search multiple columns using one full text search.

SELECT * FROM table WHERE table MATCH 'A:cat OR C:cat'

http://www.sqlite.org/fts3.html#termprefix

How to FTS-match from 2 different columns

You should use only a single FTS query per table. FTS does have boolean operators:

... WHERE tblvHAL2001 MATCH 'keyword: "blah" OR keyword2: "blah"'

And in FTS5, a single column filter can specify multiple columns:

... WHERE tblvHAL2001 MATCH '{keyword keyword2}: "blah"'

SQLite3 multiple FTS tables search

Use:

 TRUNCATE TABLE srch_res;

INSERT INTO srch_res (type, docid, snippet, rank)
    SELECT '0', docid, snippet(reviews_c), rank(matchinfo(reviews_c), 0.5)
      FROM reviews_c
     WHERE reviews_c MATCH '%s'
 UNION ALL
    SELECT '1', docid, snippet(notes_c), rank(matchinfo(notes_c), 0.25)
      FROM notes_c
     WHERE notes_c MATCH '%s'
 UNION ALL
    SELECT '2', docid, snippet(arts_c), rank(matchinfo(arts_c), 1.0, 0.75)
      FROM arts_c
     WHERE arts_c MATCH '%s'
;

Searching in multiple columns using Full Text Search(FTS) with multiple tokens using OR operator

So, I found the solution finally,

instead of searching from all the columns individually, I created a single column in the database which contains data for required columns to be searched into,

Example

I need to search in prod_name & prod_short_desc columns, so I created a column named data in database and appended the values of prod_name & prod_short_desc then looking up into only data field worked like a charm

prod_name | prod_short_desc

samsung  | samsung s5

So, now I merged the data of both the columns into one with space as a seperator

data

samsung samsung s5

And then search was very fast indeed with below query,

select * from productsearch where productsearch match ('samsung*s5*')

Using SQLite FTS3 with INTEGER columns

In short, it's pretty difficult to enforce consistency where FTS3 is involved.

SQLite virtual tables don't allow for triggers, and FTS3 tables ignore constraints and affinities.

The best I have been able to do so far is as follows:

CREATE TABLE metadata (document INTEGER, page INTEGER, UNIQUE(document, page));
CREATE VIRTUAL TABLE data USING fts4();

CREATE VIEW whole AS SELECT metadata.rowid AS rowid, document, page, content 
    FROM metadata JOIN data ON metadata.rowid = data.rowid;

CREATE TRIGGER whole_insert INSTEAD OF INSERT ON whole
BEGIN
  INSERT INTO metadata (document, page) VALUES (NEW.document, NEW.page);
  INSERT INTO data (rowid, content) VALUES (last_insert_rowid(), NEW.content);
END;

CREATE TRIGGER whole_delete INSTEAD OF DELETE ON whole
BEGIN
  DELETE FROM metadata WHERE rowid = OLD.rowid;
  DELETE FROM data WHERE rowid = OLD.rowid;
END;

To enforce consistency I could (with PRAGMA recursive_triggers = NO) create triggers to raise exceptions on direct operations on the metadata and data tables, but this is probably overkill for my purposes (likewise, I don't need the UPDATE trigger for the whole table).

---

Related Topics