What's new in Django 1.8 Conditional Expressions #djangoja

I wrote this article while attending TokyoDjangoMeetup # 3.

What can you do?

New feature added in Django 1.8 Conditional Expressions, You can now pass conditional expressions to the ʻannotation, ʻaggregation, and ʻupdate` methods used when manipulating models.

Sample model

The following models are available. [^ 1]

`models.py`


# -*- coding: utf-8 -*-
from django.db import models


class Sponsor(models.Model):

    u"""
Sponsor of PyCon JP 2015
    """
    DIAMOND = 'D'
    PLATINUM = 'P'
    GOLD = 'G'
    SILVER = 'S'
    PLAN_CHOICES = (
        (DIAMOND, 'Diamond'),
        (PLATINUM, 'Platinum'),
        (GOLD, 'Gold'),
        (SILVER, 'Silver'),
    )
    name = models.CharField(max_length=50)
    registered_on = models.DateField()
    plan = models.CharField(
        max_length=1,
        choices=PLAN_CHOICES,
        default=SILVER,
    )

The test data is as follows.

>>> from datetime import date, timedelta
>>> Sponsor.objects.create(
...     name='Crazy Inc.',
...     plan=Sponsor.DIAMOND,
...     registered_on=date.today() - timedelta(days=19))
<Sponsor: Sponsor object>
>>> Sponsor.objects.create(
...     name='Star Inc.',
...     plan=Sponsor.PLATINUM,
...     registered_on=date.today() - timedelta(days=36))
<Sponsor: Sponsor object>
>>> Sponsor.objects.create(
...     name='Experience Inc.',
...     plan=Sponsor.GOLD,
...     registered_on=date.today() - timedelta(days=11))
<Sponsor: Sponsor object>
>>> Sponsor.objects.create(
...     name='Giorno co., ltd.',
...     plan=Sponsor.GOLD,
...     registered_on=date.today() - timedelta(days=6))
<Sponsor: Sponsor object>
>>> Sponsor.objects.create(
...     name='Chariot Inc.',
...     plan=Sponsor.SILVER,
...     registered_on=date.today() - timedelta(days=1))
<Sponsor: Sponsor object>
>>> Sponsor.objects.create(
...     name='Polnareff co., ltd.',
...     plan=Sponsor.SILVER,
...     registered_on=date.today() - timedelta(days=2))
<Sponsor: Sponsor object>

Actually use

Conditional Expressions is written by combining the following two classes.

When class
A class that encapsulates "conditions" and "results"
Case class
A class of roles that correspond to conditional expressions such as Python's ʻif ... elif ... else. The return value is the "result" of When` when the" condition "is true.

The When class specifies the" condition "first and the" result "last. The way to specify the "condition" is the same as the filter method.

>>> from django.db.models import When, F, Q
>>> #Refer to the name field when plan is Gold
>>> When(plan=Sponsor.GOLD, then='name')
>>> When(plan=Sponsor.GOLD, then=F('name'))
>>> from datetime import date
>>> # registered_on is 2015/2/1 to 2015/9/Refer to the plan field if it is between 1
>>> When(registered_on__gt=date(2015, 2, 1),
...      registered_on__lt=date(2015, 9, 1),
...      then='plan')
>>> #You can also specify OR conditions in the Q object
>>> When(Q(name__startswith="Crazy") | Q(name__startswith="Giorno"),
...      then='name')

Combine the When class above with the Case class to find the sponsorship fee for each sponsor.

>>> from django.db.models import Case, When, Value, CharField
>>> Sponsor.objects.annotate(
...     fee=Case(
...         When(plan=Sponsor.DIAMOND, then=Value(u'1,000,000 Yen(Excluding tax)')),
...         When(plan=Sponsor.PLATINUM, then=Value(u'500,000 Yen(Excluding tax)')),
...         When(plan=Sponsor.GOLD, then=Value(u'300,000 Yen(Excluding tax)')),
...         When(plan=Sponsor.SILVER, then=Value(u'100,000 Yen(Excluding tax)')),
...         output_field=CharField(),
...     ),
... ).values_list('name', 'fee')
[(u'Crazy Inc.', u'1,000,000\u5186 (\u7a0e\u629c\u304d)'), (u'Star Inc.', u'500,000\u5186 (\u7a0e\u629c\u304d)'), (u'Experience Inc.', u'300,000\u5186 (\u7a0e\u629c\u304d)'), (u'Giorno co., ltd.', u'300,000\u5186 (\u7a0e\u629c\u304d)'), (u'Chariot Inc.', u'100,000\u5186 (\u7a0e\u629c\u304d)'), (u'Polnareff co., ltd.', u'100,000\u5186 (\u7a0e\u629c\u304d)')]

Let's update plan according to the value of registered_on.

