Query Expressions

Query expressions describe a value or a computation that can be used as part ofan update, create, filter, order by, annotation, or aggregate. When anexpression outputs a boolean value, it may be used directly in filters. Thereare a number of built-in expressions (documented below) that can be used tohelp you write queries. Expressions can be combined, or in some cases nested,to form more complex computations.

Supported arithmetic

Django supports negation, addition, subtraction, multiplication, division,modulo arithmetic, and the power operator on query expressions, using Pythonconstants, variables, and even other expressions.

Some examples

  1. from django.db.models import Count, F, Value
  2. from django.db.models.functions import Length, Upper
  3.  
  4. # Find companies that have more employees than chairs.
  5. Company.objects.filter(num_employees__gt=F('num_chairs'))
  6.  
  7. # Find companies that have at least twice as many employees
  8. # as chairs. Both the querysets below are equivalent.
  9. Company.objects.filter(num_employees__gt=F('num_chairs') * 2)
  10. Company.objects.filter(
  11.     num_employees__gt=F('num_chairs') + F('num_chairs'))
  12.  
  13. # How many chairs are needed for each company to seat all employees?
  14. >>> company = Company.objects.filter(
  15. ...    num_employees__gt=F('num_chairs')).annotate(
  16. ...    chairs_needed=F('num_employees') - F('num_chairs')).first()
  17. >>> company.num_employees
  18. 120
  19. >>> company.num_chairs
  20. 50
  21. >>> company.chairs_needed
  22. 70
  23.  
  24. # Create a new company using expressions.
  25. >>> company = Company.objects.create(name='Google', ticker=Upper(Value('goog')))
  26. # Be sure to refresh it if you need to access the field.
  27. >>> company.refresh_from_db()
  28. >>> company.ticker
  29. 'GOOG'
  30.  
  31. # Annotate models with an aggregated value. Both forms
  32. # below are equivalent.
  33. Company.objects.annotate(num_products=Count('products'))
  34. Company.objects.annotate(num_products=Count(F('products')))
  35.  
  36. # Aggregates can contain complex computations also
  37. Company.objects.annotate(num_offerings=Count(F('products') + F('services')))
  38.  
  39. # Expressions can also be used in order_by(), either directly
  40. Company.objects.order_by(Length('name').asc())
  41. Company.objects.order_by(Length('name').desc())
  42. # or using the double underscore lookup syntax.
  43. from django.db.models import CharField
  44. from django.db.models.functions import Length
  45. CharField.register_lookup(Length)
  46. Company.objects.order_by('name__length')
  47.  
  48. # Boolean expression can be used directly in filters.
  49. from django.db.models import Exists
  50. Company.objects.filter(
  51.     Exists(Employee.objects.filter(company=OuterRef('pk'), salary__gt=10))
  52. )

Built-in Expressions

Note

These expressions are defined in django.db.models.expressions anddjango.db.models.aggregates, but for convenience they’re available andusually imported from django.db.models.

F() expressions

  • class F
  • An F() object represents the value of a model field or annotated column. Itmakes it possible to refer to model field values and perform databaseoperations using them without actually having to pull them out of the databaseinto Python memory.

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

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

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

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

  1. from django.db.models import F
  2.  
  3. reporter = Reporters.objects.get(name='Tintin')
  4. reporter.stories_filed = F('stories_filed') + 1
  5. reporter.save()

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

When Django encounters an instance of F(), it overrides the standard Pythonoperators to create an encapsulated SQL expression; in this case, one whichinstructs the database to increment the database field represented byreporter.stories_filed.

Whatever value is or was on reporter.stories_filed, Python never gets toknow 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 fieldand describe the operation.

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

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

As well as being used in operations on single instances as above, F() canbe used on QuerySets of object instances, with update(). This reducesthe two queries we were using above - the get() and thesave() - to just one:

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

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

  1. Reporter.objects.all().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

Avoiding race conditions using F()

