Conditional Expressions官方手册链接，包含Case()、按条件update

Contents

• Conditional Expressions
  • The conditional expression classes
    • When
    • Case
  • Advanced queries
    • Conditional update
    • Conditional aggregation

Browse

• Prev: Query Expressions
• Next: Database Functions
• Table of contents
• General Index
• Python Module Index

You are here:

• Django 2.1 documentation
  • API Reference
    • Models
      • Conditional Expressions

Case¶

class Case(*cases, **extra)[source]¶

A Case() expression is like the if … elif … else statement in Python. Each condition in the provided When() objects is evaluated in order, until one evaluates to a truthful value. The result expression from the matching When() object is returned.

A simple example:

>>>
>>> from datetime import date, timedelta
>>> from django.db.models import Case, CharField, 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. Other options are provided using keyword arguments. If none of the conditions evaluate to TRUE, then the expression given with the default keyword argument is returned. If a default argument isn’t provided, None is used.

If we wanted to change our previous query to get the discount based on how long the 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%')]>

以下是我插入的解释：

------------------------------------------------------------------------------------------------------------------------------------------------------------

Case（）函数中有个output_field=CharField()参数，原文没有解释，看 Case（）源代码，发现另一个函数有一段解释：

[docs]class ExpressionWrapper(Expression):
    """
    An expression that can wrap another expression so that it can provide
    extra context to the inner expression, such as the output_field.
    """

查ExpressionWrapper这个函数的定义：

ExpressionWrapper() expressions¶

class ExpressionWrapper(expression, output_field)[source]¶

ExpressionWrapper simply surrounds another expression and provides access to properties, such as output_field, that may not be available on other expressions. ExpressionWrapper is necessary when using arithmetic on F() expressions with different types as described in Using F() with annotations.

解释在F()函数的注释应用中有详细解释：

Using F() with annotations¶

F（）函数可以建立动态字段，动态字段的值是对已有不同字段进行数学运算所得值，比如对已有两个字段加减乘除的结果作为动态字段的值。

F() can be used to create dynamic fields on your models by combining different fields with arithmetic:

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

如果要绑定的两个字段是不用的类型，你需要指定返回结果的类型。F（）函数不能提供output_field 参数，你需要使用ExpressionWrapper()函数，以提供output_field=DateTimeField()这个参数。（虽然找不到active_at和duration这两个字段的定义，但是从名称理解，两个字段的类型不同，一个是DateTimeField，一个是DurationField）

If the fields that you’re combining are of different types you’ll need to tell Django what kind of field will be returned. Since F() does not directly support output_field you will need to wrap the expression with ExpressionWrapper:

from django.db.models import DateTimeField, ExpressionWrapper, F

Ticket.objects.annotate(
    expires=ExpressionWrapper(
        F('active_at') + F('duration'), output_field=DateTimeField()))

-------------------------------------------------------------------------------------------------------------------

以下接上文：

Note

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

Case() also works in a filter() clause. For example, to find gold clients that registered more than a month ago and platinum clients that registered 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¶

Conditional expressions can be used in annotations, aggregations, lookups, and updates. They can also be combined and nested with other expressions. This allows you to make powerful conditional queries.

Conditional update¶

Let’s say we want to change the account_type for our clients to match their registration dates. We can do this using a conditional expression and the update() method:

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