Django's database functions represent functions that will run in the database. It provides a way for users to use functions provided by the underlying database as annotations, aggregations, or filters. Functions are also expressions, so they can be used and combined with other expressions like aggregate functions.
One of the rich Django's features includes customization of its various functionalities. Yes, you got it right! ๐ We can customize Django database functions as per our needs.
๐ In this post, we will look into a couple of examples below to get the gist of writing custom functions based on our business needs.
๐ Let's first understand the Django Func()
class which serves as the basis for us to move forward.
๐ Django Func(*expressions, **extra)
class
- The class Func() is the most general part of
Django Query Expressions
- It allows the implementation of almost any function or operator into
Django ORM
in some way -
Func() expression is the base type of all expressions that involve database functions like
COALESCE
andLOWER
, or aggregates likeSUM
- I recommend reading Avoiding SQL injection before using
Func()
Following are some ways to write our custom database functions:
๐น Custom database functions
We can create our custom database functions using Django's Func class. In one of my projects, I wanted to covert the UTC
timestamp to IST
in Django filter with a specific date format. Writing two simple Django database functions helped me reuse
it at multiple instances as follows:
from django.db.models import Func
class TimestampToIST(Func):
""" Converts the db (UTC) timestamp value to IST equivalent timestamp
"""
function = 'timezone'
template = "%(function)s('Asia/Calcutta', %(expressions)s)"
class TimestampToStr(Func):
""" Converts the timestamp to string using the given format
"""
function = 'to_char'
template = "%(function)s(%(expressions)s, 'DD/MM/YYYY HH24:MI:SS')" # 21/06/2021 16:08:34
# Usage
Author.objects.annotate(last_updated=TimestampToStr(TimestampToIST(F('updated_at'))))
๐น Partial implementation of database functions
Another great customization example is to make a new version of the function with one or two arguments already filled in. For example, let's create a specialized SubStr
that extracts the first character from a string:
from functools import partial
from django.db.models.functions import Substr
ExtractFirstChar = partial(Substr, pos=1, length=1)
# Usage
User.objects.annotate(name_initial=ExtractFirstChar('first_name'))
๐น Executing a GROUP BY
without an aggregation function
Imagine a situation where we want to use GROUP BY
without using any aggregate function. Django ORM
does not allow us to use GROUP BY
without an aggregate function ๐คจ Hence, to accomplish this, we can create a Django function that is treated as an aggregate function by Django but evaluates to NULL
in a SQL query
, as sourced from StackOverflow
from django.db.models import CharField, Func
class NullAgg(Func):
"""Annotation that causes GROUP BY without aggregating.
A fake aggregate Func class that can be used in an annotation to cause
a query to perform a GROUP BY without also performing an aggregate
operation that would require the server to enumerate all rows in every
group.
Takes no constructor arguments and produces a value of NULL.
Example:
ContentType.objects.values('app_label').annotate(na=NullAgg())
"""
template = 'NULL'
contains_aggregate = True
window_compatible = False
arity = 0
output_field = CharField()
Top comments (2)
Excellent explanations!
I highly recommend your blog!
Thanks for sharing.
Thanks @stormytalent ๐
Glad that you liked it.