F() expressions¶

class F¶

An F() object represents the value of a model field, transformed value of a model field, or annotated column. It makes it possible to refer to model field values and perform database operations using them without actually having to pull them out of the database into Python memory.

Instead, Django uses the F() object to generate an SQL expression that describes the required operation at the database level.

Let’s try this with an example. Normally, one might do something like this:

# Tintin filed a news story!
reporter = Reporters.objects.get(name="Tintin")
reporter.stories_filed += 1
reporter.save()

Here, we have pulled the value of reporter.stories_filed from the database into memory and manipulated it using familiar Python operators, and then saved the object back to the database. But instead we could also have done:

from django.db.models import F

reporter = Reporters.objects.get(name="Tintin")
reporter.stories_filed = F("stories_filed") + 1
reporter.save()

Although reporter.stories_filed = F('stories_filed') + 1 looks like a normal Python assignment of value to an instance attribute, in fact it’s an SQL construct describing an operation on the database.

When Django encounters an instance of F(), it overrides the standard Python operators to create an encapsulated SQL expression; in this case, one which instructs the database to increment the database field represented by reporter.stories_filed.

Whatever value is or was on reporter.stories_filed, Python never gets to know about it – it is dealt with entirely by the database. All Python does, through Django’s F() class, is create the SQL syntax to refer to the field and describe the operation.

To access the new value saved this way, the object must be reloaded:

reporter = Reporters.objects.get(pk=reporter.pk)
# Or, more succinctly:
reporter.refresh_from_db()

As well as being used in operations on single instances as above, F() can be used on QuerySets of object instances, with update(). This reduces the two queries we were using above – the get() and the save() – to just one:

reporter = Reporters.objects.filter(name="Tintin")
reporter.update(stories_filed=F("stories_filed") + 1)

We can also use update() to increment the field value on multiple objects – which could be very much faster than pulling them all into Python from the database, looping over them, incrementing the field value of each one, and saving each one back to the database:

Reporter.objects.update(stories_filed=F("stories_filed") + 1)

F() therefore can offer performance advantages by:

• getting the database, rather than Python, to do work
• reducing the number of queries some operations require

reporter = Reporters.objects.get(name="Tintin")
reporter.stories_filed = F("stories_filed") + 1
reporter.save()

reporter.name = "Tintin Jr."
reporter.save()

company = Company.objects.annotate(chairs_needed=F("num_employees") - F("num_chairs"))

from django.db.models import DateTimeField, ExpressionWrapper, F

Ticket.objects.annotate(
    expires=ExpressionWrapper(
        F("active_at") + F("duration"), output_field=DateTimeField()
    )
)

>>> car = Company.objects.annotate(built_by=F("manufacturer"))[0]
>>> car.manufacturer
<Manufacturer: Toyota>
>>> car.built_by
3

from django.db.models import F

Company.objects.order_by(F("last_contacted").desc(nulls_last=True))

from django.db.models import F

Company.objects.update(is_active=~F("is_active"))

Func() expressions¶

Func() expressions are the base type of all expressions that involve database functions like COALESCE and LOWER, or aggregates like SUM. They can be used directly:

from django.db.models import F, Func

queryset.annotate(field_lower=Func(F("field"), function="LOWER"))

or they can be used to build a library of database functions:

class Lower(Func):
    function = "LOWER"


queryset.annotate(field_lower=Lower("field"))

But both cases will result in a queryset where each model is annotated with an extra attribute field_lower produced, roughly, from the following SQL:

SELECT
    ...
    LOWER("db_table"."field") as "field_lower"

See Database Functions for a list of built-in database functions.

The Func API is as follows:

class Func(*expressions, **extra)¶

function¶

A class attribute describing the function that will be generated. Specifically, the function will be interpolated as the function placeholder within template. Defaults to None.

template¶

A class attribute, as a format string, that describes the SQL that is generated for this function. Defaults to '%(function)s(%(expressions)s)'.

If you’re constructing SQL like strftime('%W', 'date') and need a literal % character in the query, quadruple it (%%%%) in the template attribute because the string is interpolated twice: once during the template interpolation in as_sql() and once in the SQL interpolation with the query parameters in the database cursor.

arg_joiner¶

