Conditional Expressions

蔚落 2021-12-10 07:55 187阅读 0赞

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}

转载于:https://www.cnblogs.com/LTEF/p/9736814.html