Conditional Expressions

We'll be using the following model in the subsequent examples:

class When(condition=None, then=None, **lookups)[源代码]
A When() object is used to encapsulate a condition and its result for usein the conditional expression. Using a When() object is similar to usingthe filter() method. The condition canbe specified using orQ objects. The result is provided using the thenkeyword.

Some examples:

>>> from django.db.models import F, Q, When
>>> # 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')

Keep in mind that each of these values can be an expression.

注解

Since the then keyword argument is reserved for the result of theWhen(), there is a potential conflict if a has a field named then. This can beresolved in two ways:

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

A expression is like the if … …else statement in Python. Each condition in the providedWhen() objects is evaluated in order, until one evaluates to atruthful value. The result expression from the matching When() objectis returned.

Case() accepts any number of When() objects as individual arguments.Other options are provided using keyword arguments. If none of the conditionsevaluate to TRUE, then the expression given with the default keywordargument is returned. If a default argument isn't provided, isused.

If we wanted to change our previous query to get the discount based on how longthe Client has been with us, we could do so using lookups:

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

注解

Remember that the conditions are evaluated in order, so in the aboveexample we get the correct result even though the second condition matchesboth Jane Doe and Jack Black. This works just like an …elif … statement in Python.

Case() also works in a filter() clause. For example, to find goldclients that registered more than a month ago and platinum clients thatregistered more than a year ago:

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

Advanced queries

Let's say we want to change the account_type for our clients to matchtheir registration dates. We can do this using a conditional expression and the method:

What if we want to find out how many clients there are for eachaccount_type? We can use the filter argument of aggregatefunctions to achieve this:

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

This aggregate produces a query with the SQL 2003 FILTER WHERE syntaxon databases that support it:

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

On other databases, this is emulated using a CASE statement:

The two SQL statements are functionally equivalent but the more explicit may perform better.