A class attribute that denotes the character used to join the list of expressions together. Defaults to ', '.

arity¶

A class attribute that denotes the number of arguments the function accepts. If this attribute is set and the function is called with a different number of expressions, TypeError will be raised. Defaults to None.

as_sql(compiler, connection, function=None, template=None, arg_joiner=None, **extra_context)¶

Generates the SQL fragment for the database function. Returns a tuple (sql, params), where sql is the SQL string, and params is the list or tuple of query parameters.

The as_vendor() methods should use the function, template, arg_joiner, and any other **extra_context parameters to customize the SQL as needed. For example:

django/db/models/functions.py¶

class ConcatPair(Func):
    ...
    function = "CONCAT"
    ...

    def as_mysql(self, compiler, connection, **extra_context):
        return super().as_sql(
            compiler,
            connection,
            function="CONCAT_WS",
            template="%(function)s('', %(expressions)s)",
            **extra_context
        )

To avoid an SQL injection vulnerability, extra_context must not contain untrusted user input as these values are interpolated into the SQL string rather than passed as query parameters, where the database driver would escape them.

The *expressions argument is a list of positional expressions that the function will be applied to. The expressions will be converted to strings, joined together with arg_joiner, and then interpolated into the template as the expressions placeholder.

Positional arguments can be expressions or Python values. Strings are assumed to be column references and will be wrapped in F() expressions while other values will be wrapped in Value() expressions.

The **extra kwargs are key=value pairs that can be interpolated into the template attribute. To avoid an SQL injection vulnerability, extra must not contain untrusted user input as these values are interpolated into the SQL string rather than passed as query parameters, where the database driver would escape them.

The function, template, and arg_joiner keywords can be used to replace the attributes of the same name without having to define your own class. output_field can be used to define the expected return type.

Aggregate() expressions¶

An aggregate expression is a special case of a Func() expression that informs the query that a GROUP BY clause is required. All of the aggregate functions, like Sum() and Count(), inherit from Aggregate().

Since Aggregates are expressions and wrap expressions, you can represent some complex computations:

from django.db.models import Count

Company.objects.annotate(
    managers_required=(Count("num_employees") / 4) + Count("num_managers")
)

The Aggregate API is as follows:

class Aggregate(*expressions, output_field=None, distinct=False, filter=None, default=None, **extra)¶

template¶

A class attribute, as a format string, that describes the SQL that is generated for this aggregate. Defaults to '%(function)s(%(distinct)s%(expressions)s)'.

function¶

A class attribute describing the aggregate function that will be generated. Specifically, the function will be interpolated as the function placeholder within template. Defaults to None.

window_compatible¶

Defaults to True since most aggregate functions can be used as the source expression in Window.

allow_distinct¶

A class attribute determining whether or not this aggregate function allows passing a distinct keyword argument. If set to False (default), TypeError is raised if distinct=True is passed.

empty_result_set_value¶

Defaults to None since most aggregate functions result in NULL when applied to an empty result set.

The expressions positional arguments can include expressions, transforms of the model field, or the names of model fields. They will be converted to a string and used as the expressions placeholder within the template.

The distinct argument determines whether or not the aggregate function should be invoked for each distinct value of expressions (or set of values, for multiple expressions). The argument is only supported on aggregates that have allow_distinct set to True.

The filter argument takes a Q object that’s used to filter the rows that are aggregated. See Conditional aggregation and Filtering on annotations for example usage.

The default argument takes a value that will be passed along with the aggregate to Coalesce. This is useful for specifying a value to be returned other than None when the queryset (or grouping) contains no entries.

The **extra kwargs are key=value pairs that can be interpolated into the template attribute.

Creating your own Aggregate Functions¶

You can create your own aggregate functions, too. At a minimum, you need to define function, but you can also completely customize the SQL that is generated. Here’s a brief example:

from django.db.models import Aggregate


class Sum(Aggregate):
    # Supports SUM(ALL field).
    function = "SUM"
    template = "%(function)s(%(all_values)s%(expressions)s)"
    allow_distinct = False

    def __init__(self, expression, all_values=False, **extra):
        super().__init__(expression, all_values="ALL " if all_values else "", **extra)

Value() expressions¶

class Value(value, output_field=None)¶

