Exact phrase first before anything else in SQLite

问题:

Suppose the search input is 'what is'. This will match 'whatever it is' and also 'what is' which is the exact phrase. Now, how do I make the exact phrase first in the sorting?

I've been thinking about this since yesterday and I keep on coming up with different solutions but each has somehow its own flaw.

Here are my failed approach though (supposing input = 'what is'):

SELECT snippet(fts), 1 as rank
FROM fts
WHERE body MATCH '"what is"'
UNION ALL
SELECT snippet(fts), 2 as rank
FROM fts
WHERE body MATCH 'what* NEAR/3 is*' -- 3 is arbitrary
ORDER BY rank

• The problem on this one is that the two SELECTs are not mutually exclusive so there will be duplicates.
• I can't use UNION since they differ on rank column and snippet function (first will have START|what is|END, other will have START|what|ENDSTART|is|END).
• I can't also use this (my previous question) since MATCH won't work in ORDER BY clause (because the resulting table/select is not the original FTS table).

Here's my current solution:

SELECT snippet(fts)
FROM fts WHERE rowId IN
(
SELECT DISTINCT rowId
FROM 
( 
SELECT rowId, 1 as rank 
FROM fts
WHERE body MATCH '"what is"'
UNION ALL 
SELECT rowId, 2 as rank  
FROM fts
WHERE body MATCH 'what* NEAR/3 is*'
ORDER BY rank
) 
)
WHERE body MATCH 'what* NEAR/3 is*' 

What I'm doing here is:

1. In the innermost SELECT, I'm doing the sorting
2. In the next SELECT, I'm filtering out the duplicates (this would work because I'm not using snippet yet.
3. Lastly, I'm selecting the snippet if rowId is in the 2nd select.

The problem here is, as you expect, ordering is completely gone :(.

EDIT:

I'm using FTS4 in Android (I don't use the enhanced query syntax because I'm not sure if Android supports that).

回答1:

You have to do all FTS processing (including snippet()) on the FTS table itself, and only afterwards combine the result in the uppermost query:

SELECT docid,
       snippet,
       MIN(rank) AS rank
FROM (SELECT docid,
             snippet(fts) AS snippet,
             1 AS rank
      FROM fts
      WHERE body MATCH '"what is"'
      UNION ALL
      SELECT docid,
             snippet(fts),
             2
      FROM fts
      WHERE body MATCH 'what* NEAR/3 is*')
GROUP BY docid
ORDER BY MIN(rank) /*, docid*/;

Alternatively, get all possible rows with the more general pattern, and check for the more strict MATCH in the ORDER BY:

SELECT snippet(fts)
FROM fts
WHERE body MATCH 'what* NEAR/3 is*'
ORDER BY NOT (body MATCH '"what is"');