How to make/use a custom database function in Django

by

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')

More Related Contents:

  1. OperationalError: database is locked
  2. Django sort by distance
  3. GeoDjango on Windows: “Could not find the GDAL library” / “OSError: [WinError 126] The specified module could not be found”
  4. multiple databases and multiple models in django
  5. Django persistent database connection
  6. How do you join two tables on a foreign key field using django ORM?
  7. How to see the raw SQL queries Django is running?
  8. A QuerySet by aggregate field value
  9. How to move a model between two Django apps (Django 1.7)
  10. sqlite3.OperationalError: unable to open database file
  11. Race conditions in django
  12. What is your favorite solution for managing database migrations in django? [closed]
  13. How do I convert kilometres to degrees in Geodjango/GEOS?
  14. django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module: No module named MySQLdb
  15. Django: Increment blog entry view count by one. Is this efficient?
  16. django makemigrations and migrate on heroku server don’t create tables
  17. Django: add image in an ImageField from image url
  18. how to iterate through dictionary in a dictionary in django template?
  19. Django multi tenancy
  20. Django – How to make a variable available to all templates?
  21. manage.py runserver
  22. filter foreignkey field in django admin
  23. Where does pip install its packages?
  24. How can my Model primary key start with a specific number?
  25. Django Passing data between views
  26. Serving large files ( with high loads ) in Django
  27. can’t pip install mysql-python
  28. Django: using and POST
  29. ImportError: Could not import settings
  30. Django: When to customize save vs using post-save signal

Leave a Comment