Help us understand the problem. What is going on with this article?

Django1.8の新機能Conditional Expressions #djangoja

More than 3 years have passed since last update.

この記事はTokyoDjangoMeetup #3参加中に書きました。

どんなことができるのか?

Django1.8で追加された新機能Conditional Expressionsで、モデル操作時に使うannotationaggregationupdateメソッドに条件式を渡せるようになりました。

サンプルのモデル

以下のモデルを用意しました。1

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


class Sponsor(models.Model):

    u"""
    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,
    )

テストデータは以下のとおりです。

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

実際に使ってみる

Conditional Expressionsは、以下の2つのクラスを組み合わせて書きます。

  • Whenクラス
    • 「条件」と「結果」をカプセル化したクラス
  • Caseクラス
    • Pythonのif ... elif ... elseのような条件式に当たる役割のクラス。「条件」が真となるWhenの「結果」を戻り値とする。

Whenクラスは、最初に「条件」、最後に「結果」を指定します。
「条件」の指定の仕方はfilterメソッドと同じです。

>>> from django.db.models import When, F, Q
>>> # planがGoldのときにnameフィールドを参照する
>>> When(plan=Sponsor.GOLD, then='name')
>>> When(plan=Sponsor.GOLD, then=F('name'))
>>> from datetime import date
>>> # registered_onが2015/2/1から2015/9/1の間ならplanフィールドを参照する
>>> When(registered_on__gt=date(2015, 2, 1),
...      registered_on__lt=date(2015, 9, 1),
...      then='plan')
>>> # QオブジェクトでOR条件を指定することもできる
>>> When(Q(name__startswith="Crazy") | Q(name__startswith="Giorno"),
...      then='name')

上記のWhenクラスをCaseクラスと組み合わせて、各スポンサーのスポンサー料を求めてみましょう。

>>> from django.db.models import Case, When, Value, CharField
>>> Sponsor.objects.annotate(
...     fee=Case(
...         When(plan=Sponsor.DIAMOND, then=Value(u'1,000,000円 (税抜き)')),
...         When(plan=Sponsor.PLATINUM, then=Value(u'500,000円 (税抜き)')),
...         When(plan=Sponsor.GOLD, then=Value(u'300,000円 (税抜き)')),
...         When(plan=Sponsor.SILVER, then=Value(u'100,000円 (税抜き)')),
...         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)')]

registered_onの値に応じてplanを更新してみましょう。

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

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}

どんなSQLが作られているのか?

djnago-debug-toolbardebugsqlshellコマンドを使って、実際に作られるSQLを見てみましょう。2

>>> Sponsor.objects.annotate(
...     fee=Case(
...         When(plan=Sponsor.DIAMOND, then=Value(u'1,000,000円 (税抜き)')),
...         When(plan=Sponsor.PLATINUM, then=Value(u'500,000円 (税抜き)')),
...         When(plan=Sponsor.GOLD, then=Value(u'300,000円 (税抜き)')),
...         When(plan=Sponsor.SILVER, then=Value(u'100,000円 (税抜き)')),
...         output_field=CharField(),
...     ),
... ).values_list('name', 'fee')
SELECT `sponsors_sponsor`.`name`,
       CASE
           WHEN `sponsors_sponsor`.`plan` = 'D' THEN '1,000,000円 (税抜き)'
           WHEN `sponsors_sponsor`.`plan` = 'P' THEN '500,000円 (税抜き)'
           WHEN `sponsors_sponsor`.`plan` = 'G' THEN '300,000円 (税抜き)'
           WHEN `sponsors_sponsor`.`plan` = 'S' THEN '100,000円 (税抜き)'
           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. 本家ドキュメントのサンプルコードPyCon JP 2015スポンサーシップのご案内を参考に改変。 

  2. データベースはMySQL5.5.43を使っています。 

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away