Another useful benefit of F() is that having the database - rather thanPython - update a field’s value avoids a race condition.

If two Python threads execute the code in the first example above, one threadcould retrieve, increment, and save a field’s value after the other hasretrieved it from the database. The value that the second thread saves will bebased on the original value; the work of the first thread will be lost.

If the database is responsible for updating the field, the process is morerobust: it will only ever update the field based on the value of the field inthe database when the save() or update() is executed, ratherthan based on its value when the instance was retrieved.

F() assignments persist after Model.save()

F() objects assigned to model fields persist after saving the modelinstance and will be applied on each save(). For example:

  1. reporter = Reporters.objects.get(name='Tintin')
  2. reporter.stories_filed = F('stories_filed') + 1
  3. reporter.save()
  4.  
  5. reporter.name = 'Tintin Jr.'
  6. reporter.save()

stories_filed will be updated twice in this case. If it’s initially 1,the final value will be 3. This persistence can be avoided by reloading themodel object after saving it, for example, by usingrefresh_from_db().

Using F() in filters

F() is also very useful in QuerySet filters, where they make itpossible to filter a set of objects against criteria based on their fieldvalues, rather than on Python values.

This is documented in using F() expressions in queries.

Using F() with annotations

F() can be used to create dynamic fields on your models by combiningdifferent fields with arithmetic:

  1. company = Company.objects.annotate(
  2.     chairs_needed=F('num_employees') - F('num_chairs'))

If the fields that you’re combining are of different types you’ll needto tell Django what kind of field will be returned. Since F() does notdirectly support output_field you will need to wrap the expression withExpressionWrapper:

  1. from django.db.models import DateTimeField, ExpressionWrapper, F
  2.  
  3. Ticket.objects.annotate(
  4.     expires=ExpressionWrapper(
  5.         F('active_at') + F('duration'), output_field=DateTimeField()))

When referencing relational fields such as ForeignKey, F() returns theprimary key value rather than a model instance:

  1. >> car = Company.objects.annotate(built_by=F('manufacturer'))[0]
  2. >> car.manufacturer
  3. <Manufacturer: Toyota>
  4. >> car.built_by
  5. 3

Using F() to sort null values

Use F() and the nulls_first or nulls_last keyword argument toExpression.asc() or desc() to control the ordering ofa field’s null values. By default, the ordering depends on your database.

For example, to sort companies that haven’t been contacted (last_contactedis null) after companies that have been contacted:

  1. from django.db.models import F
  2. Company.objects.order_by(F('last_contacted').desc(nulls_last=True))

Func() expressions

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

  1. from django.db.models import F, Func
  2.  
  3. queryset.annotate(field_lower=Func(F('field'), function='LOWER'))

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

  1. class Lower(Func):
  2.     function = 'LOWER'
  3.  
  4. queryset.annotate(field_lower=Lower('field'))

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

  1. SELECT
  2.     ...
  3.     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 functionplaceholder within template. Defaults to None.

    • template

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

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

  • arg_joiner

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

  • arity

  • A class attribute that denotes the number of arguments the functionaccepts. If this attribute is set and the function is called with adifferent number of expressions, TypeError will be raised. Defaultsto None.

  • assql(_compiler, connection, function=None, template=None, arg_joiner=None, **extra_context)

  • Generates the SQL for the database function.

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

django/db/models/functions.py

  1. class ConcatPair(Func):
  2.     ...
  3.     function = 'CONCAT'
  4.     ...
  5.  
  6.     def as_mysql(self, compiler, connection, **extra_context):
  7.         return super().as_sql(
  8.             compiler, connection,
  9.             function='CONCAT_WS',
  10.             template="%(function)s('', %(expressions)s)",
  11.             **extra_context
  12.         )

To avoid a SQL injection vulnerability, extra_context mustnot contain untrusted user inputas these values are interpolated into the SQL string rather than passedas query parameters, where the database driver would escape them.

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

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

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

