Query expressions describe a value or a computation that can be used as part of
an update, create, filter, order by, annotation, or aggregate. There are a
number of built-in expressions (documented below) that can be used to help you
write queries. Expressions can be combined, or in some cases nested, to form
more complex computations.

Изменено в Django 1.9:

Support for using expressions when creating new model instances was added.

fromdjango.db.modelsimportF,Countfromdjango.db.models.functionsimportLength,Upper,Value# Find companies that have more employees than chairs.Company.objects.filter(num_employees__gt=F('num_chairs'))# Find companies that have at least twice as many employees# as chairs. Both the querysets below are equivalent.Company.objects.filter(num_employees__gt=F('num_chairs')*2)Company.objects.filter(num_employees__gt=F('num_chairs')+F('num_chairs'))# How many chairs are needed for each company to seat all employees?>>>company=Company.objects.filter(...num_employees__gt=F('num_chairs')).annotate(...chairs_needed=F('num_employees')-F('num_chairs')).first()>>>company.num_employees120>>>company.num_chairs50>>>company.chairs_needed70# Create a new company using expressions.>>>company=Company.objects.create(name='Google',ticker=Upper(Value('goog')))# Be sure to refresh it if you need to access the field.>>>company.refresh_from_db()>>>company.ticker'GOOG'# Annotate models with an aggregated value. Both forms# below are equivalent.Company.objects.annotate(num_products=Count('products'))Company.objects.annotate(num_products=Count(F('products')))# Aggregates can contain complex computations alsoCompany.objects.annotate(num_offerings=Count(F('products')+F('services')))# Expressions can also be used in order_by()Company.objects.order_by(Length('name').asc())Company.objects.order_by(Length('name').desc())

An F() object represents the 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 a SQL expression that
describes the required operation at the database level.

This is easiest to understand through an example. Normally, one might do
something like this:

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:

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.

Примечание

In order to access the new value that has been saved in this way, the object
will need to be reloaded:

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:

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:

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

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

If the database is responsible for updating the field, the process is more
robust: it will only ever update the field based on the value of the field in
the database when the save() or update() is executed, rather
than based on its value when the instance was retrieved.

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

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

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. Note that the keywords function and
template can be used to replace the function and template
attributes respectively, without having to define your own class.
output_field can be used to define the expected return type.

An aggregate expression is a special case of a Func() expression that informs the query that a GROUPBY 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:

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.

The expression argument can be the name of a field on the model, or another
expression. It will be converted to a string and used as the expressions
placeholder within the template.

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

Note that output_field is only required when Django is unable to determine
what field type the result should be. Complex expressions that mix field types
should define the desired output_field. For example, adding an
IntegerField() and a FloatField() together should probably have
output_field=FloatField() defined.

Изменено в Django 1.8:

output_field is a new parameter.

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

Добавлено в Django 1.8:

Aggregate functions can now use arithmetic and reference multiple
model fields in a single function.

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.

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

ExpressionWrapper simply 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.

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

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.

Provides the chance to do any pre-processing 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.

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.

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

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:

importcopyfromdjango.db.modelsimportExpressionclassCoalesce(Expression):template='COALESCE( %(expressions)s )'def__init__(self,expressions,output_field,**extra):super(Coalesce,self).__init__(output_field=output_field)iflen(expressions)<2:raiseValueError('expressions must have at least 2 elements')forexpressioninexpressions:ifnothasattr(expression,'resolve_expression'):raiseTypeError('%r is not an Expression'%expression)self.expressions=expressionsself.extra=extra

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 pre-processing and validation. Since we do not have
any of our own validation at this point, we just delegate to the nested
expressions:

defas_sql(self,compiler,connection):sql_expressions,sql_params=[],[]forexpressioninself.expressions:sql,params=compiler.compile(expression)sql_expressions.append(sql)sql_params.extend(params)self.extra['expressions']=','.join(sql_expressions)returnself.template%self.extra,sql_paramsdefas_oracle(self,compiler,connection):""" Example of vendor specific handling (Oracle in this case). Let's make the function name lowercase. """self.template='coalesce( %(expressions)s )'returnself.as_sql(compiler,connection)

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: