PostgreSQL text search in Django not working as expected

Question

I am implementing a search function for my API to return the object's properties when requested. So far I have tried to use Full Text Search which is usable but it has these annoying things: words have to be spelled correctly for the results to be returned and partial search, for example "appl" instead of "apple", won't work. I have also tried Trigram Similarity but it failed for long sentences. How do I implement a search function that is both accurate and fuzzy in Django?

This works

This won't work

This is my views.py

from django.shortcuts import render
from rest_framework.response import Response
from rest_framework import status
from rest_framework.decorators import api_view
from .models import Object_Locations
from .serializers import Object_LocationsSerializer
from django.contrib.postgres.search import SearchVector, SearchQuery


def index(request):
   return render(request, 'main/base.html', {})

@api_view(['GET',])
def LocationsList(request):
    if request.method == 'GET':
        vector = SearchVector('name', 'desc', 'catergory')
        query = request.GET.get('search')

        if query:
            locations = Object_Locations.objects.annotate(search=vector,).filter(search=SearchQuery(query))
        else:
            locations = Object_Locations.objects.all()

        serializer = Object_LocationsSerializer(locations, many=True)
        return Response(serializer.data)

Paolo Melchiorre · Accepted Answer · 2020-04-27 09:53:43Z

2

From the SearchQuery documentation :

SearchQuery translates the terms the user provides into a search query object that the database compares to a search vector. By default, all the words the user provides are passed through the stemming algorithms, and then it looks for matches for all of the resulting terms.

If search_type is 'plain', which is the default, the terms are treated as separate keywords. If search_type is 'phrase', the terms are treated as a single phrase. If search_type is 'raw', then you can provide a formatted search query with terms and operators.

In the code above the SearchQuery with the search phrase "a blac husky" is translated to a SQL code like:

... @@ plainto_tsquery('a blac husky') ...

So if you want to have results with similar search phrases it's possible to combine query terms:

from django.contrib.postgres.search import SearchQuery, SearchVector
from django.shortcuts import render
from rest_framework import status
from rest_framework.decorators import api_view
from rest_framework.response import Response

from .models import Object_Locations
from .serializers import Object_LocationsSerializer


def index(request):
return render(request, 'main/base.html', {})


@api_view(['GET',])
def LocationsList(request):
    if request.method == 'GET':
        vector = SearchVector('name', 'desc', 'catergory')
        query_terms = request.GET.get('search')
        query_raw = ' | '.join(query_terms.split())
        query = SearchQuery(query_raw, search_type='raw')
        if query.value:
            locations = Object_Locations.objects.annotate(search=vector).filter(search=query)
        else:
            locations = Object_Locations.objects.all()
        serializer = Object_LocationsSerializer(locations, many=True)
        return Response(serializer.data)

In the above code the SearchQuery with the search phrase "a blac husky" is translated to a SQL code like:

... @@ plainto_tsquery('a | blac | husky') ...

It's possible to use logical operation to combine SearchQuery as described in the documentation I linked.

If you want to deepen further you can read an article that I wrote on the subject:

"Full-Text Search in Django with PostgreSQL"

edited Apr 27, 2020 at 9:53

answered Apr 27, 2020 at 9:33

Paolo Melchiorre

6,1733 gold badges35 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Benassin Over a year ago

Thanks a lot. I will definitely check out your article. I have another question, can we combine this with Trigram Similarity to improve it even more like when we type "a blac husk" it will still display the result. Is it possible?

Paolo Melchiorre Over a year ago

Yes in the article I wrote example to use trigram and fts togheter

Collectives™ on Stack Overflow

PostgreSQL text search in Django not working as expected

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related