The function, template, and arg_joiner keywords can be used toreplace the attributes of the same name without having to define your ownclass. 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 clauseis required. All of the aggregate functions,like Sum() and Count(), inherit from Aggregate().

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

  1. from django.db.models import Count
  2.  
  3. Company.objects.annotate(
  4.     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, **extra)
    • template

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

    • function

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

    • window_compatible

    • Defaults to True since most aggregate functions can be used as thesource expression in Window.

    • allow_distinct

    • New in Django 2.2:

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

The expressions positional arguments can include expressions or the namesof model fields. They will be converted to a string and used as theexpressions placeholder within the template.

The output_field argument requires a model field instance, likeIntegerField() or BooleanField(), into which Django will load the valueafter it’s retrieved from the database. Usually no arguments are needed wheninstantiating the model field as any arguments relating to data validation(max_length, max_digits, etc.) will not be enforced on the expression’soutput value.

Note that output_field is only required when Django is unable to determinewhat field type the result should be. Complex expressions that mix field typesshould define the desired output_field. For example, adding anIntegerField() and a FloatField() together should probably haveoutput_field=FloatField() defined.

The distinct argument determines whether or not the aggregate functionshould be invoked for each distinct value of expressions (or set ofvalues, for multiple expressions). The argument is only supported onaggregates that have allow_distinct set to True.

The filter argument takes a Q object that’sused to filter the rows that are aggregated. See Conditional aggregationand Filtering on annotations for example usage.

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

New in Django 2.2:The allow_distinct attribute and distinct argument were added.

Creating your own Aggregate Functions

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

  1. from django.db.models import Aggregate
  2.  
  3. class Sum(Aggregate):
  4.     # Supports SUM(ALL field).
  5.     function = 'SUM'
  6.     template = '%(function)s(%(all_values)s%(expressions)s)'
  7.     allow_distinct = False
  8.  
  9.     def __init__(self, expression, all_values=False, **extra):
  10.         super().__init__(
  11.             expression,
  12.             all_values='ALL ' if all_values else '',
  13.             **extra
  14.         )

Value() expressions

  • class Value(value, output_field=None)
  • A Value() object represents the smallest possible component of anexpression: 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 aValue().

