# 查询表达式¶

## 支持的算术¶

Django支持在查询表达式使用加减乘除，求模，幂运算，Python常量，变量甚至是其它表达式。

New in Django 1.7:

## 一些例子¶

Changed in Django 1.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())
```

## 内置表达式¶

### F() 表达式¶

class F

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

.和上面单独实例的操作一样， 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)
```

F()表达式的效率上的优点主要体现在

• 直接通过数据库操作而不是Python
• 减少数据库查询次数

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

F()查询集 过滤器中也十分有用，它使得使用条件通过字段值而不是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()))
```

### Func()表达式¶

New in Django 1.8.

Func() 表达式是所有表达式的基础类型，包括数据库函数如 COALESCELOWER, 或者 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')))
```

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

Func API如下：

class Func(*expressions, **extra)
function

template

arg_joiner

*expressions参数是函数将要应用与表达式的位置参数列表。表达式将转换为字符串，与arg_joiner连接在一起，然后作为expressions占位符插入template

**extra kwargs是可以插入到template属性中的key=value对。请注意，关键字functiontemplate可用于分别替换functiontemplate属性，而无需定义自己的类。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(expression, output_field=None, **extra)
template

function

expression参数可以是模型上的字段的名称，也可以是另一个表达式。它将转换为字符串，并用作template中的expressions占位符。

output_field参数需要一个模型字段实例，如IntegerField()BooleanField()，Django将在检索后数据库。通常在实例化模型字段时不需要任何参数，因为与数据验证相关的任何参数（max_lengthmax_digits等）不会对表达式的输出值强制执行。

Changed in Django 1.8:

output_field是一个新参数。

**extra kwargs是可以插入到template属性中的key=value对。

New in Django 1.8:

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

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

### Value()表达式¶

class Value(value, output_field=None)

Value()对象表示表达式的最小可能组件：简单值。当您需要在表达式中表示整数，布尔或字符串的值时，可以在Value()中包装该值。

value参数描述要包括在表达式中的值，例如1TrueNoneDjango知道如何将这些Python值转换为相应的数据库类型。

output_field参数应为模型字段实例，如IntegerField()BooleanField()，Django将在检索后从数据库。通常在实例化模型字段时不需要任何参数，因为与数据验证相关的任何参数（max_lengthmax_digits等）不会对表达式的输出值强制执行。

### ExpressionWrapper()表达式¶

class ExpressionWrapper(expression, output_field)
New in Django 1.8.

ExpressionWrapper简单地包围另一个表达式，并提供对其他表达式可能不可用的属性（例如output_field）的访问。当对Using F() with annotations中描述的不同类型的F()表达式使用算术时，必须使用ExpressionWrapper

### 条件表达式¶

New in Django 1.8.

## 技术信息¶

### 表达式API¶

class Expression
contains_aggregate

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

query是后端查询实现。

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

reuse是用于多连接场景的一组可重用连接。

summarize是一个布尔值，当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(self, value, expression, connection, context)

refs_aggregate(existing_aggregates)

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. 例如：

```queryset.filter(num_chairs__gt=F('sum__employees'))
```

F()表达式此处引用前一个Sum()计算，这意味着此过滤器表达式应添加到HAVING WHERE子句。

get_group_by_cols()

asc()

desc()

reverse_ordering()

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

COALESCE SQL函数定义为获取列或值的列表。它将返回不是NULL的第一列或值。

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

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

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

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