条件表达式

使用条件表达式可以在过滤器,注释,聚合和更新中使用if ... elif ... else逻辑。 条件表达式为表的每一行计算一系列条件并返回匹配的结果表达式。 条件表达式也可以像其他的expressions一样进行组合和嵌套。

条件表达式类

我们将在后面的例子中使用以下模型:

from django.db import models

class Client(models.Model):
    REGULAR = 'R'
    GOLD = 'G'
    PLATINUM = 'P'
    ACCOUNT_TYPE_CHOICES = (
        (REGULAR, 'Regular'),
        (GOLD, 'Gold'),
        (PLATINUM, 'Platinum'),
    )
    name = models.CharField(max_length=50)
    registered_on = models.DateField()
    account_type = models.CharField(
        max_length=1,
        choices=ACCOUNT_TYPE_CHOICES,
        default=REGULAR,
    )

When

When(condition=None, then=None, **lookups)[source]

A When()对象封装条件及其结果以在条件表达式中使用。 使用When()对象类似于使用filter()方法。 可以使用field lookupsQ对象指定条件。 结果是使用then关键字提供的。

一些例子:

>>> from django.db.models import When, F, Q
>>> # String arguments refer to fields; the following two examples are equivalent:
>>> When(account_type=Client.GOLD, then='name')
>>> When(account_type=Client.GOLD, then=F('name'))
>>> # You can use field lookups in the condition
>>> from datetime import date
>>> When(registered_on__gt=date(2014, 1, 1),
...      registered_on__lt=date(2015, 1, 1),
...      then='account_type')
>>> # Complex conditions can be created using Q objects
>>> When(Q(name__startswith="John") | Q(name__startswith="Paul"),
...      then='name')

请记住,每个这些值都可以是一个表达式。

注意

Since the then keyword argument is reserved for the result of the When(), there is a potential conflict if a Model has a field named then. 这可以通过两种方式来解决:

>>> When(then__exact=0, then=1)
>>> When(Q(then=0), then=1)

Case

个案*个案**额外[source]

A Case() expression is like the ifelifelse statement in Python. 在提供的When()对象中,每个condition按顺序进行评估,直到评估为真值。 The result expression from the matching When() object is returned.

一个简单的例子:

>>>
>>> from datetime import date, timedelta
>>> from django.db.models import CharField, Case, Value, When
>>> Client.objects.create(
...     name='Jane Doe',
...     account_type=Client.REGULAR,
...     registered_on=date.today() - timedelta(days=36))
>>> Client.objects.create(
...     name='James Smith',
...     account_type=Client.GOLD,
...     registered_on=date.today() - timedelta(days=5))
>>> Client.objects.create(
...     name='Jack Black',
...     account_type=Client.PLATINUM,
...     registered_on=date.today() - timedelta(days=10 * 365))
>>> # Get the discount for each Client based on the account type
>>> Client.objects.annotate(
...     discount=Case(
...         When(account_type=Client.GOLD, then=Value('5%')),
...         When(account_type=Client.PLATINUM, then=Value('10%')),
...         default=Value('0%'),
...         output_field=CharField(),
...     ),
... ).values_list('name', 'discount')
<QuerySet [('Jane Doe', '0%'), ('James Smith', '5%'), ('Jack Black', '10%')]>

Case() accepts any number of When() objects as individual arguments. 其他选项使用关键字参数提供。 如果没有任何条件评估为TRUE,则返回给出default关键字参数的表达式。 如果未提供default参数,则使用None

如果我们想根据Client与我们在一起的时间来更改我们以前的查询来获得折扣,我们可以使用查找来实现:

>>> a_month_ago = date.today() - timedelta(days=30)
>>> a_year_ago = date.today() - timedelta(days=365)
>>> # Get the discount for each Client based on the registration date
>>> Client.objects.annotate(
...     discount=Case(
...         When(registered_on__lte=a_year_ago, then=Value('10%')),
...         When(registered_on__lte=a_month_ago, then=Value('5%')),
...         default=Value('0%'),
...         output_field=CharField(),
...     )
... ).values_list('name', 'discount')
<QuerySet [('Jane Doe', '5%'), ('James Smith', '0%'), ('Jack Black', '10%')]>

注意

请记住,条件是按顺序评估的,所以在上面的例子中,即使第二个条件与Jane Doe和Jack Black都匹配,我们也能得到正确的结果。 This works just like an ifelifelse statement in Python.

Case() also works in a filter() clause. 例如,要查找一个多月前注册的黄金客户和一年多以前注册的铂金客户:

>>> a_month_ago = date.today() - timedelta(days=30)
>>> a_year_ago = date.today() - timedelta(days=365)
>>> Client.objects.filter(
...     registered_on__lte=Case(
...         When(account_type=Client.GOLD, then=a_month_ago),
...         When(account_type=Client.PLATINUM, then=a_year_ago),
...     ),
... ).values_list('name', 'account_type')
<QuerySet [('Jack Black', 'P')]>

高级查询

条件表达式可用于注释,聚合,查找和更新。 它们也可以与其他表达式组合并嵌套。 这使您可以进行强大的条件查询。

有条件的更新

假设我们要改变我们客户的account_type以匹配他们的注册日期。 我们可以使用条件表达式和update()方法来做到这一点:

>>> a_month_ago = date.today() - timedelta(days=30)
>>> a_year_ago = date.today() - timedelta(days=365)
>>> # Update the account_type for each Client from the registration date
>>> Client.objects.update(
...     account_type=Case(
...         When(registered_on__lte=a_year_ago,
...              then=Value(Client.PLATINUM)),
...         When(registered_on__lte=a_month_ago,
...              then=Value(Client.GOLD)),
...         default=Value(Client.REGULAR)
...     ),
... )
>>> Client.objects.values_list('name', 'account_type')
<QuerySet [('Jane Doe', 'G'), ('James Smith', 'R'), ('Jack Black', 'P')]>

条件聚合

如果我们想知道每个account_type有多少个客户端呢? 我们可以使用aggregate functionsfilter参数来实现这一点:

>>> # Create some more Clients first so we can have something to count
>>> Client.objects.create(
...     name='Jean Grey',
...     account_type=Client.REGULAR,
...     registered_on=date.today())
>>> Client.objects.create(
...     name='James Bond',
...     account_type=Client.PLATINUM,
...     registered_on=date.today())
>>> Client.objects.create(
...     name='Jane Porter',
...     account_type=Client.PLATINUM,
...     registered_on=date.today())
>>> # Get counts for each value of account_type
>>> from django.db.models import Count
>>> Client.objects.aggregate(
...     regular=Count('pk', filter=Q(account_type=Client.REGULAR)),
...     gold=Count('pk', filter=Q(account_type=Client.GOLD)),
...     platinum=Count('pk', filter=Q(account_type=Client.PLATINUM)),
... )
{'regular': 2, 'gold': 1, 'platinum': 3}

这个聚集在支持它的数据库上使用SQL 2003 FILTER WHERE语法生成一个查询:

SELECT count('id') FILTER (WHERE account_type=1) as regular,
       count('id') FILTER (WHERE account_type=2) as gold,
       count('id') FILTER (WHERE account_type=3) as platinum
FROM clients;

在其他数据库上,这是使用CASE语句来模拟的:

SELECT count(CASE WHEN account_type=1 THEN id ELSE null) as regular,
       count(CASE WHEN account_type=2 THEN id ELSE null) as gold,
       count(CASE WHEN account_type=3 THEN id ELSE null) as platinum
FROM clients;

这两个SQL语句在功能上是等价的,但更明确的FILTER可能会更好。