3

I have three fts virtual tables in my sqlite database - two with one content column, and one with 2 columns - title and content. Say one table is for some articles' content and titles, and other two is for reviews and their notes. Is there some way to commit a search among them all in one query? I need to merge all results and sort them by relevance, first the articles and their titles, then reviews and notes. I have made some workaround. I fill a table with search results to display on every new search, but I think that is not very good idea:

DROP TABLE IF EXISTS srch_res;
CREATE TABLE srch_res(type integer, docid integer, snippet text, rank numeric);"
INSERT INTO srch_res SELECT '0', docid, snippet(reviews_c), rank(matchinfo(reviews_c), 0.5) FROM reviews_c WHERE reviews_c MATCH '%s';
INSERT INTO srch_res SELECT '1', docid, snippet(notes_c), rank(matchinfo(notes_c), 0.25) FROM notes_c WHERE notes_c MATCH '%s';
INSERT INTO srch_res SELECT '2', docid, snippet(arts_c), rank(matchinfo(arts_c), 1.0, 0.75) FROM arts_c WHERE arts_c MATCH '%s';
Anonymous
  • 347
  • 2
  • 8
  • btw, what are those floats in the `SELECT`s?, why the last `SELECT` got 2 floats (`1.0, 0.75`)? Thanks – Jason Mar 11 '19 at 08:27

1 Answers1

3

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'
;
Benoit
  • 76,634
  • 23
  • 210
  • 236
  • Thank you. You think that's ok to use a separate table for search results? – Anonymous Jun 07 '11 at 16:03
  • @Anonymous: If you needn't remembering them across sessions, then use temporary tables. Also consider, if you found the answer helpful, clicking the check mark under the answer score to mark it as accepted. – Benoit Jun 07 '11 at 16:25