Django Postgres Full Text TrigramSimilarity Multiple Fields

Question

I am trying to figure out how to use TrigramSimilarity with unaccent for multiple fields.

So far i have:

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)
    )

But i read in the documentation that i could order by similarity (not sure if the code above does that automatically), so i tried doing:

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

I soon realized that the + in TrigramSimilarity was not doing the OR, but i need to full text search across all those different fields.

What is the correct syntax for me to achieve that (use an OR instead of and AND) taking query performance into account??

Thanks

Answer 1

I think the code below could be of help, but I haven't ran it:

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 can be imported like below:

from django.db.models.functions import Greatest