A Value() object represents the smallest possible component of an expression: a simple value. When you need to represent the value of an integer, boolean, or string within an expression, you can wrap that value within a Value().

You will rarely need to use Value() directly. When you write the expression F('field') + 1, Django implicitly wraps the 1 in a Value(), allowing simple values to be used in more complex expressions. You will need to use Value() when you want to pass a string to an expression. Most expressions interpret a string argument as the name of a field, like Lower('name').

The value argument describes the value to be included in the expression, such as 1, True, or None. Django knows how to convert these Python values into their corresponding database type.

If no output_field is specified, it will be inferred from the type of the provided value for many common types. For example, passing an instance of datetime.datetime as value defaults output_field to DateTimeField.

ExpressionWrapper() expressions¶

class ExpressionWrapper(expression, output_field)¶

ExpressionWrapper surrounds another expression and provides access to properties, such as output_field, that may not be available on other expressions. ExpressionWrapper is necessary when using arithmetic on F() expressions with different types as described in Using F() with annotations.

Conditional expressions¶

Conditional expressions allow you to use if … elif … else logic in queries. Django natively supports SQL CASE expressions. For more details see Conditional Expressions.

Subquery() expressions¶

class Subquery(queryset, output_field=None)¶

You can add an explicit subquery to a QuerySet using the Subquery expression.

For example, to annotate each post with the email address of the author of the newest comment on that post:

>>> from django.db.models import OuterRef, Subquery
>>> newest = Comment.objects.filter(post=OuterRef("pk")).order_by("-created_at")
>>> Post.objects.annotate(newest_commenter_email=Subquery(newest.values("email")[:1]))

On PostgreSQL, the SQL looks like:

SELECT "post"."id", (
    SELECT U0."email"
    FROM "comment" U0
    WHERE U0."post_id" = ("post"."id")
    ORDER BY U0."created_at" DESC LIMIT 1
) AS "newest_commenter_email" FROM "post"

>>> Book.objects.filter(author=OuterRef(OuterRef("pk")))

>>> from datetime import timedelta
>>> from django.utils import timezone
>>> one_day_ago = timezone.now() - timedelta(days=1)
>>> posts = Post.objects.filter(published_at__gte=one_day_ago)
>>> Comment.objects.filter(post__in=Subquery(posts.values("pk")))

>>> subquery = Subquery(newest.values("email")[:1])
>>> Post.objects.annotate(newest_commenter_email=subquery)

>>> from django.db.models import Exists, OuterRef
>>> from datetime import timedelta
>>> from django.utils import timezone
>>> one_day_ago = timezone.now() - timedelta(days=1)
>>> recent_comments = Comment.objects.filter(
...     post=OuterRef("pk"),
...     created_at__gte=one_day_ago,
... )
>>> Post.objects.annotate(recent_comment=Exists(recent_comments))

SELECT "post"."id", "post"."published_at", EXISTS(
    SELECT (1) as "a"
    FROM "comment" U0
    WHERE (
        U0."created_at" >= YYYY-MM-DD HH:MM:SS AND
        U0."post_id" = "post"."id"
    )
    LIMIT 1
) AS "recent_comment" FROM "post"

>>> recent_comments = Comment.objects.filter(...)  # From above
>>> Post.objects.filter(Exists(recent_comments))

>>> from django.db.models import OuterRef, Subquery, Sum
>>> comments = Comment.objects.filter(post=OuterRef("pk")).order_by().values("post")
>>> total_comments = comments.annotate(total=Sum("length")).values("total")
>>> Post.objects.filter(length__gt=Subquery(total_comments))

Raw SQL expressions¶

class RawSQL(sql, params, output_field=None)¶

Sometimes database expressions can’t easily express a complex WHERE clause. In these edge cases, use the RawSQL expression. For example:

>>> from django.db.models.expressions import RawSQL
>>> queryset.annotate(val=RawSQL("select col from sometable where othercol = %s", (param,)))

These extra lookups may not be portable to different database engines (because you’re explicitly writing SQL code) and violate the DRY principle, so you should avoid them if possible.

RawSQL expressions can also be used as the target of __in filters:

>>> queryset.filter(id__in=RawSQL("select id from sometable where col = %s", (param,)))

