Debian Codegraph @

docs/ref/models/conditional-expressions.txt

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