sqlite weight the matches in full text search

Question

I have a fts3 table like this:

 CREATE VIRTUAL TABLE docs USING fts3(id, title, body);

I want to make weight the matches in title higher than those in content like this question

What I did:

    SELECT    
          case when title match 'word' then 1 else 0 end as titleRank,  
          case when body match 'word' then 1 else 0 end as contentRank      
          docs.*  
    FROM  docs  
    WHERE title match 'word' OR body match 'word'
    ORDER BY titleRank desc, contentRank desc

But seems the select case doesn't work with full text search (I use this tool, there is no error, no response from this query, but if I remove select case, it works)

Where am I wrong?

1. Your `id` column will be full-text-indexed; [it is not a key](http://www.sqlite.org/fts3.html#section_1_3). 2. See the [matchinfo function](http://www.sqlite.org/fts3.html#matchinfo). — CL., Sep 07 '15 at 07:25
@CL. sorry, I don't understand how the matchinfo can solve my problem. Can you explain it? — ductran, Sep 07 '15 at 07:36
It can return information about which phrase appears in which column. — CL., Sep 07 '15 at 07:39
@CL. well, look like it needs to use programming language to read the result of matchinfo. I can't use this result on the next query — ductran, Sep 07 '15 at 13:02

score 4 · Answer 1 · edited Dec 06 '18 at 15:54

There is this offsets function that might be useful. In your case, you know the title column is index 1 and the body column is index 2, so you could write this:

SELECT *
FROM docs
WHERE docs MATCH 'title:word OR body:word'
ORDER BY SUBSTR(OFFSETS(docs), 1, 1), id;

This will order rows that have a match in column 1 (title) before those with a match in column 2 (body).

(Edited the location of the closing parentheses on the OFFSETS() function.)

sqlite weight the matches in full text search

1 Answers1