RawSQL("select col from sometable where othercol = '%s'")  # unsafe!

Window functions¶

Window functions provide a way to apply functions on partitions. Unlike a normal aggregation function which computes a final result for each set defined by the group by, window functions operate on frames and partitions, and compute the result for each row.

You can specify multiple windows in the same query which in Django ORM would be equivalent to including multiple expressions in a QuerySet.annotate() call. The ORM doesn’t make use of named windows, instead they are part of the selected columns.

class Window(expression, partition_by=None, order_by=None, frame=None, output_field=None)¶

template¶

Defaults to %(expression)s OVER (%(window)s). If only the expression argument is provided, the window clause will be blank.

The Window class is the main expression for an OVER clause.

The expression argument is either a window function, an aggregate function, or an expression that’s compatible in a window clause.

The partition_by argument accepts an expression or a sequence of expressions (column names should be wrapped in an F-object) that control the partitioning of the rows. Partitioning narrows which rows are used to compute the result set.

The output_field is specified either as an argument or by the expression.

The order_by argument accepts an expression on which you can call asc() and desc(), a string of a field name (with an optional "-" prefix which indicates descending order), or a tuple or list of strings and/or expressions. The ordering controls the order in which the expression is applied. For example, if you sum over the rows in a partition, the first result is the value of the first row, the second is the sum of first and second row.

The frame parameter specifies which other rows that should be used in the computation. See Frames for details.

For example, to annotate each movie with the average rating for the movies by the same studio in the same genre and release year:

>>> from django.db.models import Avg, F, Window
>>> Movie.objects.annotate(
...     avg_rating=Window(
...         expression=Avg("rating"),
...         partition_by=[F("studio"), F("genre")],
...         order_by="released__year",
...     ),
... )

This allows you to check if a movie is rated better or worse than its peers.

You may want to apply multiple expressions over the same window, i.e., the same partition and frame. For example, you could modify the previous example to also include the best and worst rating in each movie’s group (same studio, genre, and release year) by using three window functions in the same query. The partition and ordering from the previous example is extracted into a dictionary to reduce repetition:

>>> from django.db.models import Avg, F, Max, Min, Window
>>> window = {
...     "partition_by": [F("studio"), F("genre")],
...     "order_by": "released__year",
... }
>>> Movie.objects.annotate(
...     avg_rating=Window(
...         expression=Avg("rating"),
...         **window,
...     ),
...     best=Window(
...         expression=Max("rating"),
...         **window,
...     ),
...     worst=Window(
...         expression=Min("rating"),
...         **window,
...     ),
... )

Filtering against window functions is supported as long as lookups are not disjunctive (not using OR or XOR as a connector) and against a queryset performing aggregation.

For example, a query that relies on aggregation and has an OR-ed filter against a window function and a field is not supported. Applying combined predicates post-aggregation could cause rows that would normally be excluded from groups to be included:

>>> qs = Movie.objects.annotate(
...     category_rank=Window(Rank(), partition_by="category", order_by="-rating"),
...     scenes_count=Count("actors"),
... ).filter(Q(category_rank__lte=3) | Q(title__contains="Batman"))
>>> list(qs)
NotImplementedError: Heterogeneous disjunctive predicates against window functions
are not implemented when performing conditional aggregation.

Among Django’s built-in database backends, MySQL 8.0.2+, PostgreSQL, and Oracle support window expressions. Support for different window expression features varies among the different databases. For example, the options in asc() and desc() may not be supported. Consult the documentation for your database as needed.

%(frame_type)s BETWEEN %(start)s AND %(end)s

ValueRange(start=0, end=0)

>>> from django.db.models import Avg, F, RowRange, Window
>>> Movie.objects.annotate(
...     avg_rating=Window(
...         expression=Avg("rating"),
...         partition_by=[F("studio"), F("genre")],
...         order_by="released__year",
...         frame=RowRange(start=-2, end=2),
...     ),
... )

>>> from django.db.models import Avg, F, ValueRange, Window
>>> Movie.objects.annotate(
...     avg_rating=Window(
...         expression=Avg("rating"),
...         partition_by=[F("studio"), F("genre")],
...         order_by="released__year",
...         frame=ValueRange(start=-12, end=12),
...     ),
... )

