查询表达式

查询表达式描述了一个值或一个计算，它可以作为更新、创建、过滤、排序、注解或聚合的一部分。当一个表达式输出一个布尔值时，它可以直接用于过滤器中。有许多内置的表达式（在下面的文档中）可以用来帮助你编写查询。表达式可以组合，或者在某些情况下嵌套，以形成更复杂的计算。

支持的算术

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

一些例子

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

内置表达式

备注

这些表达式在 django.db.models.expressions 和 django.db.models.aggregates 中定义，但为了方便起见，通常从 django.db.models 中导入使用。

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.

取而代之的是，Django 使用 F() 对象来生成一个 SQL 表达式，在数据库层面描述所需的操作。

我们举个例子试试。通常情况下，我们可以这样做：

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

这里，我们从数据库中提取了 reporter.stories_filed 的值到内存中，并使用熟悉的 Python 操作符对其进行操作，然后将对象保存回数据库。但我们也可以这样做：

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

虽然 reporter.stories_filed = F('stories_filed') + 1 看起来像一个普通的 Python 赋值给一个实例属性，但实际上它是一个描述数据库操作的 SQL 结构。

当 Django 遇到 F() 的实例时，它会覆盖标准的 Python 运算符来创建一个封装的 SQL 表达式；在本例中，它指示数据库递增由 reporter.stories_filed 表示的数据库字段。

无论 reporter.stories_filed 上的值是多少，Python 永远不会知道它——它完全由数据库处理。通过 Django 的 F() 类，Python 所做的就是创建 SQL 语法来引用这个字段并描述操作。

要访问这样保存的新值，必须重新加载对象：

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

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

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

我们还可以使用 update() 来递增多个对象上的字段值——这可能比从数据库中把它们全部拉到 Python 中，在它们身上循环，递增每个对象的字段值，然后把每个对象保存回数据库要快得多：

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

因此，F() 可以通过以下方式提供性能优势：

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

使用 F() 避免竞争条件

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

如果两个 Python 线程执行上面第一个例子中的代码，一个线程可以在另一个线程从数据库中获取一个字段的值后，检索、递增并保存它。第二个线程保存的值将基于原始值，第一个线程的工作将丢失。

如果数据库负责更新字段，那么这个过程就比较稳健：它只会在执行 save() 或 update() 时，根据数据库中字段的值来更新字段，而不是根据检索实例时的值来更新。

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

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

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

在这种情况下，stories_filed 将被更新两次。如果最初是 1，最终值将是 3。这种持久性可以通过在保存模型对象后重新加载来避免，例如，使用 refresh_from_db()。

在过滤器中使用 F()

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

这在 在查询中使用 F() 表达式 中有所记载。

与注解一起使用 F()

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

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

如果你要组合的字段是不同类型的，你需要告诉 Django 将返回什么样的字段。由于 F() 不直接支持 output_field，你需要用 ExpressionWrapper 来包装表达式：

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

当引用关系字段如 ForeignKey 时，F() 返回主键值而不是模型实例：

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

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

使用 F() 和 Expression.asc() 或 esc() 的关键词参数 nulls_first 或 nulls_last 来控制字段的空值的排序。默认情况下，排序取决于你的数据库。

例如，在已经联系过的公司之后，对尚未联系过的公司进行排序（last_contacted 为空）：

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

Func() 表达式

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

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

或者可以用它们来建立一个数据库函数库：

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

但这两种情况都会产生一个查询集，其中每个模型都有一个额外的属性 field_lower 来注释，大致由以下 SQL 产生：

  1. SELECT
  2.     ...
  3.     LOWER("db_table"."field") as "field_lower"

参见 数据库函数 获取内置的数据库函数列表。

Func API 如下：

