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.
- The class Func() is the most general part of
Django Query Expressions
- It allows the implementation of almost any function or operator into
Django ORMin some way
Func() expression is the base type of all expressions that involve database functions like
LOWER, or aggregates like
- I recommend reading Avoiding SQL injection before using
Following are some ways to write our 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'))))
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'))
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()