Expression API¶

Query expressions implement the query expression API, but also expose a number of extra methods and attributes listed below. All query expressions must inherit from Expression() or a relevant subclass.

When a query expression wraps another expression, it is responsible for calling the appropriate methods on the wrapped expression.

class Expression¶

contains_aggregate¶

Tells Django that this expression contains an aggregate and that a GROUP BY clause needs to be added to the query.

contains_over_clause¶

Tells Django that this expression contains a Window expression. It’s used, for example, to disallow window function expressions in queries that modify data.

filterable¶

Tells Django that this expression can be referenced in QuerySet.filter(). Defaults to True.

window_compatible¶

Tells Django that this expression can be used as the source expression in Window. Defaults to False.

empty_result_set_value¶

Tells Django which value should be returned when the expression is used to apply a function over an empty result set. Defaults to NotImplemented which forces the expression to be computed on the database.

resolve_expression(query=None, allow_joins=True, reuse=None, summarize=False, for_save=False)¶

Provides the chance to do any preprocessing or validation of the expression before it’s added to the query. resolve_expression() must also be called on any nested expressions. A copy() of self should be returned with any necessary transformations.

query is the backend query implementation.

allow_joins is a boolean that allows or denies the use of joins in the query.

reuse is a set of reusable joins for multi-join scenarios.

summarize is a boolean that, when True, signals that the query being computed is a terminal aggregate query.

for_save is a boolean that, when True, signals that the query being executed is performing a create or update.

get_source_expressions()¶

Returns an ordered list of inner expressions. For example:

>>> Sum(F("foo")).get_source_expressions()
[F('foo')]

set_source_expressions(expressions)¶

Takes a list of expressions and stores them such that get_source_expressions() can return them.

relabeled_clone(change_map)¶

Returns a clone (copy) of self, with any column aliases relabeled. Column aliases are renamed when subqueries are created. relabeled_clone() should also be called on any nested expressions and assigned to the clone.

change_map is a dictionary mapping old aliases to new aliases.

Example:

def relabeled_clone(self, change_map):
    clone = copy.copy(self)
    clone.expression = self.expression.relabeled_clone(change_map)
    return clone

convert_value(value, expression, connection)¶

A hook allowing the expression to coerce value into a more appropriate type.

expression is the same as self.

get_group_by_cols()¶

Responsible for returning the list of columns references by this expression. get_group_by_cols() should be called on any nested expressions. F() objects, in particular, hold a reference to a column.

Changed in Django 4.2:

The alias=None keyword argument was removed.

asc(nulls_first=None, nulls_last=None)¶

Returns the expression ready to be sorted in ascending order.

nulls_first and nulls_last define how null values are sorted. See Using F() to sort null values for example usage.

Changed in Django 4.1:

In older versions, nulls_first and nulls_last defaulted to False.

Obsoleto desde la versión 4.1: Passing nulls_first=False or nulls_last=False to asc() is deprecated. Use None instead.

desc(nulls_first=None, nulls_last=None)¶

Returns the expression ready to be sorted in descending order.

nulls_first and nulls_last define how null values are sorted. See Using F() to sort null values for example usage.

Changed in Django 4.1:

In older versions, nulls_first and nulls_last defaulted to False.

Obsoleto desde la versión 4.1: Passing nulls_first=False or nulls_last=False to desc() is deprecated. Use None instead.

reverse_ordering()¶

Returns self with any modifications required to reverse the sort order within an order_by call. As an example, an expression implementing NULLS LAST would change its value to be NULLS FIRST. Modifications are only required for expressions that implement sort order like OrderBy. This method is called when reverse() is called on a queryset.

Writing your own Query Expressions¶

You can write your own query expression classes that use, and can integrate with, other query expressions. Let’s step through an example by writing an implementation of the COALESCE SQL function, without using the built-in Func() expressions.

The COALESCE SQL function is defined as taking a list of columns or values. It will return the first column or value that isn’t NULL.

We’ll start by defining the template to be used for SQL generation and an __init__() method to set some attributes:

import copy
from django.db.models import Expression


