# 查询表达式¶

## 支持的算术¶

Django 支持负、加、减、乘、除、模数运算，以及对查询表达式的幂运算符，使用 Python 常量、变量，甚至其他表达式。

## 一些例子¶

```from django.db.models import Count, F, Value
from django.db.models.functions import Length, Upper
from django.db.models.lookups import GreaterThan

# 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

# Create a new company using expressions.
# 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 also
Company.objects.annotate(num_offerings=Count(F('products') + F('services')))

# Expressions can also be used in order_by(), either directly
Company.objects.order_by(Length('name').asc())
Company.objects.order_by(Length('name').desc())
# or using the double underscore lookup syntax.
from django.db.models import CharField
from django.db.models.functions import Length
CharField.register_lookup(Length)
Company.objects.order_by('name__length')

# Boolean expression can be used directly in filters.
from django.db.models import Exists
Company.objects.filter(
Exists(Employee.objects.filter(company=OuterRef('pk'), salary__gt=10))
)

# Lookup expressions can also be used directly in filters
Company.objects.filter(GreaterThan(F('num_employees'), F('num_chairs')))
# or annotations.
Company.objects.annotate(
need_chairs=GreaterThan(F('num_employees'), F('num_chairs')),
)
```

## 内置表达式¶

### `F()` 表达式¶

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.

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

```from django.db.models import F

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

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

`F()` 除了用于上述对单个实例的操作外，`F()` 还可以与 `update()` 一起用于对象实例的 `QuerySets`。这就把我们上面使用的两个查询——`get()``save()` 减少到只有一个：

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

```Reporter.objects.all().update(stories_filed=F('stories_filed') + 1)
```

• 让数据库，而不是 Python 来完成工作
• 减少某些操作所需的查询次数
Changed in Django 3.2:

#### 使用 `F()` 避免竞争条件¶

`F()` 的另一个有用的好处是，让数据库——而不是 Python——更新一个字段的值，避免了 竞争条件

#### `F()` 赋值在 `Model.save()` 之后持续存在¶

`F()` 分配给模型字段的对象在保存模型实例后会持续存在，并将应用于每个 `save()`。例如：

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

reporter.name = 'Tintin Jr.'
reporter.save()
```

#### 在过滤器中使用 `F()`¶

`F()``QuerySet` 过滤器中也非常有用，它们可以根据对象的字段值而不是 Python 值的标准来过滤一组对象。

#### 与注解一起使用 `F()`¶

`F()` 可用于通过将不同的字段与算术相结合，在你的模型上创建动态字段：

```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
```

#### 使用 `F()` 对空值进行排序¶

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

### `Func()` 表达式¶

`Func()` 表达式是所有涉及 `COALESCE``LOWER` 等数据库函数或 `SUM` 等集合的表达式的基本类型。它们可以直接使用：

```from django.db.models import F, Func

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

```class Lower(Func):
function = 'LOWER'

queryset.annotate(field_lower=Lower('field'))
```

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

`Func` API 如下：

class `Func`(*expressions, **extra)
`function`

`template`

`arg_joiner`

`arity`

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

`as_vendor()` 方法应该使用 `function``template``arg_joiner` 以及其他 `**extra_context` 参数来根据需要定制 SQL。例如：

`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
)
```

`*expressions` 参数是一个位置表达式的列表，该函数将应用于此。表达式将被转换为字符串，用 `arg_joiner` 连接在一起，然后插入到 `template` 中作为 `expressions` 占位符。

`**extra` 关键字是 `key=value` 对，可以内插到 `template` 属性中。为了避免 SQL 注入漏洞，`extra` 不能包含不受信任的用户输入，因为这些值会被内插到 SQL 字符串中，而不是作为查询参数传递，数据库驱动程序对其进行转义。

`function``template``arg_joiner` 关键字可以用来替换同名的属性，而不必定义自己的类。`output_field` 可以用来定义预期的返回类型。

### `Aggregate()` 表达式¶

```from django.db.models import Count

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

`Aggregate` 的 API 如下：

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

`function`

`window_compatible`

`allow_distinct`

