Django Postgres全文TrigramSimilarity Multiple Fields

Question

我想弄清楚如何将 TrigramSimilarity 与 unaccent 一起用于多个字段。

到目前为止我有:

def filter_by_location(self, queryset, location):
    log.info('Filtering location: "{location}"'.format(**locals()))
    queryset = queryset.filter(
        Q(accommodation__district__name__unaccent__trigram_similar=location) |
        Q(accommodation__municipality__name__unaccent__trigram_similar=location) |
        Q(accommodation__settlement__name__unaccent__trigram_similar=location)
    )

但我在 documentation 中读到我可以按 similarity 排序(不确定上面的代码是否自动执行)，所以我尝试这样做:

    queryset = queryset.filter(
        Q(accommodation__district__name__unaccent__trigram_similar=location) |
        Q(accommodation__municipality__name__unaccent__trigram_similar=location) |
        Q(accommodation__settlement__name__unaccent__trigram_similar=location)
    ).annotate(
        similarity=TrigramSimilarity(
            'accommodation__district__name', location
        ) + TrigramSimilarity(
            'accommodation__municipality__name', location
        ) + TrigramSimilarity(
            'accommodation__settlement__name', location
        ),
    ).filter(similarity__gt=0.3).order_by('-similarity')

我很快意识到 TrigramSimilarity 中的 + 没有执行 OR，但我需要在所有这些不同的字段中进行全文搜索.

在考虑查询性能的情况下，实现该目标的正确语法是什么(使用 OR 而不是 and AND)？

谢谢

Answer 1

我认为下面的代码可能会有帮助，但我还没有运行它:

queryset = queryset.annotate(
    similarity = Greatest(
        TrigramSimilarity('accommodation__district__name', location),
        TrigramSimilarity('accommodation__municipality__name', location),
        TrigramSimilarity('accommodation__settlement__name', location))
).filter(
    Q(accommodation__district__name__unaccent__trigram_similar=location) |
    Q(accommodation__municipality__name__unaccent__trigram_similar=location) |
    Q(accommodation__settlement__name__unaccent__trigram_similar=location),
  similarity__gt=0.3).order_by('-similarity')

Greatest 可以像下面这样导入:

from django.db.models.functions import Greatest