class Coalesce(Expression):
    template = "COALESCE( %(expressions)s )"

    def __init__(self, expressions, output_field):
        super().__init__(output_field=output_field)
        if len(expressions) < 2:
            raise ValueError("expressions must have at least 2 elements")
        for expression in expressions:
            if not hasattr(expression, "resolve_expression"):
                raise TypeError("%r is not an Expression" % expression)
        self.expressions = expressions

We do some basic validation on the parameters, including requiring at least 2 columns or values, and ensuring they are expressions. We are requiring output_field here so that Django knows what kind of model field to assign the eventual result to.

Now we implement the preprocessing and validation. Since we do not have any of our own validation at this point, we delegate to the nested expressions:

def resolve_expression(
    self, query=None, allow_joins=True, reuse=None, summarize=False, for_save=False
):
    c = self.copy()
    c.is_summary = summarize
    for pos, expression in enumerate(self.expressions):
        c.expressions[pos] = expression.resolve_expression(
            query, allow_joins, reuse, summarize, for_save
        )
    return c

Next, we write the method responsible for generating the SQL:

def as_sql(self, compiler, connection, template=None):
    sql_expressions, sql_params = [], []
    for expression in self.expressions:
        sql, params = compiler.compile(expression)
        sql_expressions.append(sql)
        sql_params.extend(params)
    template = template or self.template
    data = {"expressions": ",".join(sql_expressions)}
    return template % data, sql_params


def as_oracle(self, compiler, connection):
    """
    Example of vendor specific handling (Oracle in this case).
    Let's make the function name lowercase.
    """
    return self.as_sql(compiler, connection, template="coalesce( %(expressions)s )")

as_sql() methods can support custom keyword arguments, allowing as_vendorname() methods to override data used to generate the SQL string. Using as_sql() keyword arguments for customization is preferable to mutating self within as_vendorname() methods as the latter can lead to errors when running on different database backends. If your class relies on class attributes to define data, consider allowing overrides in your as_sql() method.

We generate the SQL for each of the expressions by using the compiler.compile() method, and join the result together with commas. Then the template is filled out with our data and the SQL and parameters are returned.

We’ve also defined a custom implementation that is specific to the Oracle backend. The as_oracle() function will be called instead of as_sql() if the Oracle backend is in use.

Finally, we implement the rest of the methods that allow our query expression to play nice with other query expressions:

def get_source_expressions(self):
    return self.expressions


def set_source_expressions(self, expressions):
    self.expressions = expressions

Let’s see how it works:

>>> from django.db.models import F, Value, CharField
>>> qs = Company.objects.annotate(
...     tagline=Coalesce(
...         [F("motto"), F("ticker_name"), F("description"), Value("No Tagline")],
...         output_field=CharField(),
...     )
... )
>>> for c in qs:
...     print("%s: %s" % (c.name, c.tagline))
...
Google: Do No Evil
Apple: AAPL
Yahoo: Internet Company
Django Software Foundation: No Tagline

from django.db.models import Func


class Position(Func):
    function = "POSITION"
    template = "%(function)s('%(substring)s' in %(expressions)s)"

    def __init__(self, expression, substring):
        # substring=substring is an SQL injection vulnerability!
        super().__init__(expression, substring=substring)

class Position(Func):
    function = "POSITION"
    arg_joiner = " IN "

    def __init__(self, expression, substring):
        super().__init__(substring, expression)

Adding support in third-party database backends¶

If you’re using a database backend that uses a different SQL syntax for a certain function, you can add support for it by monkey patching a new method onto the function’s class.

Let’s say we’re writing a backend for Microsoft’s SQL Server which uses the SQL LEN instead of LENGTH for the Length function. We’ll monkey patch a new method called as_sqlserver() onto the Length class:

from django.db.models.functions import Length


def sqlserver_length(self, compiler, connection):
    return self.as_sql(compiler, connection, function="LEN")


Length.as_sqlserver = sqlserver_length

You can also customize the SQL using the template parameter of as_sql().

We use as_sqlserver() because django.db.connection.vendor returns sqlserver for the backend.

Third-party backends can register their functions in the top level __init__.py file of the backend package or in a top level expressions.py file (or package) that is imported from the top level __init__.py.

For user projects wishing to patch the backend that they’re using, this code should live in an AppConfig.ready() method.