SearchRank on multiple SearchVectorFields

Question

I'm trying to integrate full text search in my application. Referencing Django 3.1 documentation If I want to do weighted search across several fields I should do the following:

from django.contrib.postgres.search import SearchQuery, SearchRank, SearchVector
vector = SearchVector('body_text', weight='A') + SearchVector('blog__tagline', weight='B')
query = SearchQuery('cheese')
Entry.objects.annotate(rank=SearchRank(vector, query)).filter(rank__gte=0.3).order_by('rank')

I decided to add SearchVectorField for the text columns I want to search later, but I think the documentation about how to work with those fields is not good enough and I can't find any reference on how to do the same as the query above, but using SearchVectorField

Ideally I want something like the following

objects = Post.objects.annotate(
                    rank=SearchRank(
                        F('title_text_vector', weight='A') + F('body_text_vector', weight='B'),
                        SearchQuery('keyword1 keyword2 keyword3')
                    )
                )

objects.filter( < MORE QUERIES HERE > )

score 2 · Answer 1 · answered Sep 21 '20 at 20:02

I found a workaround which as far as I saw generates the same SQL query as the approach which does not use SearchVectorFields.

Esentially I just query each field using the annotate method to generate custom rank for each SearchVectorField, which I then weight manually. Not very scalable approach, but works for my case.

        search_query = SearchQuery('keyword1 keyword2 keyword3')
        posts = Post.objects.annotate(
            title_rank=SearchRank(
                'title_vector',
                query=search_query
            )
        ).annotate(
            body_rank=SearchRank(
                'body_vector',
                query=search_query
            )
        ).annotate(
            rank=(self.search_weights['title'] * F('title_rank') +
                  self.search_weights['body'] * F('body_rank'))
        ).order_by('-rank')

This is cool, but yep - not scalable. Wonder what the scalable solution is? When I use SeachRank - my nice fast GIN indexes and SearchVectorFields basically become useless. Postgres just wants to seq scan again for some reason.. — lys, Dec 25 '21 at 10:01

SearchRank on multiple SearchVectorFields

1 Answers1