查询表达式可以作为过滤，分组，注解或者是聚合的一个值或者是计算。这里（文档中）有很多内置表达式可以帮助你完成自己的查询。表达式可以组合，甚至是嵌套，来完成更加复杂的计算
Django支持在查询表达式使用加减乘除，求模，幂运算，Python常量，变量甚至是其它表达式。
增加了对指数运算符**的支持。
一些例子依赖于Django1.8中的新功能
from django.db.models import F, Count
from django.db.models.functions import Length
# 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_employees
120
>>> company.num_chairs
50
>>> company.chairs_needed
70
# 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 also
Company.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())
说明
这些表达式定义在django.db.models.expressions 和 django.db.models.aggregates中, 但为了方便，通常可以直接从django.db.models导入.
一个 F()对象代表了一个model的字段值或注释列。使用它就可以直接参考model的field和执行数据库操作而不用再把它们（model field）查询出来放到python内存中。
作为代替，Django使用 F()对象生成一个SQL表达式，来描述数据库层级所需要的操作
这些通过一个例子可以很容易的理解。往常，我们会这样做：
# Tintin filed a news story!
reporter = Reporters.objects.get(name='Tintin')
reporter.stories_filed += 1
reporter.save()
这里呢，我们把 reporter.stories_filed 的值从数据库取出放到内存中并用我们熟悉的python运算符操作它，最后再把它保存到数据库。然而，我们还可以这样做：
from django.db.models import F
reporter = Reporters.objects.get(name='Tintin')
reporter.stories_filed = F('stories_filed') + 1
reporter.save()
虽然reporter.stories_filed = F('stories_filed') + 1看起来像一个正常的Python分配值赋给一个实例属性，事实上这是一个描述数据库操作的SQL概念
当Django遇到 F()实例，它覆盖了标准的Python运算符创建一个封装的SQL表达式。在这个例子中，reporter.stories_filed就代表了一个指示数据库对该字段进行增量的命令。
无论reporter.stories_filed的值是或曾是什么，Python一无所知--这完全是由数据库去处理的。所有的Python，通过Django的F() 类，只是去创建SQL语法参考字段和描述操作。
注
为了获得用这种方法保存的新值，此对象应重新加载：
reporter = Reporters.objects.get(pk=reporter.pk)
.和上面单独实例的操作一样， F()配合 update()可以应用于对象实例的 QuerySets这减少了我们上面使用的两个查询 - get()和save() - 只有一个：
reporter = Reporters.objects.filter(name='Tintin')
reporter.update(stories_filed=F('stories_filed') + 1)
我们可以使用update()方法批量地增加多个对象的字段值。这比先从数据库查询后，通过循环一个个增加，并一个个保存要快的很多。
Reporter.objects.all().update(stories_filed=F('stories_filed') + 1)
F()表达式的效率上的优点主要体现在
使用 F() 的另一个好处是通过数据库而不是Python来更新字段值以避免竞态条件.
如果两个Python线程执行上面第一个例子中的代码，一个线程可能在另一个线程刚从数据库中获取完字段值后获取、增加、保存该字段值。第二个线程保存的值将会基于原始字段值；第一个线程的工作将会丢失。
如果让数据库对更新字段负责，这个过程将变得更稳健：它将只会在 save() 或 update()执行时根据数据库中该字段值来更新字段，而不是基于实例之前获取的字段值。
F()可用于通过将不同字段与算术相结合来在模型上创建动态字段：
company = Company.objects.annotate(
chairs_needed=F('num_employees') - F('num_chairs'))
如果你组合的字段是不同类型，你需要告诉Django将返回什么类型的字段。由于F()不直接支持output_field，您需要使用ExpressionWrapper
from django.db.models import DateTimeField, ExpressionWrapper, F
Ticket.objects.annotate(
expires=ExpressionWrapper(
F('active_at') + F('duration'), output_field=DateTimeField()))
Func() 表达式是所有表达式的基础类型，包括数据库函数如 COALESCE 和 LOWER, 或者 SUM聚合.用下面方式可以直接使用:
from django.db.models import Func, F
queryset.annotate(field_lower=Func(F('field'), function='LOWER'))
或者它们可以用于构建数据库函数库：
class Lower(Func):
function = 'LOWER'
queryset.annotate(field_lower=Lower(F('field')))
但是这两种情况都将导致查询集，其中每个模型用从以下SQL大致生成的额外属性field_lower注释：
SELECT
...
LOWER("app_label"."field") as "field_lower"
有关内置数据库函数的列表，请参见数据库函数。
Func API如下：
类属性，作为格式字符串，描述为此函数生成的SQL。默认为'％（function）s（％（expressions）s）'。
类属性，表示用于连接表达式列表的字符。默认为'， '。
*expressions参数是函数将要应用与表达式的位置参数列表。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 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:
A class attribute, as a format string, that describes the SQL that is generated for this aggregate. Defaults to '%(function)s( %(expressions)s )'.
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.
output_field is a new parameter.
The **extra kwargs are key=value pairs that can be interpolated into the template attribute.
Aggregate functions can now use arithmetic and reference multiple model fields in a single function.
Creating your own aggregate is extremely easy. 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 Count(Aggregate):
# supports COUNT(distinct field)
function = 'COUNT'
template = '%(function)s(%(distinct)s%(expressions)s)'
def __init__(self, expression, distinct=False, **extra):
super(Count, self).__init__(
expression,
distinct='DISTINCT ' if distinct else '',
output_field=IntegerField(),
**extra)
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.
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.
Conditional expressions allow you to use if ... elif ... else logic in queries. Django natively supports SQL CASE expressions. For more details see Conditional Expressions.
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.
Tells Django that this expression contains an aggregate and that a GROUP BY clause needs to be added to the query.
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 an ordered list of inner expressions. For example:
>>> Sum(F('foo')).get_source_expressions()
[F('foo')]
Takes a list of expressions and stores them such that get_source_expressions() can return them.
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
A hook allowing the expression to coerce value into a more appropriate type.
Returns a tuple containing the (aggregate, lookup_path) of the first aggregate that this expression (or any nested expression) references, or (False, ()) if no aggregate is referenced. For example:
queryset.filter(num_chairs__gt=F('sum__employees'))
The F() expression here references a previous Sum() computation which means that this filter expression should be added to the HAVING clause rather than the WHERE clause.
In the majority of cases, returning the result of refs_aggregate on any nested expression should be appropriate, as the necessary built-in expressions will return the correct values.
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.
Returns the expression ready to be sorted in ascending order.
Returns the expression ready to be sorted in descending order.
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.
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, **extra):
super(Coalesce, self).__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
self.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:
def resolve_expression(self, query=None, allow_joins=True, reuse=None, summarize=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)
return c
Next, we write the method responsible for generating the SQL:
def as_sql(self, compiler, connection):
sql_expressions, sql_params = [], []
for expression in self.expressions:
sql, params = compiler.compile(expression)
sql_expressions.append(sql)
sql_params.extend(params)
self.extra['expressions'] = ','.join(sql_expressions)
return self.template % self.extra, sql_params
def as_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 )'
return self.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:
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
May 13, 2015