You will rarely need to use Value() directly. When you write the expressionF('field') + 1, Django implicitly wraps the 1 in a Value(),allowing simple values to be used in more complex expressions. You will need touse Value() when you want to pass a string to an expression. Mostexpressions interpret a string argument as the name of a field, likeLower('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 Pythonvalues into their corresponding database type.

The output_field argument should be a model field instance, likeIntegerField() or BooleanField(), into which Django will load the valueafter it’s retrieved from the database. Usually no arguments are needed wheninstantiating the model field as any arguments relating to data validation(max_length, max_digits, etc.) will not be enforced on the expression’soutput value.

ExpressionWrapper() expressions

  • class ExpressionWrapper(expression, output_field)
  • ExpressionWrapper surrounds another expression and provides access toproperties, such as output_field, that may not be available on otherexpressions. ExpressionWrapper is necessary when using arithmetic onF() expressions with different types as described inUsing F() with annotations.

Conditional expressions

Conditional expressions allow you to use if … elif …else logic in queries. Django natively supports SQL CASEexpressions. 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 Subqueryexpression.

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

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

On PostgreSQL, the SQL looks like:

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

Note

The examples in this section are designed to show how to forceDjango to execute a subquery. In some cases it may be possible towrite an equivalent queryset that performs the same task moreclearly or efficiently.

Referencing columns from the outer queryset

  • class OuterRef(field)
  • Use OuterRef when a queryset in a Subquery needs to refer to a fieldfrom the outer query. It acts like an F expression except that thecheck to see if it refers to a valid field isn’t made until the outer querysetis resolved.

Instances of OuterRef may be used in conjunction with nested instancesof Subquery to refer to a containing queryset that isn’t the immediateparent. For example, this queryset would need to be within a nested pair ofSubquery instances to resolve correctly:

  1. >>> Book.objects.filter(author=OuterRef(OuterRef('pk')))

Limiting a subquery to a single column

There are times when a single column must be returned from a Subquery, forinstance, to use a Subquery as the target of an __in lookup. To returnall comments for posts published within the last day:

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

In this case, the subquery must use values()to return only a single column: the primary key of the post.

Limiting the subquery to a single row

To prevent a subquery from returning multiple rows, a slice ([:1]) of thequeryset is used:

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

In this case, the subquery must only return a single column and a singlerow: the email address of the most recently created comment.

(Using get() instead of a slice would fail because theOuterRef cannot be resolved until the queryset is used within aSubquery.)

Exists() subqueries

  • class Exists(queryset)
  • Exists is a Subquery subclass that uses an SQL EXISTS statement. Inmany cases it will perform better than a subquery since the database is able tostop evaluation of the subquery when a first matching row is found.

For example, to annotate each post with whether or not it has a comment fromwithin the last day:

  1. >>> from django.db.models import Exists, OuterRef
  2. >>> from datetime import timedelta
  3. >>> from django.utils import timezone
  4. >>> one_day_ago = timezone.now() - timedelta(days=1)
  5. >>> recent_comments = Comment.objects.filter(
  6. ...     post=OuterRef('pk'),
  7. ...     created_at__gte=one_day_ago,
  8. ... )
  9. >>> Post.objects.annotate(recent_comment=Exists(recent_comments))

On PostgreSQL, the SQL looks like:

  1. SELECT "post"."id", "post"."published_at", EXISTS(
  2.     SELECT U0."id", U0."post_id", U0."email", U0."created_at"
  3.     FROM "comment" U0
  4.     WHERE (
  5.         U0."created_at" >= YYYY-MM-DD HH:MM:SS AND
  6.         U0."post_id" = ("post"."id")
  7.     )
  8. ) AS "recent_comment" FROM "post"

It’s unnecessary to force Exists to refer to a single column, since thecolumns are discarded and a boolean result is returned. Similarly, sinceordering is unimportant within an SQL EXISTS subquery and would onlydegrade performance, it’s automatically removed.

You can query using NOT EXISTS with ~Exists().

Filtering on a Subquery() or Exists() expressions

Subquery() that returns a boolean value and Exists() may be used as acondition in When expressions, or todirectly filter a queryset:

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

This will ensure that the subquery will not be added to the SELECT columns,which may result in a better performance.

Changed in Django 3.0:In previous versions of Django, it was necessary to first annotate and thenfilter against the annotation. This resulted in the annotated value alwaysbeing present in the query result, and often resulted in a query that tookmore time to execute.

Using aggregates within a Subquery expression

Aggregates may be used within a Subquery, but they require a specificcombination of filter(), values(), andannotate() to get the subquery grouping correct.

Assuming both models have a length field, to find posts where the postlength is greater than the total length of all combined comments:

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

The initial filter(…) limits the subquery to the relevant parameters.order_by() removes the default ordering(if any) on the Comment model. values('post') aggregates comments byPost. Finally, annotate(…) performs the aggregation. The order inwhich these queryset methods are applied is important. In this case, since thesubquery must be limited to a single column, values('total') is required.

This is the only way to perform an aggregation within a Subquery, asusing aggregate() attempts to evaluate the queryset (and ifthere is an OuterRef, this will not be possible to resolve).

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:
  1. >>> from django.db.models.expressions import RawSQL
  2. >>> queryset.annotate(val=RawSQL("select col from sometable where othercol = %s", (someparam,)))

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

Warning

To protect against SQL injection attacks, you must escape anyparameters that the user can control by using params. params is arequired argument to force you to acknowledge that you’re not interpolatingyour SQL with user-provided data.

You also must not quote placeholders in the SQL string. This example isvulnerable to SQL injection because of the quotes around %s:

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

You can read more about how Django’s SQL injection protection works.

Window functions

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

You can specify multiple windows in the same query which in Django ORM would beequivalent 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)
    • filterable

    • Defaults to False. The SQL standard disallows referencing windowfunctions in the WHERE clause and Django raises an exception whenconstructing a QuerySet that would do that.

    • template

    • Defaults to %(expression)s OVER (%(window)s)'. If only theexpression 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, oran expression that’s compatible in a window clause.

