Conditional Expressions

Conditional Expressions建立一些逻辑关系

The conditional expression classes

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()对象与使用filter()方法类似。可以使用字段查找或Q对象指定条件。使用then关键字提供结果。

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

因为then关键字参数是为When()的结果保留的，所以如果模型有一个名为then的字段，就会有潜在的冲突。这可以通过两种方式解决:

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

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

Case

Case函数类似于python中的if elif else...,按照指定的When()对象的顺序求值，直到求出一个真值为止。返回匹配上的When()的对象。

>>> 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()接受任意数量的When()对象作为单独的参数。其他选项使用关键字参数提供。如果没有条件求值为TRUE，则返回带有默认关键字参数的表达式。如果没有提供默认参数，则不使用任何参数。

如果我们想改变我们之前的查询，以获得折扣的基础上多久的客户一直与我们，我们可以这样做，使用查找:

>>> 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()也适用于filter()子句。例如，找到一个多月前注册的黄金客户和一年多前注册的白金客户:

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

条件表达式可以用于annotations、aggregations、lookups和updates。它们还可以与其他表达式组合和嵌套。这允许您进行强大的条件查询。

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

Conditional aggregation

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

Transform SQL

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

巴特西