17. How to use arbitrary database functions in querysets?

Django comes with functions like Lower, Coalesce and Max, but it can’t support all database functions, expecially ones which are database specific.

Django provides Func which allows using arbitrary database functions, even if Django doesn’t provide them.

Postgres has fuzzystrmatch, which provides several functions to determine similarities. Install the extension in your postgres DB with create extension fuzzystrmatch

We will use the levenshtein function. Lets first create some Hero objects.

Hero.objects.create(name="Zeus", description="A greek God", benevolence_factor=80, category_id=12, origin_id=1)
Hero.objects.create(name="ZeuX", description="A greek God", benevolence_factor=80, category_id=12, origin_id=1)
Hero.objects.create(name="Xeus", description="A greek God", benevolence_factor=80, category_id=12, origin_id=1)
Hero.objects.create(name="Poseidon", description="A greek God", benevolence_factor=80, category_id=12, origin_id=1)

We want to find out the Hero objects which have name similar to Zeus. You can do

from django.db.models import Func, F
Hero.objects.annotate(like_zeus=Func(F('name'), function='levenshtein', template="%(function)s(%(expressions)s, 'Zeus')"))

The like_zeus=Func(F('name'), function='levenshtein', template="%(function)s(%(expressions)s, 'Zeus')") took two arguments which allowed the database representation, viz, function and template. If you need to reuse the function, you can define a class like this.

class LevenshteinLikeZeus(Func):
    function='levenshtein'
    template="%(function)s(%(expressions)s, 'Zeus')"

And then use Hero.objects.annotate(like_zeus=LevenshteinLikeZeus(F("name")))

You can then filter on this annotated field like this.

In [16]: Hero.objects.annotate(
    ...:         like_zeus=LevenshteinLikeZeus(F("name"))
    ...:     ).filter(
    ...:         like_zeus__lt=2
    ...:     )
    ...:
Out[16]: <QuerySet [<Hero: Zeus>, <Hero: ZeuX>, <Hero: Xeus>]>