The partition_by argument is a list of expressions (column names should bewrapped 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 a sequence of expressions on which you cancall asc() anddesc(). The ordering controls the order inwhich the expression is applied. For example, if you sum over the rows in apartition, the first result is the value of the first row, the second is thesum of first and second row.

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

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

  1. >>> from django.db.models import Avg, F, Window
  2. >>> from django.db.models.functions import ExtractYear
  3. >>> Movie.objects.annotate(
  4. >>>     avg_rating=Window(
  5. >>>         expression=Avg('rating'),
  6. >>>         partition_by=[F('studio'), F('genre')],
  7. >>>         order_by=ExtractYear('released').asc(),
  8. >>>     ),
  9. >>> )

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., thesame partition and frame. For example, you could modify the previous exampleto 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. Thepartition and ordering from the previous example is extracted into a dictionaryto reduce repetition:

  1. >>> from django.db.models import Avg, F, Max, Min, Window
  2. >>> from django.db.models.functions import ExtractYear
  3. >>> window = {
  4. >>>    'partition_by': [F('studio'), F('genre')],
  5. >>>    'order_by': ExtractYear('released').asc(),
  6. >>> }
  7. >>> Movie.objects.annotate(
  8. >>>     avg_rating=Window(
  9. >>>         expression=Avg('rating'), **window,
  10. >>>     ),
  11. >>>     best=Window(
  12. >>>         expression=Max('rating'), **window,
  13. >>>     ),
  14. >>>     worst=Window(
  15. >>>         expression=Min('rating'), **window,
  16. >>>     ),
  17. >>> )

Among Django’s built-in database backends, MySQL 8.0.2+, PostgreSQL, and Oraclesupport window expressions. Support for different window expression featuresvaries among the different databases. For example, the options inasc() anddesc() may not be supported. Consult thedocumentation for your database as needed.

Frames

For a window frame, you can choose either a range-based sequence of rows or anordinary sequence of rows.

  • class ValueRange(start=None, end=None)
    • frame_type
    • This attribute is set to 'RANGE'.

PostgreSQL has limited support for ValueRange and only supports use ofthe standard start and end points, such as CURRENT ROW and UNBOUNDEDFOLLOWING.

  • class RowRange(start=None, end=None)
    • frame_type
    • This attribute is set to 'ROWS'.

Both classes return SQL with the template:

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

Frames narrow the rows that are used for computing the result. They shift fromsome start point to some specified end point. Frames can be used with andwithout partitions, but it’s often a good idea to specify an ordering of thewindow to ensure a deterministic result. In a frame, a peer in a frame is a rowwith an equivalent value, or all rows if an ordering clause isn’t present.

The default starting point for a frame is UNBOUNDED PRECEDING which is thefirst row of the partition. The end point is always explicitly included in theSQL generated by the ORM and is by default UNBOUNDED FOLLOWING. The defaultframe includes all rows from the partition to the last row in the set.

The accepted values for the start and end arguments are None, aninteger, or zero. A negative integer for start results in N preceding,while None yields UNBOUNDED PRECEDING. For both start and end,zero will return CURRENT ROW. Positive integers are accepted for end.

There’s a difference in what CURRENT ROW includes. When specified inROWS mode, the frame starts or ends with the current row. When specified inRANGE mode, the frame starts or ends at the first or last peer according tothe ordering clause. Thus, RANGE CURRENT ROW evaluates the expression forrows which have the same value specified by the ordering. Because the templateincludes both the start and end points, this may be expressed with:

  1. ValueRange(start=0, end=0)

If a movie’s “peers” are described as movies released by the same studio in thesame genre in the same year, this RowRange example annotates each moviewith the average rating of a movie’s two prior and two following peers:

  1. >>> from django.db.models import Avg, F, RowRange, Window
  2. >>> from django.db.models.functions import ExtractYear
  3. >>> Movie.objects.annotate(
  4. >>>     avg_rating=Window(
  5. >>>         expression=Avg('rating'),
  6. >>>         partition_by=[F('studio'), F('genre')],
  7. >>>         order_by=ExtractYear('released').asc(),
  8. >>>         frame=RowRange(start=-2, end=2),
  9. >>>     ),
  10. >>> )

If the database supports it, you can specify the start and end points based onvalues of an expression in the partition. If the released field of theMovie model stores the release month of each movies, this ValueRangeexample annotates each movie with the average rating of a movie’s peersreleased between twelve months before and twelve months after the each movie.

  1. >>> from django.db.models import Avg, ExpressionList, F, ValueRange, Window
  2. >>> Movie.objects.annotate(
  3. >>>     avg_rating=Window(
  4. >>>         expression=Avg('rating'),
  5. >>>         partition_by=[F('studio'), F('genre')],
  6. >>>         order_by=F('released').asc(),
  7. >>>         frame=ValueRange(start=-12, end=12),
  8. >>>     ),
  9. >>> )

Technical Information

Below you’ll find technical implementation details that may be useful tolibrary authors. The technical API and examples below will help withcreating generic query expressions that can extend the built-in functionalitythat Django provides.

Expression API

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

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

  • class Expression
    • contains_aggregate

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

    • contains_over_clause

    • Tells Django that this expression contains aWindow expression. It’s used,for example, to disallow window function expressions in queries thatmodify data.

    • filterable

    • Tells Django that this expression can be referenced inQuerySet.filter(). Defaults to True.

    • window_compatible

    • Tells Django that this expression can be used as the source expressionin Window. Defaults toFalse.

    • resolveexpression(_query=None, allow_joins=True, reuse=None, summarize=False, for_save=False)

    • Provides the chance to do any pre-processing or validation ofthe expression before it’s added to the query. resolve_expression()must also be called on any nested expressions. A copy() of selfshould be returned with any necessary transformations.

query is the backend query implementation.

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

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

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

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

  • get_source_expressions()
  • Returns an ordered list of inner expressions. For example:
  1. >>> Sum(F('foo')).get_source_expressions()
  2. [F('foo')]
  • setsource_expressions(_expressions)

  • Takes a list of expressions and stores them such thatget_source_expressions() can return them.

  • relabeledclone(_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 expressionsand assigned to the clone.

change_map is a dictionary mapping old aliases to new aliases.

Example:

  1. def relabeled_clone(self, change_map):
  2.     clone = copy.copy(self)
  3.     clone.expression = self.expression.relabeled_clone(change_map)
  4.     return clone
  • convertvalue(_value, expression, connection)

  • A hook allowing the expression to coerce value into a moreappropriate type.

  • getgroup_by_cols(_alias=None)

  • Responsible for returning the list of columns references bythis expression. get_group_by_cols() should be called on anynested expressions. F() objects, in particular, hold a referenceto a column. The alias parameter will be None unless theexpression has been annotated and is used for grouping.

Changed in Django 3.0:The alias parameter was added.

  • asc(nulls_first=False, nulls_last=False)
  • 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.

  • desc(nulls_first=False, nulls_last=False)
  • 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.

  • reverse_ordering()
  • Returns self with any modifications required to reverse the sortorder within an order_by call. As an example, an expressionimplementing NULLS LAST would change its value to beNULLS FIRST. Modifications are only required for expressions thatimplement sort order like OrderBy. This method is called whenreverse() is called on aqueryset.

Writing your own Query Expressions

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

The COALESCE SQL function is defined as taking a list of columns orvalues. 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 andan init() method to set some attributes:

  1. import copy
  2. from django.db.models import Expression
  3.  
  4. class Coalesce(Expression):
  5.     template = 'COALESCE( %(expressions)s )'
  6.  
  7.     def __init__(self, expressions, output_field):
  8.       super().__init__(output_field=output_field)
  9.       if len(expressions) < 2:
  10.           raise ValueError('expressions must have at least 2 elements')
  11.       for expression in expressions:
  12.           if not hasattr(expression, 'resolve_expression'):
  13.               raise TypeError('%r is not an Expression' % expression)
  14.       self.expressions = expressions

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

Now we implement the pre-processing and validation. Since we do not haveany of our own validation at this point, we delegate to the nestedexpressions:

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

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

  1. def as_sql(self, compiler, connection, template=None):
  2.     sql_expressions, sql_params = [], []
  3.     for expression in self.expressions:
  4.         sql, params = compiler.compile(expression)
  5.         sql_expressions.append(sql)
  6.         sql_params.extend(params)
  7.     template = template or self.template
  8.     data = {'expressions': ','.join(sql_expressions)}
  9.     return template % data, params
  10.  
  11. def as_oracle(self, compiler, connection):
  12.     """
  13.     Example of vendor specific handling (Oracle in this case).
  14.     Let's make the function name lowercase.
  15.     """
  16.     return self.as_sql(compiler, connection, template='coalesce( %(expressions)s )')

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

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

We’ve also defined a custom implementation that is specific to the Oraclebackend. 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 expressionto play nice with other query expressions:

  1. def get_source_expressions(self):
  2.     return self.expressions
  3.  
  4. def set_source_expressions(self, expressions):
  5.     self.expressions = expressions

Let’s see how it works:

  1. >>> from django.db.models import F, Value, CharField
  2. >>> qs = Company.objects.annotate(
  3. ...    tagline=Coalesce([
  4. ...        F('motto'),
  5. ...        F('ticker_name'),
  6. ...        F('description'),
  7. ...        Value('No Tagline')
  8. ...        ], output_field=CharField()))
  9. >>> for c in qs:
  10. ...     print("%s: %s" % (c.name, c.tagline))
  11. ...
  12. Google: Do No Evil
  13. Apple: AAPL
  14. Yahoo: Internet Company
  15. Django Software Foundation: No Tagline

Avoiding SQL injection

Since a Func’s keyword arguments for init() (extra) andas_sql() (extra_context) are interpolated into the SQL string ratherthan passed as query parameters (where the database driver would escape them),they must not contain untrusted user input.

For example, if substring is user-provided, this function is vulnerable toSQL injection:

  1. from django.db.models import Func
  2.  
  3. class Position(Func):
  4.     function = 'POSITION'
  5.     template = "%(function)s('%(substring)s' in %(expressions)s)"
  6.  
  7.     def __init__(self, expression, substring):
  8.         # substring=substring is a SQL injection vulnerability!
  9.         super().__init__(expression, substring=substring)

This function generates a SQL string without any parameters. Since substringis passed to super().init() as a keyword argument, it’s interpolatedinto the SQL string before the query is sent to the database.

Here’s a corrected rewrite:

  1. class Position(Func):
  2.     function = 'POSITION'
  3.     arg_joiner = ' IN '
  4.  
  5.     def __init__(self, expression, substring):
  6.         super().__init__(substring, expression)

With substring instead passed as a positional argument, it’ll be passed asa parameter in the database query.

Adding support in third-party database backends

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

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

  1. from django.db.models.functions import Length
  2.  
  3. def sqlserver_length(self, compiler, connection):
  4.     return self.as_sql(compiler, connection, function='LEN')
  5.  
  6. 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 returnssqlserver for the backend.

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

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