| 1 |
======================= |
| 2 |
Conditional Expressions |
| 3 |
======================= |
| 4 |
|
| 5 |
.. currentmodule:: django.db.models.expressions |
| 6 |
|
| 7 |
Conditional expressions let you use :keyword:`if` ... :keyword:`elif` ... |
| 8 |
:keyword:`else` logic within filters, annotations, aggregations, and updates. A |
| 9 |
conditional expression evaluates a series of conditions for each row of a |
| 10 |
table and returns the matching result expression. Conditional expressions can |
| 11 |
also be combined and nested like other :doc:`expressions <expressions>`. |
| 12 |
|
| 13 |
The conditional expression classes |
| 14 |
================================== |
| 15 |
|
| 16 |
We'll be using the following model in the subsequent examples:: |
| 17 |
|
| 18 |
from django.db import models |
| 19 |
|
| 20 |
|
| 21 |
class Client(models.Model): |
| 22 |
REGULAR = "R" |
| 23 |
GOLD = "G" |
| 24 |
PLATINUM = "P" |
| 25 |
ACCOUNT_TYPE_CHOICES = { |
| 26 |
REGULAR: "Regular", |
| 27 |
GOLD: "Gold", |
| 28 |
PLATINUM: "Platinum", |
| 29 |
} |
| 30 |
name = models.CharField(max_length=50) |
| 31 |
registered_on = models.DateField() |
| 32 |
account_type = models.CharField( |
| 33 |
max_length=1, |
| 34 |
choices=ACCOUNT_TYPE_CHOICES, |
| 35 |
default=REGULAR, |
| 36 |
) |
| 37 |
|
| 38 |
``When`` |
| 39 |
-------- |
| 40 |
|
| 41 |
.. class:: When(condition=None, then=None, **lookups) |
| 42 |
|
| 43 |
A ``When()`` object is used to encapsulate a condition and its result for use |
| 44 |
in the conditional expression. Using a ``When()`` object is similar to using |
| 45 |
the :meth:`~django.db.models.query.QuerySet.filter` method. The condition can |
| 46 |
be specified using :ref:`field lookups <field-lookups>`, |
| 47 |
:class:`~django.db.models.Q` objects, or :class:`~django.db.models.Expression` |
| 48 |
objects that have an ``output_field`` that is a |
| 49 |
:class:`~django.db.models.BooleanField`. The result is provided using the |
| 50 |
``then`` keyword. |
| 51 |
|
| 52 |
Some examples: |
| 53 |
|
| 54 |
.. code-block:: pycon |
| 55 |
|
| 56 |
>>> from django.db.models import F, Q, When |
| 57 |
>>> # String arguments refer to fields; the following two examples are equivalent: |
| 58 |
>>> When(account_type=Client.GOLD, then="name") |
| 59 |
>>> When(account_type=Client.GOLD, then=F("name")) |
| 60 |
>>> # You can use field lookups in the condition |
| 61 |
>>> from datetime import date |
| 62 |
>>> When( |
| 63 |
... registered_on__gt=date(2014, 1, 1), |
| 64 |
... registered_on__lt=date(2015, 1, 1), |
| 65 |
... then="account_type", |
| 66 |
... ) |
| 67 |
>>> # Complex conditions can be created using Q objects |
| 68 |
>>> When(Q(name__startswith="John") | Q(name__startswith="Paul"), then="name") |
| 69 |
>>> # Condition can be created using boolean expressions. |
| 70 |
>>> from django.db.models import Exists, OuterRef |
| 71 |
>>> non_unique_account_type = ( |
| 72 |
... Client.objects.filter( |
| 73 |
... account_type=OuterRef("account_type"), |
| 74 |
... ) |
| 75 |
... .exclude(pk=OuterRef("pk")) |
| 76 |
... .values("pk") |
| 77 |
... ) |
| 78 |
>>> When(Exists(non_unique_account_type), then=Value("non unique")) |
| 79 |
>>> # Condition can be created using lookup expressions. |
| 80 |
>>> from django.db.models.lookups import GreaterThan, LessThan |
| 81 |
>>> When( |
| 82 |
... GreaterThan(F("registered_on"), date(2014, 1, 1)) |
| 83 |
... & LessThan(F("registered_on"), date(2015, 1, 1)), |
| 84 |
... then="account_type", |
| 85 |
... ) |
| 86 |
|
| 87 |
Keep in mind that each of these values can be an expression. |
| 88 |
|
| 89 |
.. note:: |
| 90 |
|
| 91 |
Since the ``then`` keyword argument is reserved for the result of the |
| 92 |
``When()``, there is a potential conflict if a |
| 93 |
:class:`~django.db.models.Model` has a field named ``then``. This can be |
| 94 |
resolved in two ways: |
| 95 |
|
| 96 |
.. code-block:: pycon |
| 97 |
|
| 98 |
>>> When(then__exact=0, then=1) |
| 99 |
>>> When(Q(then=0), then=1) |
| 100 |
|
| 101 |
``Case`` |
| 102 |
-------- |
| 103 |
|
| 104 |
.. class:: Case(*cases, **extra) |
| 105 |
|
| 106 |
A ``Case()`` expression is like the :keyword:`if` ... :keyword:`elif` ... |
| 107 |
:keyword:`else` statement in ``Python``. Each ``condition`` in the provided |
| 108 |
``When()`` objects is evaluated in order, until one evaluates to a |
| 109 |
truthful value. The ``result`` expression from the matching ``When()`` object |
| 110 |
is returned. |
| 111 |
|
| 112 |
An example: |
| 113 |
|
| 114 |
.. code-block:: pycon |
| 115 |
|
| 116 |
>>> |
| 117 |
>>> from datetime import date, timedelta |
| 118 |
>>> from django.db.models import Case, Value, When |
| 119 |
>>> Client.objects.create( |
| 120 |
... name="Jane Doe", |
| 121 |
... account_type=Client.REGULAR, |
| 122 |
... registered_on=date.today() - timedelta(days=36), |
| 123 |
... ) |
| 124 |
>>> Client.objects.create( |
| 125 |
... name="James Smith", |
| 126 |
... account_type=Client.GOLD, |
| 127 |
... registered_on=date.today() - timedelta(days=5), |
| 128 |
... ) |
| 129 |
>>> Client.objects.create( |
| 130 |
... name="Jack Black", |
| 131 |
... account_type=Client.PLATINUM, |
| 132 |
... registered_on=date.today() - timedelta(days=10 * 365), |
| 133 |
... ) |
| 134 |
>>> # Get the discount for each Client based on the account type |
| 135 |
>>> Client.objects.annotate( |
| 136 |
... discount=Case( |
| 137 |
... When(account_type=Client.GOLD, then=Value("5%")), |
| 138 |
... When(account_type=Client.PLATINUM, then=Value("10%")), |
| 139 |
... default=Value("0%"), |
| 140 |
... ), |
| 141 |
... ).values_list("name", "discount") |
| 142 |
<QuerySet [('Jane Doe', '0%'), ('James Smith', '5%'), ('Jack Black', '10%')]> |
| 143 |
|
| 144 |
``Case()`` accepts any number of ``When()`` objects as individual arguments. |
| 145 |
Other options are provided using keyword arguments. If none of the conditions |
| 146 |
evaluate to ``TRUE``, then the expression given with the ``default`` keyword |
| 147 |
argument is returned. If a ``default`` argument isn't provided, ``None`` is |
| 148 |
used. |
| 149 |
|
| 150 |
If we wanted to change our previous query to get the discount based on how long |
| 151 |
the ``Client`` has been with us, we could do so using lookups: |
| 152 |
|
| 153 |
.. code-block:: pycon |
| 154 |
|
| 155 |
>>> a_month_ago = date.today() - timedelta(days=30) |
| 156 |
>>> a_year_ago = date.today() - timedelta(days=365) |
| 157 |
>>> # Get the discount for each Client based on the registration date |
| 158 |
>>> Client.objects.annotate( |
| 159 |
... discount=Case( |
| 160 |
... When(registered_on__lte=a_year_ago, then=Value("10%")), |
| 161 |
... When(registered_on__lte=a_month_ago, then=Value("5%")), |
| 162 |
... default=Value("0%"), |
| 163 |
... ) |
| 164 |
... ).values_list("name", "discount") |
| 165 |
<QuerySet [('Jane Doe', '5%'), ('James Smith', '0%'), ('Jack Black', '10%')]> |
| 166 |
|
| 167 |
.. note:: |
| 168 |
|
| 169 |
Remember that the conditions are evaluated in order, so in the above |
| 170 |
example we get the correct result even though the second condition matches |
| 171 |
both Jane Doe and Jack Black. This works just like an :keyword:`if` ... |
| 172 |
:keyword:`elif` ... :keyword:`else` statement in ``Python``. |
| 173 |
|
| 174 |
``Case()`` also works in a ``filter()`` clause. For example, to find gold |
| 175 |
clients that registered more than a month ago and platinum clients that |
| 176 |
registered more than a year ago: |
| 177 |
|
| 178 |
.. code-block:: pycon |
| 179 |
|
| 180 |
>>> a_month_ago = date.today() - timedelta(days=30) |
| 181 |
>>> a_year_ago = date.today() - timedelta(days=365) |
| 182 |
>>> Client.objects.filter( |
| 183 |
... registered_on__lte=Case( |
| 184 |
... When(account_type=Client.GOLD, then=a_month_ago), |
| 185 |
... When(account_type=Client.PLATINUM, then=a_year_ago), |
| 186 |
... ), |
| 187 |
... ).values_list("name", "account_type") |
| 188 |
<QuerySet [('Jack Black', 'P')]> |
| 189 |
|
| 190 |
Advanced queries |
| 191 |
================ |
| 192 |
|
| 193 |
Conditional expressions can be used in annotations, aggregations, filters, |
| 194 |
lookups, and updates. They can also be combined and nested with other |
| 195 |
expressions. This allows you to make powerful conditional queries. |
| 196 |
|
| 197 |
Conditional update |
| 198 |
------------------ |
| 199 |
|
| 200 |
Let's say we want to change the ``account_type`` for our clients to match |
| 201 |
their registration dates. We can do this using a conditional expression and the |
| 202 |
:meth:`~django.db.models.query.QuerySet.update` method: |
| 203 |
|
| 204 |
.. code-block:: pycon |
| 205 |
|
| 206 |
>>> a_month_ago = date.today() - timedelta(days=30) |
| 207 |
>>> a_year_ago = date.today() - timedelta(days=365) |
| 208 |
>>> # Update the account_type for each Client from the registration date |
| 209 |
>>> Client.objects.update( |
| 210 |
... account_type=Case( |
| 211 |
... When(registered_on__lte=a_year_ago, then=Value(Client.PLATINUM)), |
| 212 |
... When(registered_on__lte=a_month_ago, then=Value(Client.GOLD)), |
| 213 |
... default=Value(Client.REGULAR), |
| 214 |
... ), |
| 215 |
... ) |
| 216 |
>>> Client.objects.values_list("name", "account_type") |
| 217 |
<QuerySet [('Jane Doe', 'G'), ('James Smith', 'R'), ('Jack Black', 'P')]> |
| 218 |
|
| 219 |
.. _conditional-aggregation: |
| 220 |
|
| 221 |
Conditional aggregation |
| 222 |
----------------------- |
| 223 |
|
| 224 |
What if we want to find out how many clients there are for each |
| 225 |
``account_type``? We can use the ``filter`` argument of :ref:`aggregate |
| 226 |
functions <aggregation-functions>` to achieve this: |
| 227 |
|
| 228 |
.. code-block:: pycon |
| 229 |
|
| 230 |
>>> # Create some more Clients first so we can have something to count |
| 231 |
>>> Client.objects.create( |
| 232 |
... name="Jean Grey", account_type=Client.REGULAR, registered_on=date.today() |
| 233 |
... ) |
| 234 |
>>> Client.objects.create( |
| 235 |
... name="James Bond", account_type=Client.PLATINUM, registered_on=date.today() |
| 236 |
... ) |
| 237 |
>>> Client.objects.create( |
| 238 |
... name="Jane Porter", account_type=Client.PLATINUM, registered_on=date.today() |
| 239 |
... ) |
| 240 |
>>> # Get counts for each value of account_type |
| 241 |
>>> from django.db.models import Count |
| 242 |
>>> Client.objects.aggregate( |
| 243 |
... regular=Count("pk", filter=Q(account_type=Client.REGULAR)), |
| 244 |
... gold=Count("pk", filter=Q(account_type=Client.GOLD)), |
| 245 |
... platinum=Count("pk", filter=Q(account_type=Client.PLATINUM)), |
| 246 |
... ) |
| 247 |
{'regular': 2, 'gold': 1, 'platinum': 3} |
| 248 |
|
| 249 |
This aggregate produces a query with the SQL 2003 ``FILTER WHERE`` syntax |
| 250 |
on databases that support it: |
| 251 |
|
| 252 |
.. code-block:: sql |
| 253 |
|
| 254 |
SELECT count('id') FILTER (WHERE account_type=1) as regular, |
| 255 |
count('id') FILTER (WHERE account_type=2) as gold, |
| 256 |
count('id') FILTER (WHERE account_type=3) as platinum |
| 257 |
FROM clients; |
| 258 |
|
| 259 |
On other databases, this is emulated using a ``CASE`` statement: |
| 260 |
|
| 261 |
.. code-block:: sql |
| 262 |
|
| 263 |
SELECT count(CASE WHEN account_type=1 THEN id ELSE null) as regular, |
| 264 |
count(CASE WHEN account_type=2 THEN id ELSE null) as gold, |
| 265 |
count(CASE WHEN account_type=3 THEN id ELSE null) as platinum |
| 266 |
FROM clients; |
| 267 |
|
| 268 |
The two SQL statements are functionally equivalent but the more explicit |
| 269 |
``FILTER`` may perform better. |
| 270 |
|
| 271 |
Conditional filter |
| 272 |
------------------ |
| 273 |
|
| 274 |
When a conditional expression returns a boolean value, it is possible to use it |
| 275 |
directly in filters. This means that it will not be added to the ``SELECT`` |
| 276 |
columns, but you can still use it to filter results: |
| 277 |
|
| 278 |
.. code-block:: pycon |
| 279 |
|
| 280 |
>>> non_unique_account_type = ( |
| 281 |
... Client.objects.filter( |
| 282 |
... account_type=OuterRef("account_type"), |
| 283 |
... ) |
| 284 |
... .exclude(pk=OuterRef("pk")) |
| 285 |
... .values("pk") |
| 286 |
... ) |
| 287 |
>>> Client.objects.filter(~Exists(non_unique_account_type)) |
| 288 |
|
| 289 |
In SQL terms, that evaluates to: |
| 290 |
|
| 291 |
.. code-block:: sql |
| 292 |
|
| 293 |
SELECT ... |
| 294 |
FROM client c0 |
| 295 |
WHERE NOT EXISTS ( |
| 296 |
SELECT c1.id |
| 297 |
FROM client c1 |
| 298 |
WHERE c1.account_type = c0.account_type AND NOT c1.id = c0.id |
| 299 |
) |