class Func(\expressions, **extra*)

  • function

    描述将要生成的函数的类属性。具体来说，function 将作为 function 占位符插在 template 中。默认值为 None。

  • template

    一个类属性，作为格式字符串，描述为该函数生成的SQL。默认值为 '%(function)s(%(expressions)s)'。

    如果你正在构造像 strftime('%W', 'date') 这样的 SQL，并且需要在查询中使用 % 字符，那么在 template 属性中把它四倍（%%%%），因为这个字符串会被插值两次：一次是在 as_sql() 中的模板插值中，另一次是在数据库游标中的查询参数的 SQL 插值中。

  • arg_joiner

    一个类属性，表示用于将 expressions 列表连接在一起的字符。默认值为 ', '。

  • arity

    一个类属性，表示函数接受的参数数。如果设置了这个属性，并且用不同数量的表达式调用函数，将引发 TypeError。默认值为 None。

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

    生成数据库函数的 SQL 片段。返回一个元组 (sql, params)，其中 sql 是 SQL 字符串，params 是查询参数的列表或元组。

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

    django/db/models/functions.py

    1. class ConcatPair(Func):
    2.     ...
    3.     function = 'CONCAT'
    4.     ...
    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.         )

    为了避免 SQL 注入漏洞，extra_context 不得包含不受信任的用户输入，因为这些值会被内插到 SQL 字符串中，而不是作为查询参数传递，数据库驱动对其进行转义。

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

位置参数可以是表达式或 Python 值。字符串被认为是列引用，将被包装在 F() 表达式中，而其他值将被包装在 Value() 表达式中。

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

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

Aggregate() 表达式

聚合表达式是 Func() 表达式 的一个特例，它通知查询需要一个 GROUP BY 子句。所有的 聚合函数，如 Sum() 和 Count()，都继承自 Aggregate()。