>>> from django.db.models import Case, When, Value
>>> from datetime import date,timedelta
>>> a_week_ago = date.today() - timedelta(weeks=1)
>>> a_month_ago = date.today() - timedelta(days=30)
>>> Sponsor.objects.update(
...     plan=Case(
...         When(registered_on__lte=a_week_ago ,
...              then=Value(Sponsor.PLATINUM)),
...         When(registered_on__lte=a_month_ago,
...              then=Value(Sponsor.GOLD)),
...         default=Value(Sponsor.SILVER)
...     ),
... )
>>> Sponsor.objects.values_list('name', 'plan')
[(u'Crazy Inc.', u'P'), (u'Star Inc.', u'P'), (u'Experience Inc.', u'P'), (u'Giorno co., ltd.', u'S'), (u'Chariot Inc.', u'S'), (u'Polnareff co., ltd.', u'S')]

Find out how many companies have applied for each plan.

>>> from django.db.models import Case, When, Value, IntegerField, Sum
>>> Sponsor.objects.aggregate(
...     silver=Sum(
...         Case(When(plan=Sponsor.SILVER, then=1),
...              output_field=IntegerField())
...     ),
...     gold=Sum(
...         Case(When(plan=Sponsor.GOLD, then=1),
...              output_field=IntegerField())
...     ),
...     platinum=Sum(
...         Case(When(plan=Sponsor.PLATINUM, then=1),
...              output_field=IntegerField())
...     )
... )
{'platinum': 3, 'silver': 3, 'gold': None}

What kind of SQL is created?

Let's take a look at the actual SQL created using the debugsqlshell command in djnago-debug-toolbar. [^ 2]

>>> Sponsor.objects.annotate(
...     fee=Case(
...         When(plan=Sponsor.DIAMOND, then=Value(u'1,000,000 Yen(Excluding tax)')),
...         When(plan=Sponsor.PLATINUM, then=Value(u'500,000 Yen(Excluding tax)')),
...         When(plan=Sponsor.GOLD, then=Value(u'300,000 Yen(Excluding tax)')),
...         When(plan=Sponsor.SILVER, then=Value(u'100,000 Yen(Excluding tax)')),
...         output_field=CharField(),
...     ),
... ).values_list('name', 'fee')
SELECT `sponsors_sponsor`.`name`,
       CASE
           WHEN `sponsors_sponsor`.`plan` = 'D' THEN '1,000,000 Yen(Excluding tax)'
           WHEN `sponsors_sponsor`.`plan` = 'P' THEN '500,000 Yen(Excluding tax)'
           WHEN `sponsors_sponsor`.`plan` = 'G' THEN '300,000 Yen(Excluding tax)'
           WHEN `sponsors_sponsor`.`plan` = 'S' THEN '100,000 Yen(Excluding tax)'
           ELSE NULL
       END AS `fee`
FROM `sponsors_sponsor` LIMIT 21 [0.23ms]

>>> Sponsor.objects.update(
...     plan=Case(
...         When(registered_on__lte=a_week_ago ,
...              then=Value(Sponsor.PLATINUM)),
...         When(registered_on__lte=a_month_ago,
...              then=Value(Sponsor.GOLD)),
...         default=Value(Sponsor.SILVER)
...     ),
... )
UPDATE `sponsors_sponsor`
SET `plan` = CASE
                 WHEN `sponsors_sponsor`.`registered_on` <= '2015-04-22' THEN 'P'
                 WHEN `sponsors_sponsor`.`registered_on` <= '2015-03-30' THEN 'G'
                 ELSE 'S'
             END [0.62ms]

>>> Sponsor.objects.aggregate(
...     silver=Sum(
...         Case(When(plan=Sponsor.SILVER, then=1),
...              output_field=IntegerField())
...     ),
...     gold=Sum(
...         Case(When(plan=Sponsor.GOLD, then=1),
...              output_field=IntegerField())
...     ),
...     platinum=Sum(
...         Case(When(plan=Sponsor.PLATINUM, then=1),
...              output_field=IntegerField())
...     )
... )
SELECT SUM(CASE WHEN `sponsors_sponsor`.`plan` = 'P' THEN 1 ELSE NULL END) AS `platinum`,
       SUM(CASE WHEN `sponsors_sponsor`.`plan` = 'S' THEN 1 ELSE NULL END) AS `silver`,
       SUM(CASE WHEN `sponsors_sponsor`.`plan` = 'G' THEN 1 ELSE NULL END) AS `gold`
FROM `sponsors_sponsor` [0.32ms]

[^ 1]: Sample code of the original document [PyCon JP 2015 Sponsorship Information](https: // Modified with reference to //pycon.jp/2015/sponsors/prospectus.html). [^ 2]: The database uses MySQL 5.5.43.