`empty_result_set_value`
New in Django 4.0.

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`.

`output_field` 参数需要一个模型字段实例，比如 `IntegerField()``BooleanField()`，Django 会在从数据库中获取值后将其载入其中。通常在实例化模型字段时，不需要任何参数，因为任何与数据验证有关的参数（`max_length``max_digits` 等）都不会在表达式的输出值上执行。

`distinct` 参数决定是否应该为 `expressions` 的每一个不同的值（或一组值，对于多个 `expressions`）调用聚合函数。该参数仅在 `allow_distinct` 设置为 `True` 的聚合函数中得到支持。

`filter` 参数取一个 `Q 对象`，用于过滤被聚合的行。参见 条件聚合过滤注解 的用法示例。

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.

`**extra` 关键字是 `key=value` 对，可以内插到 `template` 属性中。

Changed in Django 3.2:

Changed in Django 4.0:

The `default` argument was added.

### 创建你自己的聚合函数¶

```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()` 表达式¶

class `Value`(value, output_field=None)

`Value()` 对象表示一个表达式中最小的成分：一个简单的值。当你需要在一个表达式中表示一个整数、布尔值或字符串的值时，你可以将该值包裹在一个 `Value()` 中。

`value` 参数描述了要包含在表达式中的值，比如 `1``True``None`。Django 知道如何将这些 Python 值转换为相应的数据库类型。

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. If no `output_field` is specified it will be tentatively inferred from the `type` of the provided `value`, if possible. For example, passing an instance of `datetime.datetime` as `value` would default `output_field` to `DateTimeField`.

Changed in Django 3.2:

Support for inferring a default `output_field` from the type of `value` was added.

### `ExpressionWrapper()` 表达式¶

class `ExpressionWrapper`(expression, output_field)

`ExpressionWrapper` 包裹另一个表达式，并提供对 `output_field` 等属性的访问，这些属性在其他表达式上可能无法使用。`ExpressionWrapper` 在对 `F()` 具有不同类型的表达式使用算术时是必要的，如 与注解一起使用 F() 中所述。

### `Subquery()` 表达式¶

class `Subquery`(queryset, output_field=None)

```>>> from django.db.models import OuterRef, Subquery
```

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

#### 从外部查询集中引用列¶

class `OuterRef`(field)

Use `OuterRef` when a queryset in a `Subquery` needs to refer to a field from the outer query or its transform. It acts like an `F` expression except that the check to see if it refers to a valid field isn't made until the outer queryset is resolved.

`OuterRef` 的实例可以与 `Subquery` 的嵌套实例一起使用，以引用一个不是直接父级的包含查询集的实例。例如，这个查询集需要在一对嵌套的 `Subquery` 实例中才能正确解析：

```>>> Book.objects.filter(author=OuterRef(OuterRef('pk')))
```
Changed in Django 3.2:

#### 将子查询限制为单列¶

```>>> 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])
```

（使用 `get()` 而不是分片会导致失败，因为 `OuterRef``Subquery` 中使用查询集之前无法解析。)

#### `Exists()` 子查询¶

class `Exists`(queryset)

`Exists` 是一个 `Subquery` 子类，它使用 SQL `EXISTS` 语句。在许多情况下，它的性能比子查询更好，因为当找到第一条匹配的记录时，数据库能够停止对子查询的执行。

```>>> from django.db.models import Exists, OuterRef
>>> from datetime import timedelta
>>> from django.utils import timezone
>>> one_day_ago = timezone.now() - timedelta(days=1)
...     post=OuterRef('pk'),
...     created_at__gte=one_day_ago,
... )
```

```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"
```

#### 对 `Subquery()` 或 `Exists()` 表达式进行过滤。¶

`When` 表达式中，返回布尔值的 `Subquery()``Exists()` 可以作为 `condition`，或者直接过滤一个查询集：

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

#### 在 `Subquery` 表达式中使用集合。¶

```>>> from django.db.models import OuterRef, Subquery, Sum
```

### 原始 SQL 表达式¶

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

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

`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!
```

### 窗口函数¶

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

`template`

`Window` 类是 `OVER` 子句的主要表达式。

`expression` 参数是一个 窗口函数，一个 聚合函数，或者一个与窗口子句兼容的表达式。

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.

`output_field` 是作为参数或通过表达式指定的。

The `order_by` argument accepts an expression or a sequence of expressions on which you can call `asc()` and `desc()`. 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.

`frame` 参数指定在计算中应该使用哪些其他行。详见

```>>> from django.db.models import Avg, F, Window
>>> from django.db.models.functions import ExtractYear
>>> Movie.objects.annotate(
>>>     avg_rating=Window(
>>>         expression=Avg('rating'),
>>>         partition_by=[F('studio'), F('genre')],
>>>         order_by=ExtractYear('released').asc(),
>>>     ),
>>> )
```

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

#### 帧¶

class `ValueRange`(start=None, end=None)
`frame_type`

PostgreSQL 对 `ValueRange` 的支持有限，只支持使用标准的开始和结束点，如 `CURRENT ROW``UNBOUNDED FOLLOWING`

class `RowRange`(start=None, end=None)
`frame_type`

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

`start``end` 参数的可接受值是 `None`、一个整数或零。`start` 的负整数会导致 `N preceding`，而 `None` 会产生 `UNBOUNDED PRECEDING`。对于 `start``end`，0 将返回 `CURRENT ROW``end` 接受正整数。

`CURRENT ROW` 包括的内容有区别。当在 `ROWS` 模式下指定时，帧以当前行开始或结束。当在 `RANGE` 模式下指定时，根据排序子句，帧以第一个或最后一个对等值开始或结束。因此，`RANGE CURRENT ROW` 对具有由排序指定的相同值的行执行表达式。因为模板包括 `start``end` 点，所以可以用：

```ValueRange(start=0, end=0)
```

```>>> from django.db.models import Avg, F, RowRange, Window
>>> from django.db.models.functions import ExtractYear
>>> Movie.objects.annotate(
>>>     avg_rating=Window(
>>>         expression=Avg('rating'),
>>>         partition_by=[F('studio'), F('genre')],
>>>         order_by=ExtractYear('released').asc(),
>>>         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=F('released').asc(),
>>>         frame=ValueRange(start=-12, end=12),
>>>     ),
>>> )
```

## 技术信息¶

### 表达式 API¶

class `Expression`
`contains_aggregate`

`contains_over_clause`

`filterable`

`window_compatible`

`empty_result_set_value`
New in Django 4.0.

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)

`query` 是后端查询的实现。

`allow_joins` 是一个允许或拒绝在查询中使用连接的布尔值。

`reuse` 是一组可重用的多连接方案的连接。

`summarize` 是一个布尔值，当 `True` 时，表示正在计算的查询是一个终端聚合查询。

`for_save` 是一个布尔值，当 `True` 时，表示正在执行的查询正在进行创建或更新。

`get_source_expressions`()

```>>> Sum(F('foo')).get_source_expressions()
[F('foo')]
```
`set_source_expressions`(expressions)

`relabeled_clone`(change_map)

`change_map` 是一个将旧别名映射到新别名的字典。

```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)

