postgres中简单文本搜索与复杂文本搜索的结合

我目前正试图找出如何处理我的应用程序中的搜索功能。为了显示我目前遇到的问题,我创建了一个虚构的数据库表。

+----+--------------+-----------+----------+---------------------------------------------------------------------------------+------------------------+
| id |   username   | firstname | lastname |                                   description                                   |         email          |
+====+==============+===========+==========+=================================================================================+========================+
|  1 | example.hans | Hans      | Example  | Some randome user called Hans. It doesn't really matter what the description is | example@test.com       |
|  2 | root         | Root      | Root     | Some other user                                                                 | root@localhost         |
|  3 | Shawn94      | Leo       | Larson   | Loves to play with firework                                                     | leo.larson@hotmail.com |
+----+--------------+-----------+----------+---------------------------------------------------------------------------------+------------------------+

这当然不是真实的数据。

我知道我可以用to tsvector和to tsquery在postgres中创建一种全文搜索。但是这个方法的问题是,我不能真正使用小值,比如username、firstname或lastname。如果我使用查询词 leo Postgres可能找不到ID为3的用户,因为搜索项和目标值太小。如果我使用fuzzymatching(例如:levenshtein)的话,我的描述就会有问题。Levenshtein对小价值观很有帮助。然而,它在文本上并不是很有效。

我试着把这两种方法结合起来,但是我不能平衡左旋恩施坦和tsvector。

下面是一个我试图平衡它的示例查询:

SELECT "id" as "id",
       (
           (ts_rank_cd(to_tsvector("user"."description"), to_tsquery('Bee')))
           -
           (
             (
               SELECT MIN("LEVENSHTEIN_VALUES")
               FROM unnest(
                        ARRAY [levenshtein("user"."fullName", 'Bee'), levenshtein("user"."username", 'Bee'), levenshtein("user"."email", 'Bee')]) AS "LEVENSHTEIN_VALUES"
             )
           )
         )                      AS "FULLTEXT_RANK"
FROM "user" "user"
ORDER BY "FULLTEXT_RANK" ASC
LIMIT 10;

不能保证这个查询确实有效,因为我从应用程序中获取了它并对其进行了修改,使其与示例数据相匹配。

解释我的尝试:

首先在描述字段上创建一个带有'ts_rank_cd'的秩值(实际上在实际应用程序中,我需要在多个列上进行文本搜索)。然后我得到每个字段的levenshtein值,并使用最小的值从“ts_rank_cd”中减去它。这就是我试图平衡左旋恩施坦和tsvector的方法。当然,这种方法有一个巨大的缺点。如果每个levenshtein列与搜索查询完全不同,但描述中包含我们正在搜索的术语,则可能会发生由于levenshtein索引,搜索行仍然获得较低的全文排名。

如何将全文搜索与模糊匹配结合起来?

编辑:

SELECT *,
       (
         SELECT SUM("TS_AND_SIM_RANK")
         FROM unnest(ARRAY [
           (SELECT MAX("TS_RANK")
            FROM unnest(ARRAY [
              ts_rank(to_tsvector('simple', "user"."description"), phraseto_tsquery('simple', ?))
              ]) AS "TS_RANK"),
           (SELECT MAX("SIM_RANK")
            FROM unnest(ARRAY [
              similarity("user"."username", ?),
              similarity("user"."firstname", ?),
              similarity("user"."lastname", ?)
              ]) AS "SIM_RANK")
           ]) AS "TS_AND_SIM_RANK"
       ) as "RANK"
FROM "user"
ORDER BY "RANK" DESC;

这是另一个我能做的主意。但仍然存在一些问题。