由于 Aggregate 是表达式和封装表达式，你可以表示一些复杂的计算：

  1. from django.db.models import Count
  3. Company.objects.annotate(
  4.     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

    作为格式字符串的类属性，描述为该集合生成的 SQL。默认值为 '%(function)s(%(distinct)s%(expressions)s)'。

  • function

    描述将生成的集合函数的类属性。具体来说，function 将作为 function 占位符插值在 template 中。默认值为 None。

  • window_compatible

    默认为 True，因为大多数聚合函数可以作为 Window 中的源表达式。

  • allow_distinct

    一个类属性，决定该聚合函数是否允许传递 distinct 关键字参数。如果设置为 False （默认），如果传递了 distinct=True，会引发 TypeError。

  • 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 等）都不会在表达式的输出值上执行。

请注意，只有当 Django 无法确定结果的字段类型时，才需要使用 output_field。混合字段类型的复杂表达式应该定义所需的 output_field。例如，将一个 IntegerField() 和一个 FloatField() 加在一起，可能应该定义 output_field=FloatField()。

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 4.0:

The default argument was added.

创建你自己的聚合函数

你也可以创建自己的聚合函数。至少，你需要定义 function，但你也可以完全自定义生成的 SQL。下面是一个简单的例子：

  1. from django.db.models import Aggregate
  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
  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() 表达式

class Value(value, output_field=None)

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

你很少会需要直接使用 Value()。当你写下表达式 F('field') + 1 时，Django 会隐式地将 1 包裹在 Value() 中，允许简单的值被用于更复杂的表达式中。当你想把一个字符串传递给一个表达式时，你需要使用 Value()。大多数表达式将字符串参数解释为字段的名称，如 Lower('name')。

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.

ExpressionWrapper() 表达式

class ExpressionWrapper(expression, output_field)

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

条件表达式

条件表达式允许你在查询中使用 if … elif … else 逻辑。Django 原生支持 SQL CASE 表达式。更多细节请参见 条件表达式。

Subquery() 表达式

class Subquery(queryset, output_field=None)

你可以使用 Subquery 表达式向 QuerySet 添加一个显式子查询。

例如，在每篇文章中标注该文章最新评论的作者的电子邮件地址：

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

在 PostgreSQL 上，SQL 看起来像：

  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"

备注

本节中的例子是为了展示如何强制 Django 执行一个子查询。在某些情况下，可以写一个等价的查询集，更清楚或更有效地执行同样的任务。

从外部查询集中引用列

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 实例中才能正确解析：

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

将子查询限制为单列

有时必须从 Subquery 中返回一列，例如，使用 Subquery 作为 __in 查询的目标。要返回最近一天内发表的所有文章评论：

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

在这种情况下，子查询必须使用 values() 只返回一列：该文章的主键。

将子查询限制为单行

为了防止子查询返回多条记录，使用了查询集的一个片断（[:1]）：

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

在这种情况下，子查询必须只返回一列 和 一行：最近创建的评论的电子邮件地址。

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

Exists() 子查询

class Exists(queryset)

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

例如，要对每篇文章进行注解，说明它是否有过去一天内的评论：

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

在 PostgreSQL 上，SQL 看起来像：

  1. SELECT "post"."id", "post"."published_at", EXISTS(
  2.     SELECT (1) as "a"
  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.     LIMIT 1
  9. ) AS "recent_comment" FROM "post"

没有必要强制 Exists 指向单一列，因为列会被丢弃，并返回一个布尔结果。同样，由于在 SQL EXISTS 子查询中，排序并不重要，只会降低性能，所以会自动删除。

可以用 `~Exists() 来查询 NOT EXISTS。

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

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

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

这将确保子查询不会被添加到 SELECT 列中，这可能会带来更好的性能。

在 Subquery 表达式中使用集合。

聚合可以在 Subquery 中使用，但需要将 filter()、values() 和 annotate() 进行特定的组合，才能使子查询分组正确。

假设两种模式都有 length 字段，要找到帖子长度大于所有合计评论总长度的帖子：

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

初始的 filter(...) 将子查询限制在相关参数上。order_by() 删除 Comment 模型上的默认 Order （如果有的话）。values('post') 按 Post 聚合评论。最后，annotate(...) 执行聚合。这些查询集方法的应用顺序很重要。在这种情况下，由于子查询必须限于一列，所以需要使用 values('total')。

这是在 Subquery 内进行聚合的唯一方法，因为使用 aggregary() 试图执行查询集（如果有 OuterRef，将无法解决）。

原始 SQL 表达式

class RawSQL(sql, params, output_field=None)

有时，数据库表达式不容易表达一个复杂的 WHERE 子句。在这些边缘情况下，使用 RawSQL 表达式。例如：

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

这些额外的查找可能无法移植到不同的数据库引擎中（因为你是显式地编写 SQL 代码），并且违反了 DRY 原则，所以你应该尽可能地避免它们。

RawSQL expressions can also be used as the target of __in filters:

  1. >>> queryset.filter(id__in=RawSQL("select id from sometable where col = %s", (param,)))

警告

为了防止 SQL 注入攻击 ，你必须使用 params 来转义任何用户可以控制的参数。params 是一个必要的参数，以迫使你承认你没有用用户提供的数据来插值你的 SQL。

你也不能在 SQL 字符串中引用占位符。这个例子由于在 %s 周围的引号而容易受到 SQL 注入：

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

你可以阅读更多关于 Django 的 SQL 注入保护 的工作原理。

窗口函数

窗口函数提供了一种在分区上应用函数的方法。与一般的聚合函数不同，窗口函数对 帧 和分区进行操作，并计算每行的结果。

你可以在同一个查询中指定多个窗口，这在 Django ORM 中相当于在一个 QuerySet.annotate() 调用中包含多个表达式。ORM 并没有利用命名窗口，而是将其作为所选列的一部分。

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

  • filterable

    默认值为 False。SQL 标准不允许在 WHERE 子句中引用窗口函数，当构建 QuerySet 时，Django 会引发异常。

  • template

    默认为 %(expression)sOVER (%(window)s)'。如果只提供 expression 参数，窗口子句将是空白的。

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 on which you can call asc() and desc(), a string of a field name (with an optional "-" prefix which indicates descending order), or a tuple or list of strings and/or expressions. 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 参数指定在计算中应该使用哪些其他行。详见 帧。

Changed in Django 4.1:

Support for order_by by field name references was added.

例如，用同一制片厂在同一类型和发行年份的电影的平均评分来注解每部电影：

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

这可以让你检查一部电影的评分是好是坏，与它的同行相比。

你可能希望在同一个窗口，即同一个分区和帧上应用多个表达式。例如，你可以修改前面的例子，通过在同一查询中使用三个窗口函数，也包括每部电影的组别（相同的工作室、类型和发布年份）中的最佳和最差评级。前面例子中的分区和排序被提取到一个字典中，以减少重复：

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

在 Django 内置的数据库后端中，MySQL 8.0.2+、PostgreSQL、Oracle 都支持窗口表达式。不同的数据库对不同窗口表达式功能的支持也不同。例如， asc() 和 esc() 中的选项可能不被支持。根据需要，请查阅你的数据库的文档。

帧

对于一个窗口帧，你可以选择基于范围的行序列或普通的行序列。

class ValueRange(start=None, end=None)

  • frame_type

    该属性被设置为 'RANGE'。

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

class RowRange(start=None, end=None)

  • frame_type

    该属性被设置为 'ROWS'。

两个类都返回 SQL，模板为：

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

帧缩小了用于计算结果的行。它们从某个起点移动到某个指定的终点。帧可以不分区使用，但通常情况下，指定窗口的排序以确保结果的确定性是个好主意。在帧中，帧中的对等值是指具有等值的行，如果不存在排序子句，则是指所有行。

一个帧的默认起点是 UNBOUNDED PRECEDING，即分区的第一行。终点总是显式地包含在 ORM 生成的 SQL 中，默认为 UNBOUNDED FOLLOWING。默认帧包括从分区到集合中最后一行的所有行。

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

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

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

如果一部电影的”对等值”被描述为同一制片厂在同一年发行的同一类型的电影，那么这个 RowRange 的例子用一部电影的前后两部同行的平均评分来注解每部电影：

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

If the database supports it, you can specify the start and end points based on values of an expression in the partition. If the released field of the Movie model stores the release month of each movies, this ValueRange example annotates each movie with the average rating of a movie’s peers released between twelve months before and twelve months after the each movie:

  1. >>> from django.db.models import Avg, 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='released__year',
  7. >>>         frame=ValueRange(start=-12, end=12),
  8. >>>     ),
  9. >>> )

技术信息

下面你会发现对库作者可能有用的技术实现细节。下面的技术 API 和示例将有助于创建通用的查询表达式，可以扩展 Django 提供的内置功能。

表达式 API

查询表达式实现了 查询表达式 API，但也暴露了下面列出的一些额外的方法和属性。所有查询表达式必须继承于 Expression() 或相关子类。

当一个查询表达式包装另一个表达式时，它负责在被包装的表达式上调用相应的方法。

class Expression

  • contains_aggregate

    告诉 Django 这个表达式包含一个集合，需要在查询中添加一个 GROUP BY 子句。

  • contains_over_clause

    告诉 Django 这个表达式包含一个 Window 表达式。例如，它用于在修改数据的查询中不允许使用窗口函数表达式。

  • filterable

    告诉 Django 这个表达式可以在 QuerySet.filter() 中引用。默认值为 True。

  • window_compatible

    告诉 Django 这个表达式可以作为 Window 的源表达式。默认值为 False。

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

    Provides the chance to do any preprocessing 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 是后端查询的实现。

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

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

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

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

  • get_source_expressions()

    返回内部表达式的有序列表。例如：

    1. >>> Sum(F('foo')).get_source_expressions()
    2. [F('foo')]

  • set_source_expressions(expressions)

    获取一个表达式列表，并将其存储起来，使 get_source_expressions() 能够返回它们。

  • relabeled_clone(change_map)

    返回 self` 的克隆（副本），并重新标明所有列别名。当创建子查询时，列别名会被重新命名。relabeled_clone() 也应该对任何嵌套的表达式进行调用并分配给克隆。

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

    举例：

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

  • convert_value(value, expression, connection)

    一个钩子，允许表达式将 value` 强制转换成一个更合适的类型。

    expression 与 self 相同。

  • get_group_by_cols(alias=None)

    负责返回该表达式引用的列列表。get_group_by_cols() 应在任何嵌套的表达式上调用。F() 对象，尤其是持有列的引用。alias 参数将是 None，除非表达式已经被注解并用于分组。

  • asc(nulls_first=None, nulls_last=None)

    返回准备按升序排序的表达式。

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

    Changed in Django 4.1:

    In older versions, nulls_first and nulls_last defaulted to False.

    4.1 版后已移除: Passing nulls_first=False or nulls_last=False to asc() is deprecated. Use None instead.

  • desc(nulls_first=None, nulls_last=None)

    返回准备好降序排序的表达式。

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

    Changed in Django 4.1:

    In older versions, nulls_first and nulls_last defaulted to False.

    4.1 版后已移除: Passing nulls_first=False or nulls_last=False to desc() is deprecated. Use None instead.

  • reverse_ordering()

    返回 self，包括在 order_by 中对排序顺序进行的任何修改。例如，一个实现 NULLS LAST 的表达式将把它的值改为 NULLS FIRST。只有实现排序顺序的表达式，如 OrderBy 才需要修改。当 reverse() 在一个查询集上被调用时，会调用这个方法。

编写自己的查询表达式

你可以编写你自己的查询表达式类，这些类使用并可以与其他查询表达式集成。让我们通过一个例子，在不使用内置的 Func() 表达式 的情况下，编写一个 COALESCE SQL 函数的实现。

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

我们将首先定义用于生成 SQL 的模板和一个 __init__() 方法来设置一些属性：

  1. import copy
  2. from django.db.models import Expression
  4. class Coalesce(Expression):
  5.     template = 'COALESCE( %(expressions)s )'
  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

我们对参数进行一些基本的验证，包括要求至少有 2 个列或值，并确保它们是表达式。我们在这里要求 output_field，这样 Django 就知道要把最终的结果分配给什么样的模型字段。

Now we implement the preprocessing and validation. Since we do not have any of our own validation at this point, we delegate to the nested expressions:

  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

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

我们使用 compiler.compile() 方法为每个 expressions 生成 SQL，并将结果用逗号连接起来。然后在模板中填入我们的数据，并返回 SQL 和参数。

我们还定义了一个专门针对 Oracle 后端的自定义实现。如果使用 Oracle 后端，将调用 as_oracle() 函数，而不是 as_sql()。

最后，我们实现了其余的方法，使我们的查询表达式能够与其他查询表达式很好地配合：

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

让我们来看看它如何工作：

  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

避免 SQL 注入

由于 Func 的关键字参数 __init__() （**extra）和 as_sql() （**extra_context）的参数是内插到 SQL 字符串中，而不是作为查询参数传递的（数据库驱动程序会对其进行转义处理），因此它们不能包含不受信任的用户输入。

例如，如果 substring 是用户提供的，这个函数就容易被 SQL 注入：

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

这个函数在没有任何参数的情况下生成一个 SQL 字符串。由于 substring 是作为关键字参数传递给 super().__init__() 的，所以在将查询发送到数据库之前，它就被插入到 SQL 字符串中。

下面是更正后的改写：

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

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

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

如果你使用的数据库后端对某个函数使用了不同的 SQL 语法，你可以通过在函数的类上打一个新的方法来增加对它的支持。

比方说，我们正在为微软的 SQL Server 编写一个后端，它使用 SQL 的 LEN 而不是 LENGTH 来实现 Length 函数。我们将把一个名为 as_sqlserver() 的新方法移植到 Length 类上：

  1. from django.db.models.functions import Length
  3. def sqlserver_length(self, compiler, connection):
  4.     return self.as_sql(compiler, connection, function='LEN')
  6. Length.as_sqlserver = sqlserver_length

你也可以使用 as_sql() 的 template 参数自定义 SQL。

我们使用 as_sqlserver()，因为 django.db.connection.vendor 返回 sqlserver 作为后端。

第三方后端可以在后端包的顶层 __init__.py 文件或从顶层 __init__.py 导入的顶层 expressions.py 文件（或包）中注册它们的函数。

对于希望给自己正在使用的后端打补丁的用户项目来说，这段代码应该存在于 AppConfig.ready() 方法中。