`expression``self` 相同。

`get_group_by_cols`(alias=None)

`asc`(nulls_first=False, nulls_last=False)

`nulls_first``nulls_last` 定义了如何对空值进行排序。参见 使用 F() 对空值进行排序 的用法示例。

`desc`(nulls_first=False, nulls_last=False)

`nulls_first``nulls_last` 定义了如何对空值进行排序。参见 使用 F() 对空值进行排序 的用法示例。

`reverse_ordering`()

### 编写自己的查询表达式¶

`COALESCE` SQL 函数被定义为接收一个列或值的列表，它将返回第一个不是 `NULL` 的列或值。它将返回第一个不是 `NULL` 的列或值。

```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
```

```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
```

```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()` 方法可以支持自定义关键字参数，允许 `as_vendorname()` 方法覆盖用于生成 SQL 字符串的数据。使用 `as_sql()` 关键字参数进行自定义最好是在 `as_vendorname()` 方法中突变 `self`，因为后者在不同的数据库后端运行时可能会导致错误。如果你的类依赖于类属性来定义数据，可以考虑在你的 `as_sql()` 方法中允许覆盖。

```def get_source_expressions(self):
return self.expressions

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

```>>> 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))
...
Apple: AAPL
Yahoo: Internet Company
Django Software Foundation: No Tagline
```

#### 避免 SQL 注入¶

```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)
```

`substring` 代替作为位置参数传递，它将作为数据库查询的参数传递。

### 在第三方数据库后端增加支持¶

```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
```