[Django]-How to make/use a custom database function in Django

by

23👍

✅

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')
👤John Moutafis

Leave a comment