pythondjangodatabasegeodjangodjango-database-functions

How to make/use a custom database function in Django


Prologue:

This is a question arising often in SO:

and can be applied to the above as well as in the following:

I wanted to compose an example on SO Documentation but since it got shut down on August 8, 2017, I will follow the suggestion of this widely upvoted and discussed meta answer and write my example as a self-answered post.

Of course, I would be more than happy to see any different approach as well!!


Question:

Django/GeoDjango has some database functions like Lower() or MakeValid() which can be used like this:

Author.objects.create(name='Margaret Smith')
author = Author.objects.annotate(name_lower=Lower('name')).get()
print(author.name_lower)

Is there any way to use and/or create my own custom database function based on existing database functions like:

How can I apply/use those functions in Django/GeoDjango ORM?


Solution

  • Django provides the Func() expression to facilitate the calling of database functions in a queryset:

    Func() expressions are the base type of all expressions that involve database functions like COALESCE and LOWER, or aggregates like SUM.

    There are 2 options on how to use a database function in Django/GeoDjango ORM:

    For convenience, let us assume that the model is named MyModel and that the substring is stored in a variable named subst:

    from django.contrib.gis.db import models as gis_models
    
    class MyModel(models.Model):
        name = models.CharField()
        the_geom = gis_models.PolygonField()
    
    1. Use Func()to call the function directly:

      We will also need the following to make our queries work:

      The query:

      MyModel.objects.aggregate(
          pos=Func(F('name'), Value(subst), function='POSITION')
      )
      
    2. Create your own database function extending Func:

      We can extend Func class to create our own database functions:

      class Position(Func):
          function = 'POSITION'
      

      and use it in a query:

      MyModel.objects.aggregate(pos=Position('name', Value(subst)))
      

    GeoDjango Appendix:

    In GeoDjango, in order to import a GIS related function (like PostGIS's Transform function) the Func() method must be replaced by GeoFunc(), but it is essentially used under the same principles:

    class Transform(GeoFunc):
        function='ST_Transform'
    

    There are more complex cases of GeoFunc usage and an interesting use case has emerged here: How to calculate Frechet Distance in Django?


    Generalize custom database function Appendix:

    In case that you want to create a custom database function (Option 2) and you want to be able to use it with any database without knowing it beforehand, you can use Func's as_<database-name> method, provided that the function you want to use exists in every database:

    class Position(Func):
        function = 'POSITION' # MySQL method
    
        def as_sqlite(self, compiler, connection):
            #SQLite method
            return self.as_sql(compiler, connection, function='INSTR')
    
        def as_postgresql(self, compiler, connection):
            # PostgreSQL method
            return self.as_sql(compiler, connection, function='STRPOS')