在SQLite FTS中，首先要有确切的短语吗? [英] Exact phrase first before anything else in SQLite FTS?

问题描述

假设搜索输入为是什么".这将匹配无论是什么"，也将匹配什么是"，这是确切的短语.现在，如何在排序中首先使确切的短语呢?

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.

这是我失败的方法(假设输入='what is'):

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

• 此问题是两个SELECT不互斥 所以会有重复.
• 我无法使用UNION，因为它们在等级列和摘要功能上有所不同(首先具有START | what is | END，其他具有START | what | ENDSTART | is | END).
• 我也不能使用
  • 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*' 
  

  我在这里做的是:

  1. 在最里面的SELECT中，我正在进行排序
  2. 在下一个SELECT中，我将筛选出重复项(这将起作用，因为我尚未使用代码段.
  3. 最后，如果rowId在第二选择中，我将选择代码段.

  这里的问题是，正如您所期望的，订购已完全消失:(.

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

  我正在Android中使用FTS4(我不使用增强的查询语法，因为我不确定Android是否支持).

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

  推荐答案

  您必须对FTS表本身进行所有FTS处理(包括snippet())，然后再将结果合并到最上面的查询中:

  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*/;
  

  或者，使用更通用的模式获取所有可能的行，然后在ORDER BY中检查更严格的MATCH:

  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"');
  

  这篇关于在SQLite FTS中，首先要有确切的短语吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！