Debian Codegraph @

docs/ref/models/querysets.txt

raw
1 ==========================
2 ``QuerySet`` API reference
3 ==========================
4
5 .. currentmodule:: django.db.models.query
6
7 This document describes the details of the ``QuerySet`` API. It builds on the
8 material presented in the :doc:`model </topics/db/models>` and :doc:`database
9 query </topics/db/queries>` guides, so you'll probably want to read and
10 understand those documents before reading this one.
11
12 Throughout this reference we'll use the :ref:`example blog models
13 <queryset-model-example>` presented in the :doc:`database query guide
14 </topics/db/queries>`.
15
16 .. _when-querysets-are-evaluated:
17
18 When ``QuerySet``\s are evaluated
19 =================================
20
21 Internally, a ``QuerySet`` can be constructed, filtered, sliced, and generally
22 passed around without actually hitting the database. No database activity
23 actually occurs until you do something to evaluate the queryset.
24
25 You can evaluate a ``QuerySet`` in the following ways:
26
27 * **Iteration.** A ``QuerySet`` is iterable, and it executes its database
28 query the first time you iterate over it. For example, this will print
29 the headline of all entries in the database::
30
31 for e in Entry.objects.all():
32 print(e.headline)
33
34 Note: Don't use this if all you want to do is determine if at least one
35 result exists. It's more efficient to use :meth:`~QuerySet.exists`.
36
37 * **Asynchronous iteration.** A ``QuerySet`` can also be iterated over using
38 ``async for``::
39
40 async for e in Entry.objects.all():
41 results.append(e)
42
43 Both synchronous and asynchronous iterators of QuerySets share the same
44 underlying cache.
45
46 * **Slicing.** As explained in :ref:`limiting-querysets`, a ``QuerySet`` can
47 be sliced, using Python's array-slicing syntax. Slicing an unevaluated
48 ``QuerySet`` usually returns another unevaluated ``QuerySet``, but Django
49 will execute the database query if you use the "step" parameter of slice
50 syntax, and will return a list. Slicing a ``QuerySet`` that has been
51 evaluated also returns a list.
52
53 Also note that even though slicing an unevaluated ``QuerySet`` returns
54 another unevaluated ``QuerySet``, modifying it further (e.g., adding
55 more filters, or modifying ordering) is not allowed, since that does not
56 translate well into SQL and it would not have a clear meaning either.
57
58 * **Pickling/Caching.** See the following section for details of what
59 is involved when `pickling QuerySets`_. The important thing for the
60 purposes of this section is that the results are read from the database.
61
62 * **repr().** A ``QuerySet`` is evaluated when you call ``repr()`` on it.
63 This is for convenience in the Python interactive interpreter, so you can
64 immediately see your results when using the API interactively.
65
66 * **len().** A ``QuerySet`` is evaluated when you call ``len()`` on it.
67 This, as you might expect, returns the length of the result list.
68
69 Note: If you only need to determine the number of records in the set (and
70 don't need the actual objects), it's much more efficient to handle a count
71 at the database level using SQL's ``SELECT COUNT(*)``. Django provides a
72 :meth:`~QuerySet.count` method for precisely this reason.
73
74 * **list().** Force evaluation of a ``QuerySet`` by calling ``list()`` on
75 it. For example::
76
77 entry_list = list(Entry.objects.all())
78
79 * **bool().** Testing a ``QuerySet`` in a boolean context, such as using
80 ``bool()``, ``or``, ``and`` or an ``if`` statement, will cause the query
81 to be executed. If there is at least one result, the ``QuerySet`` is
82 ``True``, otherwise ``False``. For example::
83
84 if Entry.objects.filter(headline="Test"):
85 print("There is at least one Entry with the headline Test")
86
87 Note: If you only want to determine if at least one result exists (and don't
88 need the actual objects), it's more efficient to use :meth:`~QuerySet.exists`.
89
90 .. _pickling QuerySets:
91
92 Pickling ``QuerySet``\s
93 -----------------------
94
95 If you :mod:`pickle` a ``QuerySet``, this will force all the results to be loaded
96 into memory prior to pickling. Pickling is usually used as a precursor to
97 caching and when the cached queryset is reloaded, you want the results to
98 already be present and ready for use (reading from the database can take some
99 time, defeating the purpose of caching). This means that when you unpickle a
100 ``QuerySet``, it contains the results at the moment it was pickled, rather
101 than the results that are currently in the database.
102
103 If you only want to pickle the necessary information to recreate the
104 ``QuerySet`` from the database at a later time, pickle the ``query`` attribute
105 of the ``QuerySet``. You can then recreate the original ``QuerySet`` (without
106 any results loaded) using some code like this:
107
108 .. code-block:: pycon
109
110 >>> import pickle
111 >>> query = pickle.loads(s) # Assuming 's' is the pickled string.
112 >>> qs = MyModel.objects.all()
113 >>> qs.query = query # Restore the original 'query'.
114
115 The ``query`` attribute is an opaque object. It represents the internals of
116 the query construction and is not part of the public API. However, it is safe
117 (and fully supported) to pickle and unpickle the attribute's contents as
118 described here.
119
120 .. admonition:: Restrictions on ``QuerySet.values_list()``
121
122 If you recreate :meth:`QuerySet.values_list` using the pickled ``query``
123 attribute, it will be converted to :meth:`QuerySet.values`:
124
125 .. code-block:: pycon
126
127 >>> import pickle
128 >>> qs = Blog.objects.values_list("id", "name")
129 >>> qs
130 <QuerySet [(1, 'Beatles Blog')]>
131 >>> reloaded_qs = Blog.objects.all()
132 >>> reloaded_qs.query = pickle.loads(pickle.dumps(qs.query))
133 >>> reloaded_qs
134 <QuerySet [{'id': 1, 'name': 'Beatles Blog'}]>
135
136 .. admonition:: You can't share pickles between versions
137
138 Pickles of ``QuerySet`` objects are only valid for the version of Django
139 that was used to generate them. If you generate a pickle using Django
140 version N, there is no guarantee that pickle will be readable with
141 Django version N+1. Pickles should not be used as part of a long-term
142 archival strategy.
143
144 Since pickle compatibility errors can be difficult to diagnose, such as
145 silently corrupted objects, a ``RuntimeWarning`` is raised when you try to
146 unpickle a queryset in a Django version that is different than the one in
147 which it was pickled.
148
149 .. _queryset-api:
150
151 ``QuerySet`` API
152 ================
153
154 Here's the formal declaration of a ``QuerySet``:
155
156 .. class:: QuerySet(model=None, query=None, using=None, hints=None)
157
158 Usually when you'll interact with a ``QuerySet`` you'll use it by
159 :ref:`chaining filters <chaining-filters>`. To make this work, most
160 ``QuerySet`` methods return new querysets. These methods are covered in
161 detail later in this section.
162
163 The ``QuerySet`` class has the following public attributes you can use for
164 introspection:
165
166 .. attribute:: ordered
167
168 ``True`` if the ``QuerySet`` is ordered — i.e. has an
169 :meth:`order_by()` clause or a default ordering on the model.
170 ``False`` otherwise.
171
172 .. attribute:: db
173
174 The database that will be used if this query is executed now.
175
176 .. note::
177
178 The ``query`` parameter to :class:`QuerySet` exists so that specialized
179 query subclasses can reconstruct internal query state. The value of the
180 parameter is an opaque representation of that query state and is not
181 part of a public API.
182
183 .. currentmodule:: django.db.models.query.QuerySet
184
185 Methods that return new ``QuerySet``\s
186 --------------------------------------
187
188 Django provides a range of ``QuerySet`` refinement methods that modify either
189 the types of results returned by the ``QuerySet`` or the way its SQL query is
190 executed.
191
192 .. note::
193
194 These methods do not run database queries, therefore they are **safe to**
195 **run in asynchronous code**, and do not have separate asynchronous
196 versions.
197
198 ``filter()``
199 ~~~~~~~~~~~~
200
201 .. method:: filter(*args, **kwargs)
202
203 Returns a new ``QuerySet`` containing objects that match the given lookup
204 parameters.
205
206 The lookup parameters (``**kwargs``) should be in the format described in
207 `Field lookups`_ below. Multiple parameters are joined via ``AND`` in the
208 underlying SQL statement.
209
210 If you need to execute more complex queries (for example, queries with ``OR`` statements),
211 you can use :class:`Q objects <django.db.models.Q>` (``*args``).
212
213 ``exclude()``
214 ~~~~~~~~~~~~~
215
216 .. method:: exclude(*args, **kwargs)
217
218 Returns a new ``QuerySet`` containing objects that do *not* match the given
219 lookup parameters.
220
221 The lookup parameters (``**kwargs``) should be in the format described in
222 `Field lookups`_ below. Multiple parameters are joined via ``AND`` in the
223 underlying SQL statement, and the whole thing is enclosed in a ``NOT()``.
224
225 This example excludes all entries whose ``pub_date`` is later than 2005-1-3
226 AND whose ``headline`` is "Hello"::
227
228 Entry.objects.exclude(pub_date__gt=datetime.date(2005, 1, 3), headline="Hello")
229
230 In SQL terms, that evaluates to:
231
232 .. code-block:: sql
233
234 SELECT ...
235 WHERE NOT (pub_date > '2005-1-3' AND headline = 'Hello')
236
237 This example excludes all entries whose ``pub_date`` is later than 2005-1-3
238 OR whose headline is "Hello"::
239
240 Entry.objects.exclude(pub_date__gt=datetime.date(2005, 1, 3)).exclude(headline="Hello")
241
242 In SQL terms, that evaluates to:
243
244 .. code-block:: sql
245
246 SELECT ...
247 WHERE NOT pub_date > '2005-1-3'
248 AND NOT headline = 'Hello'
249
250 Note the second example is more restrictive.
251
252 If you need to execute more complex queries (for example, queries with ``OR`` statements),
253 you can use :class:`Q objects <django.db.models.Q>` (``*args``).
254
255 ``annotate()``
256 ~~~~~~~~~~~~~~
257
258 .. method:: annotate(*args, **kwargs)
259
260 Annotates each object in the ``QuerySet`` with the provided list of :doc:`query
261 expressions </ref/models/expressions>` or :class:`~django.db.models.Q` objects.
262 Each object can be annotated with:
263
264 * a simple value, via ``Value()``;
265 * a reference to a field on the model (or any related models), via ``F()``;
266 * a boolean, via ``Q()``; or
267 * a result from an aggregate expression (averages, sums, etc.) computed over
268 the objects that are related to the objects in the ``QuerySet``.
269
270 Each argument to ``annotate()`` is an annotation that will be added
271 to each object in the ``QuerySet`` that is returned.
272
273 The aggregation functions that are provided by Django are described
274 in `Aggregation Functions`_ below.
275
276 Annotations specified using keyword arguments will use the keyword as
277 the alias for the annotation. Anonymous arguments will have an alias
278 generated for them based upon the name of the aggregate function and
279 the model field that is being aggregated. Only aggregate expressions
280 that reference a single field can be anonymous arguments. Everything
281 else must be a keyword argument.
282
283 For example, if you were manipulating a list of blogs, you may want
284 to determine how many entries have been made in each blog:
285
286 .. code-block:: pycon
287
288 >>> from django.db.models import Count
289 >>> q = Blog.objects.annotate(Count("entry"))
290 # The name of the first blog
291 >>> q[0].name
292 'Blogasaurus'
293 # The number of entries on the first blog
294 >>> q[0].entry__count
295 42
296
297 The ``Blog`` model doesn't define an ``entry__count`` attribute by itself,
298 but by using a keyword argument to specify the aggregate function, you can
299 control the name of the annotation:
300
301 .. code-block:: pycon
302
303 >>> q = Blog.objects.annotate(number_of_entries=Count("entry"))
304 # The number of entries on the first blog, using the name provided
305 >>> q[0].number_of_entries
306 42
307
308 For an in-depth discussion of aggregation, see :doc:`the topic guide on
309 Aggregation </topics/db/aggregation>`.
310
311 ``alias()``
312 ~~~~~~~~~~~
313
314 .. method:: alias(*args, **kwargs)
315
316 Same as :meth:`annotate`, but instead of annotating objects in the
317 ``QuerySet``, saves the expression for later reuse with other ``QuerySet``
318 methods. This is useful when the result of the expression itself is not needed
319 but it is used for filtering, ordering, or as a part of a complex expression.
320 Not selecting the unused value removes redundant work from the database which
321 should result in better performance.
322
323 For example, if you want to find blogs with more than 5 entries, but are not
324 interested in the exact number of entries, you could do this:
325
326 .. code-block:: pycon
327
328 >>> from django.db.models import Count
329 >>> blogs = Blog.objects.alias(entries=Count("entry")).filter(entries__gt=5)
330
331 ``alias()`` can be used in conjunction with :meth:`annotate`, :meth:`exclude`,
332 :meth:`filter`, :meth:`order_by`, and :meth:`update`. To use aliased expression
333 with other methods (e.g. :meth:`aggregate`), you must promote it to an
334 annotation::
335
336 Blog.objects.alias(entries=Count("entry")).annotate(
337 entries=F("entries"),
338 ).aggregate(Sum("entries"))
339
340 :meth:`filter` and :meth:`order_by` can take expressions directly, but
341 expression construction and usage often does not happen in the same place (for
342 example, ``QuerySet`` method creates expressions, for later use in views).
343 ``alias()`` allows building complex expressions incrementally, possibly
344 spanning multiple methods and modules, refer to the expression parts by their
345 aliases and only use :meth:`annotate` for the final result.
346
347 ``order_by()``
348 ~~~~~~~~~~~~~~
349
350 .. method:: order_by(*fields)
351
352 By default, results returned by a ``QuerySet`` are ordered by the ordering
353 tuple given by the ``ordering`` option in the model's ``Meta``. You can
354 override this on a per-``QuerySet`` basis by using the ``order_by`` method.
355
356 Example::
357
358 Entry.objects.filter(pub_date__year=2005).order_by("-pub_date", "headline")
359
360 The result above will be ordered by ``pub_date`` descending, then by
361 ``headline`` ascending. The negative sign in front of ``"-pub_date"`` indicates
362 *descending* order. Ascending order is implied. To order randomly, use ``"?"``,
363 like so::
364
365 Entry.objects.order_by("?")
366
367 Note: ``order_by('?')`` queries may be expensive and slow, depending on the
368 database backend you're using.
369
370 To order by a field in a different model, use the same syntax as when you are
371 querying across model relations. That is, the name of the field, followed by a
372 double underscore (``__``), followed by the name of the field in the new model,
373 and so on for as many models as you want to join. For example::
374
375 Entry.objects.order_by("blog__name", "headline")
376
377 If you try to order by a field that is a relation to another model, Django will
378 use the default ordering on the related model, or order by the related model's
379 primary key if there is no :attr:`Meta.ordering
380 <django.db.models.Options.ordering>` specified. For example, since the ``Blog``
381 model has no default ordering specified::
382
383 Entry.objects.order_by("blog")
384
385 ...is identical to::
386
387 Entry.objects.order_by("blog__id")
388
389 If ``Blog`` had ``ordering = ['name']``, then the first queryset would be
390 identical to::
391
392 Entry.objects.order_by("blog__name")
393
394 You can also order by :doc:`query expressions </ref/models/expressions>` by
395 calling :meth:`~.Expression.asc` or :meth:`~.Expression.desc` on the
396 expression::
397
398 Entry.objects.order_by(Coalesce("summary", "headline").desc())
399
400 :meth:`~.Expression.asc` and :meth:`~.Expression.desc` have arguments
401 (``nulls_first`` and ``nulls_last``) that control how null values are sorted.
402
403 Be cautious when ordering by fields in related models if you are also using
404 :meth:`distinct()`. See the note in :meth:`distinct` for an explanation of how
405 related model ordering can change the expected results.
406
407 .. note::
408 It is permissible to specify a multi-valued field to order the results by
409 (for example, a :class:`~django.db.models.ManyToManyField` field, or the
410 reverse relation of a :class:`~django.db.models.ForeignKey` field).
411
412 Consider this case::
413
414 class Event(Model):
415 parent = models.ForeignKey(
416 "self",
417 on_delete=models.CASCADE,
418 related_name="children",
419 )
420 date = models.DateField()
421
422
423 Event.objects.order_by("children__date")
424
425 Here, there could potentially be multiple ordering data for each ``Event``;
426 each ``Event`` with multiple ``children`` will be returned multiple times
427 into the new ``QuerySet`` that ``order_by()`` creates. In other words,
428 using ``order_by()`` on the ``QuerySet`` could return more items than you
429 were working on to begin with - which is probably neither expected nor
430 useful.
431
432 Thus, take care when using multi-valued field to order the results. **If**
433 you can be sure that there will only be one ordering piece of data for each
434 of the items you're ordering, this approach should not present problems. If
435 not, make sure the results are what you expect.
436
437 There's no way to specify whether ordering should be case sensitive. With
438 respect to case-sensitivity, Django will order results however your database
439 backend normally orders them.
440
441 You can order by a field converted to lowercase with
442 :class:`~django.db.models.functions.Lower` which will achieve case-consistent
443 ordering::
444
445 Entry.objects.order_by(Lower("headline").desc())
446
447 If you don't want any ordering to be applied to a query, not even the default
448 ordering, call :meth:`order_by()` with no parameters.
449
450 You can tell if a query is ordered or not by checking the
451 :attr:`.QuerySet.ordered` attribute, which will be ``True`` if the
452 ``QuerySet`` has been ordered in any way.
453
454 Each ``order_by()`` call will clear any previous ordering. For example, this
455 query will be ordered by ``pub_date`` and not ``headline``::
456
457 Entry.objects.order_by("headline").order_by("pub_date")
458
459 .. warning::
460
461 Ordering is not a free operation. Each field you add to the ordering
462 incurs a cost to your database. Each foreign key you add will
463 implicitly include all of its default orderings as well.
464
465 If a query doesn't have an ordering specified, results are returned from
466 the database in an unspecified order. A particular ordering is guaranteed
467 only when ordering by a set of fields that uniquely identify each object in
468 the results. For example, if a ``name`` field isn't unique, ordering by it
469 won't guarantee objects with the same name always appear in the same order.
470
471 ``reverse()``
472 ~~~~~~~~~~~~~
473
474 .. method:: reverse()
475
476 Use the ``reverse()`` method to reverse the order in which a queryset's
477 elements are returned. Calling ``reverse()`` a second time restores the
478 ordering back to the normal direction.
479
480 To retrieve the "last" five items in a queryset, you could do this::
481
482 my_queryset.reverse()[:5]
483
484 Note that this is not quite the same as slicing from the end of a sequence in
485 Python. The above example will return the last item first, then the
486 penultimate item and so on. If we had a Python sequence and looked at
487 ``seq[-5:]``, we would see the fifth-last item first. Django doesn't support
488 that mode of access (slicing from the end), because it's not possible to do it
489 efficiently in SQL.
490
491 Also, note that ``reverse()`` should generally only be called on a ``QuerySet``
492 which has a defined ordering (e.g., when querying against a model which defines
493 a default ordering, or when using :meth:`order_by()`). If no such ordering is
494 defined for a given ``QuerySet``, calling ``reverse()`` on it has no real
495 effect (the ordering was undefined prior to calling ``reverse()``, and will
496 remain undefined afterward).
497
498 ``distinct()``
499 ~~~~~~~~~~~~~~
500
501 .. method:: distinct(*fields)
502
503 Returns a new ``QuerySet`` that uses ``SELECT DISTINCT`` in its SQL query. This
504 eliminates duplicate rows from the query results.
505
506 By default, a ``QuerySet`` will not eliminate duplicate rows. In practice, this
507 is rarely a problem, because simple queries such as ``Blog.objects.all()``
508 don't introduce the possibility of duplicate result rows. However, if your
509 query spans multiple tables, it's possible to get duplicate results when a
510 ``QuerySet`` is evaluated. That's when you'd use ``distinct()``.
511
512 .. note::
513 Any fields used in an :meth:`order_by` call are included in the SQL
514 ``SELECT`` columns. This can sometimes lead to unexpected results when used
515 in conjunction with ``distinct()``. If you order by fields from a related
516 model, those fields will be added to the selected columns and they may make
517 otherwise duplicate rows appear to be distinct. Since the extra columns
518 don't appear in the returned results (they are only there to support
519 ordering), it sometimes looks like non-distinct results are being returned.
520
521 Similarly, if you use a :meth:`values()` query to restrict the columns
522 selected, the columns used in any :meth:`order_by()` (or default model
523 ordering) will still be involved and may affect uniqueness of the results.
524
525 The moral here is that if you are using ``distinct()`` be careful about
526 ordering by related models. Similarly, when using ``distinct()`` and
527 :meth:`values()` together, be careful when ordering by fields not in the
528 :meth:`values()` call.
529
530 On PostgreSQL only, you can pass positional arguments (``*fields``) in order to
531 specify the names of fields to which the ``DISTINCT`` should apply. This
532 translates to a ``SELECT DISTINCT ON`` SQL query. Here's the difference. For a
533 normal ``distinct()`` call, the database compares *each* field in each row when
534 determining which rows are distinct. For a ``distinct()`` call with specified
535 field names, the database will only compare the specified field names.
536
537 .. note::
538 When you specify field names, you *must* provide an ``order_by()`` in the
539 ``QuerySet``, and the fields in ``order_by()`` must start with the fields in
540 ``distinct()``, in the same order.
541
542 For example, ``SELECT DISTINCT ON (a)`` gives you the first row for each
543 value in column ``a``. If you don't specify an order, you'll get some
544 arbitrary row.
545
546 Examples (those after the first will only work on PostgreSQL):
547
548 .. code-block:: pycon
549
550 >>> Author.objects.distinct()
551 [...]
552
553 >>> Entry.objects.order_by("pub_date").distinct("pub_date")
554 [...]
555
556 >>> Entry.objects.order_by("blog").distinct("blog")
557 [...]
558
559 >>> Entry.objects.order_by("author", "pub_date").distinct("author", "pub_date")
560 [...]
561
562 >>> Entry.objects.order_by("blog__name", "mod_date").distinct("blog__name", "mod_date")
563 [...]
564
565 >>> Entry.objects.order_by("author", "pub_date").distinct("author")
566 [...]
567
568 .. note::
569 Keep in mind that :meth:`order_by` uses any default related model ordering
570 that has been defined. You might have to explicitly order by the relation
571 ``_id`` or referenced field to make sure the ``DISTINCT ON`` expressions
572 match those at the beginning of the ``ORDER BY`` clause. For example, if
573 the ``Blog`` model defined an :attr:`~django.db.models.Options.ordering` by
574 ``name``::
575
576 Entry.objects.order_by("blog").distinct("blog")
577
578 ...wouldn't work because the query would be ordered by ``blog__name`` thus
579 mismatching the ``DISTINCT ON`` expression. You'd have to explicitly order
580 by the relation ``_id`` field (``blog_id`` in this case) or the referenced
581 one (``blog__pk``) to make sure both expressions match.
582
583 ``values()``
584 ~~~~~~~~~~~~
585
586 .. method:: values(*fields, **expressions)
587
588 Returns a ``QuerySet`` that returns dictionaries, rather than model instances,
589 when used as an iterable.
590
591 Each of those dictionaries represents an object, with the keys corresponding to
592 the attribute names of model objects.
593
594 This example compares the dictionaries of ``values()`` with the normal model
595 objects:
596
597 .. code-block:: pycon
598
599 # This list contains a Blog object.
600 >>> Blog.objects.filter(name__startswith="Beatles")
601 <QuerySet [<Blog: Beatles Blog>]>
602
603 # This list contains a dictionary.
604 >>> Blog.objects.filter(name__startswith="Beatles").values()
605 <QuerySet [{'id': 1, 'name': 'Beatles Blog', 'tagline': 'All the latest Beatles news.'}]>
606
607 The ``values()`` method takes optional positional arguments, ``*fields``, which
608 specify field names to which the ``SELECT`` should be limited. If you specify
609 the fields, each dictionary will contain only the field keys/values for the
610 fields you specify. If you don't specify the fields, each dictionary will
611 contain a key and value for every field in the database table.
612
613 Example:
614
615 .. code-block:: pycon
616
617 >>> Blog.objects.values()
618 <QuerySet [{'id': 1, 'name': 'Beatles Blog', 'tagline': 'All the latest Beatles news.'}]>
619 >>> Blog.objects.values("id", "name")
620 <QuerySet [{'id': 1, 'name': 'Beatles Blog'}]>
621
622 The ``values()`` method also takes optional keyword arguments,
623 ``**expressions``, which are passed through to :meth:`annotate`:
624
625 .. code-block:: pycon
626
627 >>> from django.db.models.functions import Lower
628 >>> Blog.objects.values(lower_name=Lower("name"))
629 <QuerySet [{'lower_name': 'beatles blog'}]>
630
631 You can use built-in and :doc:`custom lookups </howto/custom-lookups>` in
632 ordering. For example:
633
634 .. code-block:: pycon
635
636 >>> from django.db.models import CharField
637 >>> from django.db.models.functions import Lower
638 >>> CharField.register_lookup(Lower)
639 >>> Blog.objects.values("name__lower")
640 <QuerySet [{'name__lower': 'beatles blog'}]>
641
642 An aggregate within a ``values()`` clause is applied before other arguments
643 within the same ``values()`` clause. If you need to group by another value,
644 add it to an earlier ``values()`` clause instead. For example:
645
646 .. code-block:: pycon
647
648 >>> from django.db.models import Count
649 >>> Blog.objects.values("entry__authors", entries=Count("entry"))
650 <QuerySet [{'entry__authors': 1, 'entries': 20}, {'entry__authors': 1, 'entries': 13}]>
651 >>> Blog.objects.values("entry__authors").annotate(entries=Count("entry"))
652 <QuerySet [{'entry__authors': 1, 'entries': 33}]>
653
654 A few subtleties that are worth mentioning:
655
656 * If you have a field called ``foo`` that is a
657 :class:`~django.db.models.ForeignKey`, the default ``values()`` call
658 will return a dictionary key called ``foo_id``, since this is the name
659 of the hidden model attribute that stores the actual value (the ``foo``
660 attribute refers to the related model). When you are calling
661 ``values()`` and passing in field names, you can pass in either ``foo``
662 or ``foo_id`` and you will get back the same thing (the dictionary key
663 will match the field name you passed in).
664
665 For example:
666
667 .. code-block:: pycon
668
669 >>> Entry.objects.values()
670 <QuerySet [{'blog_id': 1, 'headline': 'First Entry', ...}, ...]>
671
672 >>> Entry.objects.values("blog")
673 <QuerySet [{'blog': 1}, ...]>
674
675 >>> Entry.objects.values("blog_id")
676 <QuerySet [{'blog_id': 1}, ...]>
677
678 * When using ``values()`` together with :meth:`distinct()`, be aware that
679 ordering can affect the results. See the note in :meth:`distinct` for
680 details.
681
682 * If you use a ``values()`` clause after an :meth:`extra()` call,
683 any fields defined by a ``select`` argument in the :meth:`extra()` must
684 be explicitly included in the ``values()`` call. Any :meth:`extra()` call
685 made after a ``values()`` call will have its extra selected fields
686 ignored.
687
688 * Calling :meth:`only()` and :meth:`defer()` after ``values()`` doesn't make
689 sense, so doing so will raise a ``TypeError``.
690
691 * Combining transforms and aggregates requires the use of two :meth:`annotate`
692 calls, either explicitly or as keyword arguments to :meth:`values`. As above,
693 if the transform has been registered on the relevant field type the first
694 :meth:`annotate` can be omitted, thus the following examples are equivalent:
695
696 .. code-block:: pycon
697
698 >>> from django.db.models import CharField, Count
699 >>> from django.db.models.functions import Lower
700 >>> CharField.register_lookup(Lower)
701 >>> Blog.objects.values("entry__authors__name__lower").annotate(entries=Count("entry"))
702 <QuerySet [{'entry__authors__name__lower': 'test author', 'entries': 33}]>
703 >>> Blog.objects.values(entry__authors__name__lower=Lower("entry__authors__name")).annotate(
704 ... entries=Count("entry")
705 ... )
706 <QuerySet [{'entry__authors__name__lower': 'test author', 'entries': 33}]>
707 >>> Blog.objects.annotate(entry__authors__name__lower=Lower("entry__authors__name")).values(
708 ... "entry__authors__name__lower"
709 ... ).annotate(entries=Count("entry"))
710 <QuerySet [{'entry__authors__name__lower': 'test author', 'entries': 33}]>
711
712 It is useful when you know you're only going to need values from a small number
713 of the available fields and you won't need the functionality of a model
714 instance object. It's more efficient to select only the fields you need to use.
715
716 Finally, note that you can call ``filter()``, ``order_by()``, etc. after the
717 ``values()`` call, that means that these two calls are identical::
718
719 Blog.objects.values().order_by("id")
720 Blog.objects.order_by("id").values()
721
722 The people who made Django prefer to put all the SQL-affecting methods first,
723 followed (optionally) by any output-affecting methods (such as ``values()``),
724 but it doesn't really matter. This is your chance to really flaunt your
725 individualism.
726
727 You can also refer to fields on related models with reverse relations through
728 ``OneToOneField``, ``ForeignKey`` and ``ManyToManyField`` attributes:
729
730 .. code-block:: pycon
731
732 >>> Blog.objects.values("name", "entry__headline")
733 <QuerySet [{'name': 'My blog', 'entry__headline': 'An entry'},
734 {'name': 'My blog', 'entry__headline': 'Another entry'}, ...]>
735
736 .. warning::
737
738 Because :class:`~django.db.models.ManyToManyField` attributes and reverse
739 relations can have multiple related rows, including these can have a
740 multiplier effect on the size of your result set. This will be especially
741 pronounced if you include multiple such fields in your ``values()`` query,
742 in which case all possible combinations will be returned.
743
744 .. admonition:: Special values for ``JSONField`` on SQLite
745
746 Due to the way the ``JSON_EXTRACT`` and ``JSON_TYPE`` SQL functions are
747 implemented on SQLite, and lack of the ``BOOLEAN`` data type,
748 ``values()`` will return ``True``, ``False``, and ``None`` instead of
749 ``"true"``, ``"false"``, and ``"null"`` strings for
750 :class:`~django.db.models.JSONField` key transforms.
751
752 .. versionchanged:: 5.2
753
754 The ``SELECT`` clause generated when using ``values()`` was updated to
755 respect the order of the specified ``*fields`` and ``**expressions``.
756
757 ``values_list()``
758 ~~~~~~~~~~~~~~~~~
759
760 .. method:: values_list(*fields, flat=False, named=False)
761
762 This is similar to ``values()`` except that instead of returning dictionaries,
763 it returns tuples when iterated over. Each tuple contains the value from the
764 respective field or expression passed into the ``values_list()`` call — so the
765 first item is the first field, etc. For example:
766
767 .. code-block:: pycon
768
769 >>> Entry.objects.values_list("id", "headline")
770 <QuerySet [(1, 'First entry'), ...]>
771 >>> from django.db.models.functions import Lower
772 >>> Entry.objects.values_list("id", Lower("headline"))
773 <QuerySet [(1, 'first entry'), ...]>
774
775 If you only pass in a single field, you can also pass in the ``flat``
776 parameter. If ``True``, this will mean the returned results are single values,
777 rather than 1-tuples. An example should make the difference clearer:
778
779 .. code-block:: pycon
780
781 >>> Entry.objects.values_list("id").order_by("id")
782 <QuerySet[(1,), (2,), (3,), ...]>
783
784 >>> Entry.objects.values_list("id", flat=True).order_by("id")
785 <QuerySet [1, 2, 3, ...]>
786
787 It is an error to pass in ``flat`` when there is more than one field.
788
789 You can pass ``named=True`` to get results as a
790 :func:`~python:collections.namedtuple`:
791
792 .. code-block:: pycon
793
794 >>> Entry.objects.values_list("id", "headline", named=True)
795 <QuerySet [Row(id=1, headline='First entry'), ...]>
796
797 Using a named tuple may make use of the results more readable, at the expense
798 of a small performance penalty for transforming the results into a named tuple.
799
800 If you don't pass any values to ``values_list()``, it will return all the
801 fields in the model, in the order they were declared.
802
803 A common need is to get a specific field value of a certain model instance. To
804 achieve that, use ``values_list()`` followed by a ``get()`` call:
805
806 .. code-block:: pycon
807
808 >>> Entry.objects.values_list("headline", flat=True).get(pk=1)
809 'First entry'
810
811 ``values()`` and ``values_list()`` are both intended as optimizations for a
812 specific use case: retrieving a subset of data without the overhead of creating
813 a model instance. This metaphor falls apart when dealing with many-to-many and
814 other multivalued relations (such as the one-to-many relation of a reverse
815 foreign key) because the "one row, one object" assumption doesn't hold.
816
817 For example, notice the behavior when querying across a
818 :class:`~django.db.models.ManyToManyField`:
819
820 .. code-block:: pycon
821
822 >>> Author.objects.values_list("name", "entry__headline")
823 <QuerySet [('Noam Chomsky', 'Impressions of Gaza'),
824 ('George Orwell', 'Why Socialists Do Not Believe in Fun'),
825 ('George Orwell', 'In Defence of English Cooking'),
826 ('Don Quixote', None)]>
827
828 Authors with multiple entries appear multiple times and authors without any
829 entries have ``None`` for the entry headline.
830
831 Similarly, when querying a reverse foreign key, ``None`` appears for entries
832 not having any author:
833
834 .. code-block:: pycon
835
836 >>> Entry.objects.values_list("authors")
837 <QuerySet [('Noam Chomsky',), ('George Orwell',), (None,)]>
838
839 .. admonition:: Special values for ``JSONField`` on SQLite
840
841 Due to the way the ``JSON_EXTRACT`` and ``JSON_TYPE`` SQL functions are
842 implemented on SQLite, and lack of the ``BOOLEAN`` data type,
843 ``values_list()`` will return ``True``, ``False``, and ``None`` instead of
844 ``"true"``, ``"false"``, and ``"null"`` strings for
845 :class:`~django.db.models.JSONField` key transforms.
846
847 .. versionchanged:: 5.2
848
849 The ``SELECT`` clause generated when using ``values_list()`` was updated to
850 respect the order of the specified ``*fields``.
851
852 ``dates()``
853 ~~~~~~~~~~~
854
855 .. method:: dates(field, kind, order='ASC')
856
857 Returns a ``QuerySet`` that evaluates to a list of :class:`datetime.date`
858 objects representing all available dates of a particular kind within the
859 contents of the ``QuerySet``.
860
861 ``field`` should be the name of a ``DateField`` of your model.
862 ``kind`` should be either ``"year"``, ``"month"``, ``"week"``, or ``"day"``.
863 Each :class:`datetime.date` object in the result list is "truncated" to the
864 given ``type``.
865
866 * ``"year"`` returns a list of all distinct year values for the field.
867 * ``"month"`` returns a list of all distinct year/month values for the
868 field.
869 * ``"week"`` returns a list of all distinct year/week values for the field. All
870 dates will be a Monday.
871 * ``"day"`` returns a list of all distinct year/month/day values for the
872 field.
873
874 ``order``, which defaults to ``'ASC'``, should be either ``'ASC'`` or
875 ``'DESC'``. This specifies how to order the results.
876
877 Examples:
878
879 .. code-block:: pycon
880
881 >>> Entry.objects.dates("pub_date", "year")
882 [datetime.date(2005, 1, 1)]
883 >>> Entry.objects.dates("pub_date", "month")
884 [datetime.date(2005, 2, 1), datetime.date(2005, 3, 1)]
885 >>> Entry.objects.dates("pub_date", "week")
886 [datetime.date(2005, 2, 14), datetime.date(2005, 3, 14)]
887 >>> Entry.objects.dates("pub_date", "day")
888 [datetime.date(2005, 2, 20), datetime.date(2005, 3, 20)]
889 >>> Entry.objects.dates("pub_date", "day", order="DESC")
890 [datetime.date(2005, 3, 20), datetime.date(2005, 2, 20)]
891 >>> Entry.objects.filter(headline__contains="Lennon").dates("pub_date", "day")
892 [datetime.date(2005, 3, 20)]
893
894 ``datetimes()``
895 ~~~~~~~~~~~~~~~
896
897 .. method:: datetimes(field_name, kind, order='ASC', tzinfo=None)
898
899 Returns a ``QuerySet`` that evaluates to a list of :class:`datetime.datetime`
900 objects representing all available dates of a particular kind within the
901 contents of the ``QuerySet``.
902
903 ``field_name`` should be the name of a ``DateTimeField`` of your model.
904
905 ``kind`` should be either ``"year"``, ``"month"``, ``"week"``, ``"day"``,
906 ``"hour"``, ``"minute"``, or ``"second"``. Each :class:`datetime.datetime`
907 object in the result list is "truncated" to the given ``type``.
908
909 ``order``, which defaults to ``'ASC'``, should be either ``'ASC'`` or
910 ``'DESC'``. This specifies how to order the results.
911
912 ``tzinfo`` defines the time zone to which datetimes are converted prior to
913 truncation. Indeed, a given datetime has different representations depending
914 on the time zone in use. This parameter must be a :class:`datetime.tzinfo`
915 object. If it's ``None``, Django uses the :ref:`current time zone
916 <default-current-time-zone>`. It has no effect when :setting:`USE_TZ` is
917 ``False``.
918
919 .. _database-time-zone-definitions:
920
921 .. note::
922
923 This function performs time zone conversions directly in the database.
924 As a consequence, your database must be able to interpret the value of
925 ``tzinfo.tzname(None)``. This translates into the following requirements:
926
927 - SQLite: no requirements. Conversions are performed in Python.
928 - PostgreSQL: no requirements (see `Time Zones`_).
929 - Oracle: no requirements (see `Choosing a Time Zone File`_).
930 - MySQL: load the time zone tables with `mysql_tzinfo_to_sql`_.
931
932 .. _Time Zones: https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES
933 .. _Choosing a Time Zone File: https://docs.oracle.com/en/database/oracle/
934 oracle-database/18/nlspg/datetime-data-types-and-time-zone-support.html
935 #GUID-805AB986-DE12-4FEA-AF56-5AABCD2132DF
936 .. _mysql_tzinfo_to_sql: https://dev.mysql.com/doc/refman/en/mysql-tzinfo-to-sql.html
937
938 ``none()``
939 ~~~~~~~~~~
940
941 .. method:: none()
942
943 Calling ``none()`` will create a queryset that never returns any objects and no
944 query will be executed when accessing the results. A ``qs.none()`` queryset
945 is an instance of ``EmptyQuerySet``.
946
947 Examples:
948
949 .. code-block:: pycon
950
951 >>> Entry.objects.none()
952 <QuerySet []>
953 >>> from django.db.models.query import EmptyQuerySet
954 >>> isinstance(Entry.objects.none(), EmptyQuerySet)
955 True
956
957 ``all()``
958 ~~~~~~~~~
959
960 .. method:: all()
961
962 Returns a *copy* of the current ``QuerySet`` (or ``QuerySet`` subclass). This
963 can be useful in situations where you might want to pass in either a model
964 manager or a ``QuerySet`` and do further filtering on the result. After calling
965 ``all()`` on either object, you'll definitely have a ``QuerySet`` to work with.
966
967 When a ``QuerySet`` is :ref:`evaluated <when-querysets-are-evaluated>`, it
968 typically caches its results. If the data in the database might have changed
969 since a ``QuerySet`` was evaluated, you can get updated results for the same
970 query by calling ``all()`` on a previously evaluated ``QuerySet``.
971
972 ``union()``
973 ~~~~~~~~~~~
974
975 .. method:: union(*other_qs, all=False)
976
977 Uses SQL's ``UNION`` operator to combine the results of two or more
978 ``QuerySet``\s. For example:
979
980 >>> qs1.union(qs2, qs3)
981
982 The ``UNION`` operator selects only distinct values by default. To allow
983 duplicate values, use the ``all=True`` argument.
984
985 ``union()``, ``intersection()``, and ``difference()`` return model instances
986 of the type of the first ``QuerySet`` even if the arguments are ``QuerySet``\s
987 of other models. Passing different models works as long as the ``SELECT`` list
988 is the same in all ``QuerySet``\s (at least the types, the names don't matter
989 as long as the types are in the same order). In such cases, you must use the
990 column names from the first ``QuerySet`` in ``QuerySet`` methods applied to the
991 resulting ``QuerySet``. For example:
992
993 .. code-block:: pycon
994
995 >>> qs1 = Author.objects.values_list("name")
996 >>> qs2 = Entry.objects.values_list("headline")
997 >>> qs1.union(qs2).order_by("name")
998
999 In addition, only ``LIMIT``, ``OFFSET``, ``COUNT(*)``, ``ORDER BY``, and
1000 specifying columns (i.e. slicing, :meth:`count`, :meth:`exists`,
1001 :meth:`order_by`, and :meth:`values()`/:meth:`values_list()`) are allowed
1002 on the resulting ``QuerySet``. Further, databases place restrictions on
1003 what operations are allowed in the combined queries. For example, most
1004 databases don't allow ``LIMIT`` or ``OFFSET`` in the combined queries.
1005
1006 ``intersection()``
1007 ~~~~~~~~~~~~~~~~~~
1008
1009 .. method:: intersection(*other_qs)
1010
1011 Uses SQL's ``INTERSECT`` operator to return the shared elements of two or more
1012 ``QuerySet``\s. For example:
1013
1014 .. code-block:: pycon
1015
1016 >>> qs1.intersection(qs2, qs3)
1017
1018 See :meth:`union` for some restrictions.
1019
1020 ``difference()``
1021 ~~~~~~~~~~~~~~~~
1022
1023 .. method:: difference(*other_qs)
1024
1025 Uses SQL's ``EXCEPT`` operator to keep only elements present in the
1026 ``QuerySet`` but not in some other ``QuerySet``\s. For example:
1027
1028 .. code-block:: pycon
1029
1030 >>> qs1.difference(qs2, qs3)
1031
1032 See :meth:`union` for some restrictions.
1033
1034 ``select_related()``
1035 ~~~~~~~~~~~~~~~~~~~~
1036
1037 .. method:: select_related(*fields)
1038
1039 Returns a ``QuerySet`` that will "follow" foreign-key relationships, selecting
1040 additional related-object data when it executes its query. This is a
1041 performance booster which results in a single more complex query but means
1042 later use of foreign-key relationships won't require database queries.
1043
1044 The following examples illustrate the difference between plain lookups and
1045 ``select_related()`` lookups. Here's standard lookup::
1046
1047 # Hits the database.
1048 e = Entry.objects.get(id=5)
1049
1050 # Hits the database again to get the related Blog object.
1051 b = e.blog
1052
1053 And here's ``select_related`` lookup::
1054
1055 # Hits the database.
1056 e = Entry.objects.select_related("blog").get(id=5)
1057
1058 # Doesn't hit the database, because e.blog has been prepopulated
1059 # in the previous query.
1060 b = e.blog
1061
1062 You can use ``select_related()`` with any queryset of objects::
1063
1064 from django.utils import timezone
1065
1066 # Find all the blogs with entries scheduled to be published in the future.
1067 blogs = set()
1068
1069 for e in Entry.objects.filter(pub_date__gt=timezone.now()).select_related("blog"):
1070 # Without select_related(), this would make a database query for each
1071 # loop iteration in order to fetch the related blog for each entry.
1072 blogs.add(e.blog)
1073
1074 The order of ``filter()`` and ``select_related()`` chaining isn't important.
1075 These querysets are equivalent::
1076
1077 Entry.objects.filter(pub_date__gt=timezone.now()).select_related("blog")
1078 Entry.objects.select_related("blog").filter(pub_date__gt=timezone.now())
1079
1080 You can follow foreign keys in a similar way to querying them. If you have the
1081 following models::
1082
1083 from django.db import models
1084
1085
1086 class City(models.Model):
1087 # ...
1088 pass
1089
1090
1091 class Person(models.Model):
1092 # ...
1093 hometown = models.ForeignKey(
1094 City,
1095 on_delete=models.SET_NULL,
1096 blank=True,
1097 null=True,
1098 )
1099
1100
1101 class Book(models.Model):
1102 # ...
1103 author = models.ForeignKey(Person, on_delete=models.CASCADE)
1104
1105 ... then a call to ``Book.objects.select_related('author__hometown').get(id=4)``
1106 will cache the related ``Person`` *and* the related ``City``::
1107
1108 # Hits the database with joins to the author and hometown tables.
1109 b = Book.objects.select_related("author__hometown").get(id=4)
1110 p = b.author # Doesn't hit the database.
1111 c = p.hometown # Doesn't hit the database.
1112
1113 # Without select_related()...
1114 b = Book.objects.get(id=4) # Hits the database.
1115 p = b.author # Hits the database.
1116 c = p.hometown # Hits the database.
1117
1118 You can refer to any :class:`~django.db.models.ForeignKey` or
1119 :class:`~django.db.models.OneToOneField` relation in the list of fields
1120 passed to ``select_related()``.
1121
1122 You can also refer to the reverse direction of a
1123 :class:`~django.db.models.OneToOneField` in the list of fields passed to
1124 ``select_related`` — that is, you can traverse a
1125 :class:`~django.db.models.OneToOneField` back to the object on which the field
1126 is defined. Instead of specifying the field name, use the :attr:`related_name
1127 <django.db.models.ForeignKey.related_name>` for the field on the related object.
1128
1129 There may be some situations where you wish to call ``select_related()`` with a
1130 lot of related objects, or where you don't know all of the relations. In these
1131 cases it is possible to call ``select_related()`` with no arguments. This will
1132 follow all non-null foreign keys it can find - nullable foreign keys must be
1133 specified. This is not recommended in most cases as it is likely to make the
1134 underlying query more complex, and return more data, than is actually needed.
1135
1136 If you need to clear the list of related fields added by past calls of
1137 ``select_related`` on a ``QuerySet``, you can pass ``None`` as a parameter:
1138
1139 .. code-block:: pycon
1140
1141 >>> without_relations = queryset.select_related(None)
1142
1143 Chaining ``select_related`` calls works in a similar way to other methods -
1144 that is that ``select_related('foo', 'bar')`` is equivalent to
1145 ``select_related('foo').select_related('bar')``.
1146
1147 ``prefetch_related()``
1148 ~~~~~~~~~~~~~~~~~~~~~~
1149
1150 .. method:: prefetch_related(*lookups)
1151
1152 Returns a ``QuerySet`` that will automatically retrieve, in a single batch,
1153 related objects for each of the specified lookups.
1154
1155 This has a similar purpose to ``select_related``, in that both are designed to
1156 stop the deluge of database queries that is caused by accessing related objects,
1157 but the strategy is quite different.
1158
1159 ``select_related`` works by creating an SQL join and including the fields of the
1160 related object in the ``SELECT`` statement. For this reason, ``select_related``
1161 gets the related objects in the same database query. However, to avoid the much
1162 larger result set that would result from joining across a 'many' relationship,
1163 ``select_related`` is limited to single-valued relationships - foreign key and
1164 one-to-one.
1165
1166 ``prefetch_related``, on the other hand, does a separate lookup for each
1167 relationship, and does the 'joining' in Python. This allows it to prefetch
1168 many-to-many, many-to-one, and
1169 :class:`~django.contrib.contenttypes.fields.GenericRelation` objects which
1170 cannot be done using ``select_related``, in addition to the foreign key and
1171 one-to-one relationships that are supported by ``select_related``. It also
1172 supports prefetching of
1173 :class:`~django.contrib.contenttypes.fields.GenericForeignKey`, however, the
1174 queryset for each ``ContentType`` must be provided in the ``querysets``
1175 parameter of :class:`~django.contrib.contenttypes.prefetch.GenericPrefetch`.
1176
1177 For example, suppose you have these models::
1178
1179 from django.db import models
1180
1181
1182 class Topping(models.Model):
1183 name = models.CharField(max_length=30)
1184
1185
1186 class Pizza(models.Model):
1187 name = models.CharField(max_length=50)
1188 toppings = models.ManyToManyField(Topping)
1189
1190 def __str__(self):
1191 return "%s (%s)" % (
1192 self.name,
1193 ", ".join(topping.name for topping in self.toppings.all()),
1194 )
1195
1196 and run:
1197
1198 .. code-block:: pycon
1199
1200 >>> Pizza.objects.all()
1201 ["Hawaiian (ham, pineapple)", "Seafood (prawns, smoked salmon)"...
1202
1203 The problem with this is that every time ``Pizza.__str__()`` asks for
1204 ``self.toppings.all()`` it has to query the database, so
1205 ``Pizza.objects.all()`` will run a query on the Toppings table for **every**
1206 item in the Pizza ``QuerySet``.
1207
1208 We can reduce to just two queries using ``prefetch_related``:
1209
1210 .. code-block:: pycon
1211
1212 >>> Pizza.objects.prefetch_related("toppings")
1213
1214 This implies a ``self.toppings.all()`` for each ``Pizza``; now each time
1215 ``self.toppings.all()`` is called, instead of having to go to the database for
1216 the items, it will find them in a prefetched ``QuerySet`` cache that was
1217 populated in a single query.
1218
1219 That is, all the relevant toppings will have been fetched in a single query,
1220 and used to make ``QuerySet`` instances that have a pre-filled cache of the
1221 relevant results; these are then used in the ``self.toppings.all()`` calls.
1222
1223 The additional queries in ``prefetch_related()`` are executed after the
1224 ``QuerySet`` has begun to be evaluated and the primary query has been executed.
1225
1226 Note that there is no mechanism to prevent another database query from altering
1227 the items in between the execution of the primary query and the additional
1228 queries, which could produce an inconsistent result. For example, if a
1229 ``Pizza`` is deleted after the primary query has executed, its toppings will
1230 not be returned in the additional query, and it will seem like the pizza has no
1231 toppings:
1232
1233 .. code-block:: pycon
1234
1235 >>> Pizza.objects.prefetch_related("toppings")
1236 # "Hawaiian" Pizza was deleted in another shell.
1237 <QuerySet [<Pizza: Hawaiian ()>, <Pizza: Seafood (prawns, smoked salmon)>]>
1238
1239 If you have an iterable of model instances, you can prefetch related attributes
1240 on those instances using the :func:`~django.db.models.prefetch_related_objects`
1241 function.
1242
1243 Note that the result cache of the primary ``QuerySet`` and all specified related
1244 objects will then be fully loaded into memory. This changes the typical
1245 behavior of a ``QuerySet``, which normally tries to avoid loading all objects
1246 into memory before they are needed, even after a query has been executed in the
1247 database.
1248
1249 .. note::
1250
1251 Remember that, as always with ``QuerySet`` objects, any subsequent chained
1252 methods which imply a different database query will ignore previously
1253 cached results, and retrieve data using a fresh database query. So, if you
1254 write the following:
1255
1256 .. code-block:: pycon
1257
1258 >>> pizzas = Pizza.objects.prefetch_related("toppings")
1259 >>> [list(pizza.toppings.filter(spicy=True)) for pizza in pizzas]
1260
1261 ...then the fact that ``pizza.toppings.all()`` has been prefetched will not
1262 help you. The ``prefetch_related('toppings')`` implied
1263 ``pizza.toppings.all()``, but ``pizza.toppings.filter()`` is a new and
1264 different query. The prefetched cache can't help here; in fact it hurts
1265 performance, since you have done a database query that you haven't used. So
1266 use this feature with caution!
1267
1268 Also, if you call the database-altering methods
1269 :meth:`~django.db.models.fields.related.RelatedManager.add`,
1270 :meth:`~django.db.models.fields.related.RelatedManager.create`,
1271 :meth:`~django.db.models.fields.related.RelatedManager.remove`,
1272 :meth:`~django.db.models.fields.related.RelatedManager.clear` or
1273 :meth:`~django.db.models.fields.related.RelatedManager.set`, on
1274 :class:`related managers<django.db.models.fields.related.RelatedManager>`,
1275 any prefetched cache for the relation will be cleared.
1276
1277 You can also use the normal join syntax to do related fields of related
1278 fields. Suppose we have an additional model to the example above::
1279
1280 class Restaurant(models.Model):
1281 pizzas = models.ManyToManyField(Pizza, related_name="restaurants")
1282 best_pizza = models.ForeignKey(
1283 Pizza, related_name="championed_by", on_delete=models.CASCADE
1284 )
1285
1286 The following are all legal:
1287
1288 .. code-block:: pycon
1289
1290 >>> Restaurant.objects.prefetch_related("pizzas__toppings")
1291
1292 This will prefetch all pizzas belonging to restaurants, and all toppings
1293 belonging to those pizzas. This will result in a total of 3 database queries -
1294 one for the restaurants, one for the pizzas, and one for the toppings.
1295
1296 .. code-block:: pycon
1297
1298 >>> Restaurant.objects.prefetch_related("best_pizza__toppings")
1299
1300 This will fetch the best pizza and all the toppings for the best pizza for each
1301 restaurant. This will be done in 3 database queries - one for the restaurants,
1302 one for the 'best pizzas', and one for the toppings.
1303
1304 The ``best_pizza`` relationship could also be fetched using ``select_related``
1305 to reduce the query count to 2:
1306
1307 .. code-block:: pycon
1308
1309 >>> Restaurant.objects.select_related("best_pizza").prefetch_related("best_pizza__toppings")
1310
1311 Since the prefetch is executed after the main query (which includes the joins
1312 needed by ``select_related``), it is able to detect that the ``best_pizza``
1313 objects have already been fetched, and it will skip fetching them again.
1314
1315 Chaining ``prefetch_related`` calls will accumulate the lookups that are
1316 prefetched. To clear any ``prefetch_related`` behavior, pass ``None`` as a
1317 parameter:
1318
1319 .. code-block:: pycon
1320
1321 >>> non_prefetched = qs.prefetch_related(None)
1322
1323 One difference to note when using ``prefetch_related`` is that objects created
1324 by a query can be shared between the different objects that they are related to
1325 i.e. a single Python model instance can appear at more than one point in the
1326 tree of objects that are returned. This will normally happen with foreign key
1327 relationships. Typically this behavior will not be a problem, and will in fact
1328 save both memory and CPU time.
1329
1330 While ``prefetch_related`` supports prefetching ``GenericForeignKey``
1331 relationships, the number of queries will depend on the data. Since a
1332 ``GenericForeignKey`` can reference data in multiple tables, one query per table
1333 referenced is needed, rather than one query for all the items. There could be
1334 additional queries on the ``ContentType`` table if the relevant rows have not
1335 already been fetched.
1336
1337 ``prefetch_related`` in most cases will be implemented using an SQL query that
1338 uses the 'IN' operator. This means that for a large ``QuerySet`` a large 'IN' clause
1339 could be generated, which, depending on the database, might have performance
1340 problems of its own when it comes to parsing or executing the SQL query. Always
1341 profile for your use case!
1342
1343 If you use ``iterator()`` to run the query, ``prefetch_related()`` calls will
1344 only be observed if a value for ``chunk_size`` is provided.
1345
1346 You can use the :class:`~django.db.models.Prefetch` object to further control
1347 the prefetch operation.
1348
1349 In its simplest form ``Prefetch`` is equivalent to the traditional string based
1350 lookups:
1351
1352 .. code-block:: pycon
1353
1354 >>> from django.db.models import Prefetch
1355 >>> Restaurant.objects.prefetch_related(Prefetch("pizzas__toppings"))
1356
1357 You can provide a custom queryset with the optional ``queryset`` argument.
1358 This can be used to change the default ordering of the queryset:
1359
1360 .. code-block:: pycon
1361
1362 >>> Restaurant.objects.prefetch_related(
1363 ... Prefetch("pizzas__toppings", queryset=Toppings.objects.order_by("name"))
1364 ... )
1365
1366 Or to call :meth:`~django.db.models.query.QuerySet.select_related()` when
1367 applicable to reduce the number of queries even further:
1368
1369 .. code-block:: pycon
1370
1371 >>> Pizza.objects.prefetch_related(
1372 ... Prefetch("restaurants", queryset=Restaurant.objects.select_related("best_pizza"))
1373 ... )
1374
1375 You can also assign the prefetched result to a custom attribute with the optional
1376 ``to_attr`` argument. The result will be stored directly in a list.
1377
1378 This allows prefetching the same relation multiple times with a different
1379 ``QuerySet``; for instance:
1380
1381 .. code-block:: pycon
1382
1383 >>> vegetarian_pizzas = Pizza.objects.filter(vegetarian=True)
1384 >>> Restaurant.objects.prefetch_related(
1385 ... Prefetch("pizzas", to_attr="menu"),
1386 ... Prefetch("pizzas", queryset=vegetarian_pizzas, to_attr="vegetarian_menu"),
1387 ... )
1388
1389 Lookups created with custom ``to_attr`` can still be traversed as usual by other
1390 lookups:
1391
1392 .. code-block:: pycon
1393
1394 >>> vegetarian_pizzas = Pizza.objects.filter(vegetarian=True)
1395 >>> Restaurant.objects.prefetch_related(
1396 ... Prefetch("pizzas", queryset=vegetarian_pizzas, to_attr="vegetarian_menu"),
1397 ... "vegetarian_menu__toppings",
1398 ... )
1399
1400 Using ``to_attr`` is recommended when filtering down the prefetch result as it is
1401 less ambiguous than storing a filtered result in the related manager's cache:
1402
1403 .. code-block:: pycon
1404
1405 >>> queryset = Pizza.objects.filter(vegetarian=True)
1406 >>>
1407 >>> # Recommended:
1408 >>> restaurants = Restaurant.objects.prefetch_related(
1409 ... Prefetch("pizzas", queryset=queryset, to_attr="vegetarian_pizzas")
1410 ... )
1411 >>> vegetarian_pizzas = restaurants[0].vegetarian_pizzas
1412 >>>
1413 >>> # Not recommended:
1414 >>> restaurants = Restaurant.objects.prefetch_related(
1415 ... Prefetch("pizzas", queryset=queryset),
1416 ... )
1417 >>> vegetarian_pizzas = restaurants[0].pizzas.all()
1418
1419 Custom prefetching also works with single related relations like
1420 forward ``ForeignKey`` or ``OneToOneField``. Generally you'll want to use
1421 :meth:`select_related()` for these relations, but there are a number of cases
1422 where prefetching with a custom ``QuerySet`` is useful:
1423
1424 * You want to use a ``QuerySet`` that performs further prefetching
1425 on related models.
1426
1427 * You want to prefetch only a subset of the related objects.
1428
1429 * You want to use performance optimization techniques like
1430 :meth:`deferred fields <defer()>`:
1431
1432 .. code-block:: pycon
1433
1434 >>> queryset = Pizza.objects.only("name")
1435 >>>
1436 >>> restaurants = Restaurant.objects.prefetch_related(
1437 ... Prefetch("best_pizza", queryset=queryset)
1438 ... )
1439
1440 When using multiple databases, ``Prefetch`` will respect your choice of
1441 database. If the inner query does not specify a database, it will use the
1442 database selected by the outer query. All of the following are valid:
1443
1444 .. code-block:: pycon
1445
1446 >>> # Both inner and outer queries will use the 'replica' database
1447 >>> Restaurant.objects.prefetch_related("pizzas__toppings").using("replica")
1448 >>> Restaurant.objects.prefetch_related(
1449 ... Prefetch("pizzas__toppings"),
1450 ... ).using("replica")
1451 >>>
1452 >>> # Inner will use the 'replica' database; outer will use 'default' database
1453 >>> Restaurant.objects.prefetch_related(
1454 ... Prefetch("pizzas__toppings", queryset=Toppings.objects.using("replica")),
1455 ... )
1456 >>>
1457 >>> # Inner will use 'replica' database; outer will use 'cold-storage' database
1458 >>> Restaurant.objects.prefetch_related(
1459 ... Prefetch("pizzas__toppings", queryset=Toppings.objects.using("replica")),
1460 ... ).using("cold-storage")
1461
1462 .. note::
1463
1464 The ordering of lookups matters.
1465
1466 Take the following examples:
1467
1468 .. code-block:: pycon
1469
1470 >>> prefetch_related("pizzas__toppings", "pizzas")
1471
1472 This works even though it's unordered because ``'pizzas__toppings'``
1473 already contains all the needed information, therefore the second argument
1474 ``'pizzas'`` is actually redundant.
1475
1476 .. code-block:: pycon
1477
1478 >>> prefetch_related("pizzas__toppings", Prefetch("pizzas", queryset=Pizza.objects.all()))
1479
1480 This will raise a ``ValueError`` because of the attempt to redefine the
1481 queryset of a previously seen lookup. Note that an implicit queryset was
1482 created to traverse ``'pizzas'`` as part of the ``'pizzas__toppings'``
1483 lookup.
1484
1485 .. code-block:: pycon
1486
1487 >>> prefetch_related("pizza_list__toppings", Prefetch("pizzas", to_attr="pizza_list"))
1488
1489 This will trigger an ``AttributeError`` because ``'pizza_list'`` doesn't exist yet
1490 when ``'pizza_list__toppings'`` is being processed.
1491
1492 This consideration is not limited to the use of ``Prefetch`` objects. Some
1493 advanced techniques may require that the lookups be performed in a
1494 specific order to avoid creating extra queries; therefore it's recommended
1495 to always carefully order ``prefetch_related`` arguments.
1496
1497 ``extra()``
1498 ~~~~~~~~~~~
1499
1500 .. method:: extra(select=None, where=None, params=None, tables=None, order_by=None, select_params=None)
1501
1502 Sometimes, the Django query syntax by itself can't easily express a complex
1503 ``WHERE`` clause. For these edge cases, Django provides the ``extra()``
1504 ``QuerySet`` modifier — a hook for injecting specific clauses into the SQL
1505 generated by a ``QuerySet``.
1506
1507 .. admonition:: Use this method as a last resort
1508
1509 This is an old API that we aim to deprecate at some point in the future.
1510 Use it only if you cannot express your query using other queryset methods.
1511 If you do need to use it, please `file a ticket
1512 <https://code.djangoproject.com/newticket>`_ using the `QuerySet.extra
1513 keyword <https://code.djangoproject.com/query?status=assigned&status=new&keywords=~QuerySet.extra>`_
1514 with your use case (please check the list of existing tickets first) so
1515 that we can enhance the QuerySet API to allow removing ``extra()``. We are
1516 no longer improving or fixing bugs for this method.
1517
1518 For example, this use of ``extra()``:
1519
1520 .. code-block:: pycon
1521
1522 >>> qs.extra(
1523 ... select={"val": "select col from sometable where othercol = %s"},
1524 ... select_params=(someparam,),
1525 ... )
1526
1527 is equivalent to:
1528
1529 .. code-block:: pycon
1530
1531 >>> qs.annotate(val=RawSQL("select col from sometable where othercol = %s", (someparam,)))
1532
1533 The main benefit of using :class:`~django.db.models.expressions.RawSQL` is
1534 that you can set ``output_field`` if needed. The main downside is that if
1535 you refer to some table alias of the queryset in the raw SQL, then it is
1536 possible that Django might change that alias (for example, when the
1537 queryset is used as a subquery in yet another query).
1538
1539 .. warning::
1540
1541 You should be very careful whenever you use ``extra()``. Every time you use
1542 it, you should escape any parameters that the user can control by using
1543 ``params`` in order to protect against SQL injection attacks.
1544
1545 You also must not quote placeholders in the SQL string. This example is
1546 vulnerable to SQL injection because of the quotes around ``%s``:
1547
1548 .. code-block:: sql
1549
1550 SELECT col FROM sometable WHERE othercol = '%s' # unsafe!
1551
1552 You can read more about how Django's :ref:`SQL injection protection
1553 <sql-injection-protection>` works.
1554
1555 By definition, these extra lookups may not be portable to different database
1556 engines (because you're explicitly writing SQL code) and violate the DRY
1557 principle, so you should avoid them if possible.
1558
1559 Specify one or more of ``params``, ``select``, ``where`` or ``tables``. None
1560 of the arguments is required, but you should use at least one of them.
1561
1562 * ``select``
1563
1564 The ``select`` argument lets you put extra fields in the ``SELECT``
1565 clause. It should be a dictionary mapping attribute names to SQL
1566 clauses to use to calculate that attribute.
1567
1568 Example::
1569
1570 Entry.objects.extra(select={"is_recent": "pub_date > '2006-01-01'"})
1571
1572 As a result, each ``Entry`` object will have an extra attribute,
1573 ``is_recent``, a boolean representing whether the entry's ``pub_date``
1574 is greater than Jan. 1, 2006.
1575
1576 Django inserts the given SQL snippet directly into the ``SELECT``
1577 statement, so the resulting SQL of the above example would be something like:
1578
1579 .. code-block:: sql
1580
1581 SELECT blog_entry.*, (pub_date > '2006-01-01') AS is_recent
1582 FROM blog_entry;
1583
1584
1585 The next example is more advanced; it does a subquery to give each
1586 resulting ``Blog`` object an ``entry_count`` attribute, an integer count
1587 of associated ``Entry`` objects::
1588
1589 Blog.objects.extra(
1590 select={
1591 "entry_count": "SELECT COUNT(*) FROM blog_entry WHERE blog_entry.blog_id = blog_blog.id"
1592 },
1593 )
1594
1595 In this particular case, we're exploiting the fact that the query will
1596 already contain the ``blog_blog`` table in its ``FROM`` clause.
1597
1598 The resulting SQL of the above example would be:
1599
1600 .. code-block:: sql
1601
1602 SELECT blog_blog.*, (SELECT COUNT(*) FROM blog_entry WHERE blog_entry.blog_id = blog_blog.id) AS entry_count
1603 FROM blog_blog;
1604
1605 Note that the parentheses required by most database engines around
1606 subqueries are not required in Django's ``select`` clauses.
1607
1608 In some rare cases, you might wish to pass parameters to the SQL
1609 fragments in ``extra(select=...)``. For this purpose, use the
1610 ``select_params`` parameter.
1611
1612 This will work, for example::
1613
1614 Blog.objects.extra(
1615 select={"a": "%s", "b": "%s"},
1616 select_params=("one", "two"),
1617 )
1618
1619 If you need to use a literal ``%s`` inside your select string, use
1620 the sequence ``%%s``.
1621
1622 * ``where`` / ``tables``
1623
1624 You can define explicit SQL ``WHERE`` clauses — perhaps to perform
1625 non-explicit joins — by using ``where``. You can manually add tables to
1626 the SQL ``FROM`` clause by using ``tables``.
1627
1628 ``where`` and ``tables`` both take a list of strings. All ``where``
1629 parameters are "AND"ed to any other search criteria.
1630
1631 Example::
1632
1633 Entry.objects.extra(where=["foo='a' OR bar = 'a'", "baz = 'a'"])
1634
1635 ...translates (roughly) into the following SQL:
1636
1637 .. code-block:: sql
1638
1639 SELECT * FROM blog_entry WHERE (foo='a' OR bar='a') AND (baz='a')
1640
1641 Be careful when using the ``tables`` parameter if you're specifying
1642 tables that are already used in the query. When you add extra tables
1643 via the ``tables`` parameter, Django assumes you want that table
1644 included an extra time, if it is already included. That creates a
1645 problem, since the table name will then be given an alias. If a table
1646 appears multiple times in an SQL statement, the second and subsequent
1647 occurrences must use aliases so the database can tell them apart. If
1648 you're referring to the extra table you added in the extra ``where``
1649 parameter this is going to cause errors.
1650
1651 Normally you'll only be adding extra tables that don't already appear
1652 in the query. However, if the case outlined above does occur, there are
1653 a few solutions. First, see if you can get by without including the
1654 extra table and use the one already in the query. If that isn't
1655 possible, put your ``extra()`` call at the front of the queryset
1656 construction so that your table is the first use of that table.
1657 Finally, if all else fails, look at the query produced and rewrite your
1658 ``where`` addition to use the alias given to your extra table. The
1659 alias will be the same each time you construct the queryset in the same
1660 way, so you can rely upon the alias name to not change.
1661
1662 * ``order_by``
1663
1664 If you need to order the resulting queryset using some of the new
1665 fields or tables you have included via ``extra()`` use the ``order_by``
1666 parameter to ``extra()`` and pass in a sequence of strings. These
1667 strings should either be model fields (as in the normal
1668 :meth:`order_by()` method on querysets), of the form
1669 ``table_name.column_name`` or an alias for a column that you specified
1670 in the ``select`` parameter to ``extra()``.
1671
1672 For example::
1673
1674 q = Entry.objects.extra(select={"is_recent": "pub_date > '2006-01-01'"})
1675 q = q.extra(order_by=["-is_recent"])
1676
1677 This would sort all the items for which ``is_recent`` is true to the
1678 front of the result set (``True`` sorts before ``False`` in a
1679 descending ordering).
1680
1681 This shows, by the way, that you can make multiple calls to ``extra()``
1682 and it will behave as you expect (adding new constraints each time).
1683
1684 * ``params``
1685
1686 The ``where`` parameter described above may use standard Python
1687 database string placeholders — ``'%s'`` to indicate parameters the
1688 database engine should automatically quote. The ``params`` argument is
1689 a list of any extra parameters to be substituted.
1690
1691 Example::
1692
1693 Entry.objects.extra(where=["headline=%s"], params=["Lennon"])
1694
1695 Always use ``params`` instead of embedding values directly into
1696 ``where`` because ``params`` will ensure values are quoted correctly
1697 according to your particular backend. For example, quotes will be
1698 escaped correctly.
1699
1700 Bad::
1701
1702 Entry.objects.extra(where=["headline='Lennon'"])
1703
1704 Good::
1705
1706 Entry.objects.extra(where=["headline=%s"], params=["Lennon"])
1707
1708 .. warning::
1709
1710 If you are performing queries on MySQL, note that MySQL's silent type coercion
1711 may cause unexpected results when mixing types. If you query on a string
1712 type column, but with an integer value, MySQL will coerce the types of all values
1713 in the table to an integer before performing the comparison. For example, if your
1714 table contains the values ``'abc'``, ``'def'`` and you query for ``WHERE mycolumn=0``,
1715 both rows will match. To prevent this, perform the correct typecasting
1716 before using the value in a query.
1717
1718 ``defer()``
1719 ~~~~~~~~~~~
1720
1721 .. method:: defer(*fields)
1722
1723 In some complex data-modeling situations, your models might contain a lot of
1724 fields, some of which could contain a lot of data (for example, text fields),
1725 or require expensive processing to convert them to Python objects. If you are
1726 using the results of a queryset in some situation where you don't know
1727 if you need those particular fields when you initially fetch the data, you can
1728 tell Django not to retrieve them from the database.
1729
1730 This is done by passing the names of the fields to not load to ``defer()``::
1731
1732 Entry.objects.defer("headline", "body")
1733
1734 A queryset that has deferred fields will still return model instances. Each
1735 deferred field will be retrieved from the database if you access that field
1736 (one at a time, not all the deferred fields at once).
1737
1738 .. note::
1739
1740 Deferred fields will not lazy-load like this from asynchronous code.
1741 Instead, you will get a ``SynchronousOnlyOperation`` exception. If you are
1742 writing asynchronous code, you should not try to access any fields that you
1743 ``defer()``.
1744
1745 You can make multiple calls to ``defer()``. Each call adds new fields to the
1746 deferred set::
1747
1748 # Defers both the body and headline fields.
1749 Entry.objects.defer("body").filter(rating=5).defer("headline")
1750
1751 The order in which fields are added to the deferred set does not matter.
1752 Calling ``defer()`` with a field name that has already been deferred is
1753 harmless (the field will still be deferred).
1754
1755 You can defer loading of fields in related models (if the related models are
1756 loading via :meth:`select_related()`) by using the standard double-underscore
1757 notation to separate related fields::
1758
1759 Blog.objects.select_related().defer("entry__headline", "entry__body")
1760
1761 If you want to clear the set of deferred fields, pass ``None`` as a parameter
1762 to ``defer()``::
1763
1764 # Load all fields immediately.
1765 my_queryset.defer(None)
1766
1767 Some fields in a model won't be deferred, even if you ask for them. You can
1768 never defer the loading of the primary key. If you are using
1769 :meth:`select_related()` to retrieve related models, you shouldn't defer the
1770 loading of the field that connects from the primary model to the related
1771 one, doing so will result in an error.
1772
1773 Similarly, calling ``defer()`` (or its counterpart :meth:`only()`) including an
1774 argument from an aggregation (e.g. using the result of :meth:`annotate()`)
1775 doesn't make sense: doing so will raise an exception. The aggregated values
1776 will always be fetched into the resulting queryset.
1777
1778 .. note::
1779
1780 The ``defer()`` method (and its cousin, :meth:`only()`, below) are only for
1781 advanced use-cases. They provide an optimization for when you have analyzed
1782 your queries closely and understand *exactly* what information you need and
1783 have measured that the difference between returning the fields you need and
1784 the full set of fields for the model will be significant.
1785
1786 Even if you think you are in the advanced use-case situation, **only use**
1787 ``defer()`` **when you cannot, at queryset load time, determine if you will
1788 need the extra fields or not**. If you are frequently loading and using a
1789 particular subset of your data, the best choice you can make is to
1790 normalize your models and put the non-loaded data into a separate model
1791 (and database table). If the columns *must* stay in the one table for some
1792 reason, create a model with ``Meta.managed = False`` (see the
1793 :attr:`managed attribute <django.db.models.Options.managed>` documentation)
1794 containing just the fields you normally need to load and use that where you
1795 might otherwise call ``defer()``. This makes your code more explicit to the
1796 reader, is slightly faster and consumes a little less memory in the Python
1797 process.
1798
1799 For example, both of these models use the same underlying database table::
1800
1801 class CommonlyUsedModel(models.Model):
1802 f1 = models.CharField(max_length=10)
1803
1804 class Meta:
1805 managed = False
1806 db_table = "app_largetable"
1807
1808
1809 class ManagedModel(models.Model):
1810 f1 = models.CharField(max_length=10)
1811 f2 = models.CharField(max_length=10)
1812
1813 class Meta:
1814 db_table = "app_largetable"
1815
1816
1817 # Two equivalent QuerySets:
1818 CommonlyUsedModel.objects.all()
1819 ManagedModel.objects.defer("f2")
1820
1821 If many fields need to be duplicated in the unmanaged model, it may be best
1822 to create an abstract model with the shared fields and then have the
1823 unmanaged and managed models inherit from the abstract model.
1824
1825 .. note::
1826
1827 When calling :meth:`~django.db.models.Model.save()` for instances with
1828 deferred fields, only the loaded fields will be saved. See
1829 :meth:`~django.db.models.Model.save()` for more details.
1830
1831 ``only()``
1832 ~~~~~~~~~~
1833
1834 .. method:: only(*fields)
1835
1836 The ``only()`` method is essentially the opposite of :meth:`defer`. Only the
1837 fields passed into this method and that are *not* already specified as deferred
1838 are loaded immediately when the queryset is evaluated.
1839
1840 If you have a model where almost all the fields need to be deferred, using
1841 ``only()`` to specify the complementary set of fields can result in simpler
1842 code.
1843
1844 Suppose you have a model with fields ``name``, ``age`` and ``biography``. The
1845 following two querysets are the same, in terms of deferred fields::
1846
1847 Person.objects.defer("age", "biography")
1848 Person.objects.only("name")
1849
1850 Whenever you call ``only()`` it *replaces* the set of fields to load
1851 immediately. The method's name is mnemonic: **only** those fields are loaded
1852 immediately; the remainder are deferred. Thus, successive calls to ``only()``
1853 result in only the final fields being considered::
1854
1855 # This will defer all fields except the headline.
1856 Entry.objects.only("body", "rating").only("headline")
1857
1858 Since ``defer()`` acts incrementally (adding fields to the deferred list), you
1859 can combine calls to ``only()`` and ``defer()`` and things will behave
1860 logically::
1861
1862 # Final result is that everything except "headline" is deferred.
1863 Entry.objects.only("headline", "body").defer("body")
1864
1865 # Final result loads headline immediately.
1866 Entry.objects.defer("body").only("headline", "body")
1867
1868 All of the cautions in the note for the :meth:`defer` documentation apply to
1869 ``only()`` as well. Use it cautiously and only after exhausting your other
1870 options.
1871
1872 Using ``only()`` and omitting a field requested using :meth:`select_related` is
1873 an error as well. On the other hand, invoking ``only()`` without any arguments,
1874 will return every field (including annotations) fetched by the queryset.
1875
1876 As with ``defer()``, you cannot access the non-loaded fields from asynchronous
1877 code and expect them to load. Instead, you will get a
1878 ``SynchronousOnlyOperation`` exception. Ensure that all fields you might access
1879 are in your ``only()`` call.
1880
1881 .. note::
1882
1883 When calling :meth:`~django.db.models.Model.save()` for instances with
1884 deferred fields, only the loaded fields will be saved. See
1885 :meth:`~django.db.models.Model.save()` for more details.
1886
1887 .. note::
1888
1889 When using :meth:`defer` after ``only()`` the fields in :meth:`defer` will
1890 override ``only()`` for fields that are listed in both.
1891
1892 ``using()``
1893 ~~~~~~~~~~~
1894
1895 .. method:: using(alias)
1896
1897 This method is for controlling which database the ``QuerySet`` will be
1898 evaluated against if you are using more than one database. The only argument
1899 this method takes is the alias of a database, as defined in
1900 :setting:`DATABASES`.
1901
1902 For example:
1903
1904 .. code-block:: pycon
1905
1906 # queries the database with the 'default' alias.
1907 >>> Entry.objects.all()
1908
1909 # queries the database with the 'backup' alias
1910 >>> Entry.objects.using("backup")
1911
1912 ``select_for_update()``
1913 ~~~~~~~~~~~~~~~~~~~~~~~
1914
1915 .. method:: select_for_update(nowait=False, skip_locked=False, of=(), no_key=False)
1916
1917 Returns a queryset that will lock rows until the end of the transaction,
1918 generating a ``SELECT ... FOR UPDATE`` SQL statement on supported databases.
1919
1920 For example::
1921
1922 from django.db import transaction
1923
1924 entries = Entry.objects.select_for_update().filter(author=request.user)
1925 with transaction.atomic():
1926 for entry in entries:
1927 ...
1928
1929 When the queryset is evaluated (``for entry in entries`` in this case), all
1930 matched entries will be locked until the end of the transaction block, meaning
1931 that other transactions will be prevented from changing or acquiring locks on
1932 them.
1933
1934 Usually, if another transaction has already acquired a lock on one of the
1935 selected rows, the query will block until the lock is released. If this is
1936 not the behavior you want, call ``select_for_update(nowait=True)``. This will
1937 make the call non-blocking. If a conflicting lock is already acquired by
1938 another transaction, :exc:`~django.db.DatabaseError` will be raised when the
1939 queryset is evaluated. You can also ignore locked rows by using
1940 ``select_for_update(skip_locked=True)`` instead. The ``nowait`` and
1941 ``skip_locked`` are mutually exclusive and attempts to call
1942 ``select_for_update()`` with both options enabled will result in a
1943 :exc:`ValueError`.
1944
1945 By default, ``select_for_update()`` locks all rows that are selected by the
1946 query. For example, rows of related objects specified in :meth:`select_related`
1947 are locked in addition to rows of the queryset's model. If this isn't desired,
1948 specify the related objects you want to lock in ``select_for_update(of=(...))``
1949 using the same fields syntax as :meth:`select_related`. Use the value ``'self'``
1950 to refer to the queryset's model.
1951
1952 .. admonition:: Lock parents models in ``select_for_update(of=(...))``
1953
1954 If you want to lock parents models when using :ref:`multi-table inheritance
1955 <multi-table-inheritance>`, you must specify parent link fields (by default
1956 ``<parent_model_name>_ptr``) in the ``of`` argument. For example::
1957
1958 Restaurant.objects.select_for_update(of=("self", "place_ptr"))
1959
1960 .. admonition:: Using ``select_for_update(of=(...))`` with specified fields
1961
1962 If you want to lock models and specify selected fields, e.g. using
1963 :meth:`values`, you must select at least one field from each model in the
1964 ``of`` argument. Models without selected fields will not be locked.
1965
1966 On PostgreSQL only, you can pass ``no_key=True`` in order to acquire a weaker
1967 lock, that still allows creating rows that merely reference locked rows
1968 (through a foreign key, for example) while the lock is in place. The
1969 PostgreSQL documentation has more details about `row-level lock modes
1970 <https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-ROWS>`_.
1971
1972 You can't use ``select_for_update()`` on nullable relations:
1973
1974 .. code-block:: pycon
1975
1976 >>> Person.objects.select_related("hometown").select_for_update()
1977 Traceback (most recent call last):
1978 ...
1979 django.db.utils.NotSupportedError: FOR UPDATE cannot be applied to the nullable side of an outer join
1980
1981 To avoid that restriction, you can exclude null objects if you don't care about
1982 them:
1983
1984 .. code-block:: pycon
1985
1986 >>> Person.objects.select_related("hometown").select_for_update().exclude(hometown=None)
1987 <QuerySet [<Person: ...)>, ...]>
1988
1989 The ``postgresql``, ``oracle``, and ``mysql`` database backends support
1990 ``select_for_update()``. However, MariaDB only supports the ``nowait``
1991 argument, MariaDB 10.6+ also supports the ``skip_locked`` argument, and MySQL
1992 supports the ``nowait``, ``skip_locked``, and ``of`` arguments. The ``no_key``
1993 argument is only supported on PostgreSQL.
1994
1995 Passing ``nowait=True``, ``skip_locked=True``, ``no_key=True``, or ``of`` to
1996 ``select_for_update()`` using database backends that do not support these
1997 options, such as MySQL, raises a :exc:`~django.db.NotSupportedError`. This
1998 prevents code from unexpectedly blocking.
1999
2000 Evaluating a queryset with ``select_for_update()`` in autocommit mode on
2001 backends which support ``SELECT ... FOR UPDATE`` is a
2002 :exc:`~django.db.transaction.TransactionManagementError` error because the
2003 rows are not locked in that case. If allowed, this would facilitate data
2004 corruption and could easily be caused by calling code that expects to be run in
2005 a transaction outside of one.
2006
2007 Using ``select_for_update()`` on backends which do not support
2008 ``SELECT ... FOR UPDATE`` (such as SQLite) will have no effect.
2009 ``SELECT ... FOR UPDATE`` will not be added to the query, and an error isn't
2010 raised if ``select_for_update()`` is used in autocommit mode.
2011
2012 .. warning::
2013
2014 Although ``select_for_update()`` normally fails in autocommit mode, since
2015 :class:`~django.test.TestCase` automatically wraps each test in a
2016 transaction, calling ``select_for_update()`` in a ``TestCase`` even outside
2017 an :func:`~django.db.transaction.atomic()` block will (perhaps unexpectedly)
2018 pass without raising a ``TransactionManagementError``. To properly test
2019 ``select_for_update()`` you should use
2020 :class:`~django.test.TransactionTestCase`.
2021
2022 .. admonition:: Certain expressions may not be supported
2023
2024 PostgreSQL doesn't support ``select_for_update()`` with
2025 :class:`~django.db.models.expressions.Window` expressions.
2026
2027 ``raw()``
2028 ~~~~~~~~~
2029
2030 .. method:: raw(raw_query, params=(), translations=None, using=None)
2031
2032 Takes a raw SQL query, executes it, and returns a
2033 ``django.db.models.query.RawQuerySet`` instance. This ``RawQuerySet`` instance
2034 can be iterated over just like a normal ``QuerySet`` to provide object
2035 instances.
2036
2037 See the :doc:`/topics/db/sql` for more information.
2038
2039 .. warning::
2040
2041 ``raw()`` always triggers a new query and doesn't account for previous
2042 filtering. As such, it should generally be called from the ``Manager`` or
2043 from a fresh ``QuerySet`` instance.
2044
2045 Operators that return new ``QuerySet``\s
2046 ----------------------------------------
2047
2048 Combined querysets must use the same model.
2049
2050 AND (``&``)
2051 ~~~~~~~~~~~
2052
2053 Combines two ``QuerySet``\s using the SQL ``AND`` operator in a manner similar
2054 to chaining filters.
2055
2056 The following are equivalent::
2057
2058 Model.objects.filter(x=1) & Model.objects.filter(y=2)
2059 Model.objects.filter(x=1).filter(y=2)
2060
2061 SQL equivalent:
2062
2063 .. code-block:: sql
2064
2065 SELECT ... WHERE x=1 AND y=2
2066
2067 OR (``|``)
2068 ~~~~~~~~~~
2069
2070 Combines two ``QuerySet``\s using the SQL ``OR`` operator.
2071
2072 The following are equivalent::
2073
2074 Model.objects.filter(x=1) | Model.objects.filter(y=2)
2075 from django.db.models import Q
2076
2077 Model.objects.filter(Q(x=1) | Q(y=2))
2078
2079 SQL equivalent:
2080
2081 .. code-block:: sql
2082
2083 SELECT ... WHERE x=1 OR y=2
2084
2085 ``|`` is not a commutative operation, as different (though equivalent) queries
2086 may be generated.
2087
2088 XOR (``^``)
2089 ~~~~~~~~~~~
2090
2091 Combines two ``QuerySet``\s using the SQL ``XOR`` operator. A ``XOR``
2092 expression matches rows that are matched by an odd number of operands.
2093
2094 The following are equivalent::
2095
2096 Model.objects.filter(x=1) ^ Model.objects.filter(y=2)
2097 from django.db.models import Q
2098
2099 Model.objects.filter(Q(x=1) ^ Q(y=2))
2100
2101 SQL equivalent:
2102
2103 .. code-block:: sql
2104
2105 SELECT ... WHERE x=1 XOR y=2
2106
2107 .. note::
2108
2109 ``XOR`` is natively supported on MariaDB and MySQL. On other databases,
2110 ``x ^ y ^ ... ^ z`` is converted to an equivalent:
2111
2112 .. code-block:: sql
2113
2114 (x OR y OR ... OR z) AND
2115 1=MOD(
2116 (CASE WHEN x THEN 1 ELSE 0 END) +
2117 (CASE WHEN y THEN 1 ELSE 0 END) +
2118 ...
2119 (CASE WHEN z THEN 1 ELSE 0 END),
2120 2
2121 )
2122
2123 Methods that do not return ``QuerySet``\s
2124 -----------------------------------------
2125
2126 The following ``QuerySet`` methods evaluate the ``QuerySet`` and return
2127 something *other than* a ``QuerySet``.
2128
2129 These methods do not use a cache (see :ref:`caching-and-querysets`). Rather,
2130 they query the database each time they're called.
2131
2132 Because these methods evaluate the QuerySet, they are blocking calls, and so
2133 their main (synchronous) versions cannot be called from asynchronous code. For
2134 this reason, each has a corresponding asynchronous version with an ``a`` prefix
2135 - for example, rather than ``get(…)`` you can ``await aget(…)``.
2136
2137 There is usually no difference in behavior apart from their asynchronous
2138 nature, but any differences are noted below next to each method.
2139
2140 ``get()``
2141 ~~~~~~~~~
2142
2143 .. method:: get(*args, **kwargs)
2144 .. method:: aget(*args, **kwargs)
2145
2146 *Asynchronous version*: ``aget()``
2147
2148 Returns the object matching the given lookup parameters, which should be in
2149 the format described in `Field lookups`_. You should use lookups that are
2150 guaranteed unique, such as the primary key or fields in a unique constraint.
2151 For example::
2152
2153 Entry.objects.get(id=1)
2154 Entry.objects.get(Q(blog=blog) & Q(entry_number=1))
2155
2156 If you expect a queryset to already return one row, you can use ``get()``
2157 without any arguments to return the object for that row::
2158
2159 Entry.objects.filter(pk=1).get()
2160
2161 If ``get()`` doesn't find any object, it raises a :exc:`Model.DoesNotExist
2162 <django.db.models.Model.DoesNotExist>` exception::
2163
2164 Entry.objects.get(id=-999) # raises Entry.DoesNotExist
2165
2166 If ``get()`` finds more than one object, it raises a
2167 :exc:`Model.MultipleObjectsReturned
2168 <django.db.models.Model.MultipleObjectsReturned>` exception::
2169
2170 Entry.objects.get(name="A Duplicated Name") # raises Entry.MultipleObjectsReturned
2171
2172 Both these exception classes are attributes of the model class, and specific to
2173 that model. If you want to handle such exceptions from several ``get()`` calls
2174 for different models, you can use their generic base classes. For example, you
2175 can use :exc:`django.core.exceptions.ObjectDoesNotExist` to handle
2176 :exc:`~django.db.models.Model.DoesNotExist` exceptions from multiple models::
2177
2178 from django.core.exceptions import ObjectDoesNotExist
2179
2180 try:
2181 blog = Blog.objects.get(id=1)
2182 entry = Entry.objects.get(blog=blog, entry_number=1)
2183 except ObjectDoesNotExist:
2184 print("Either the blog or entry doesn't exist.")
2185
2186 ``create()``
2187 ~~~~~~~~~~~~
2188
2189 .. method:: create(**kwargs)
2190 .. method:: acreate(**kwargs)
2191
2192 *Asynchronous version*: ``acreate()``
2193
2194 A convenience method for creating an object and saving it all in one step. Thus::
2195
2196 p = Person.objects.create(first_name="Bruce", last_name="Springsteen")
2197
2198 and::
2199
2200 p = Person(first_name="Bruce", last_name="Springsteen")
2201 p.save(force_insert=True)
2202
2203 are equivalent.
2204
2205 The :ref:`force_insert <ref-models-force-insert>` parameter is documented
2206 elsewhere, but all it means is that a new object will always be created.
2207 Normally you won't need to worry about this. However, if your model contains a
2208 manual primary key value that you set and if that value already exists in the
2209 database, a call to ``create()`` will fail with an
2210 :exc:`~django.db.IntegrityError` since primary keys must be unique. Be
2211 prepared to handle the exception if you are using manual primary keys.
2212
2213 ``get_or_create()``
2214 ~~~~~~~~~~~~~~~~~~~
2215
2216 .. method:: get_or_create(defaults=None, **kwargs)
2217 .. method:: aget_or_create(defaults=None, **kwargs)
2218
2219 *Asynchronous version*: ``aget_or_create()``
2220
2221 A convenience method for looking up an object with the given ``kwargs`` (may be
2222 empty if your model has defaults for all fields), creating one if necessary.
2223
2224 Returns a tuple of ``(object, created)``, where ``object`` is the retrieved or
2225 created object and ``created`` is a boolean specifying whether a new object was
2226 created.
2227
2228 This is meant to prevent duplicate objects from being created when requests are
2229 made in parallel, and as a shortcut to boilerplatish code. For example::
2230
2231 try:
2232 obj = Person.objects.get(first_name="John", last_name="Lennon")
2233 except Person.DoesNotExist:
2234 obj = Person(first_name="John", last_name="Lennon", birthday=date(1940, 10, 9))
2235 obj.save()
2236
2237 Here, with concurrent requests, multiple attempts to save a ``Person`` with
2238 the same parameters may be made. To avoid this race condition, the above
2239 example can be rewritten using ``get_or_create()`` like so::
2240
2241 obj, created = Person.objects.get_or_create(
2242 first_name="John",
2243 last_name="Lennon",
2244 defaults={"birthday": date(1940, 10, 9)},
2245 )
2246
2247 Any keyword arguments passed to ``get_or_create()`` — *except* an optional one
2248 called ``defaults`` — will be used in a :meth:`get()` call. If an object is
2249 found, ``get_or_create()`` returns a tuple of that object and ``False``.
2250
2251 .. warning::
2252
2253 This method is atomic assuming that the database enforces uniqueness of the
2254 keyword arguments (see :attr:`~django.db.models.Field.unique` or
2255 :attr:`~django.db.models.Options.unique_together`). If the fields used in the
2256 keyword arguments do not have a uniqueness constraint, concurrent calls to
2257 this method may result in multiple rows with the same parameters being
2258 inserted.
2259
2260 You can specify more complex conditions for the retrieved object by chaining
2261 ``get_or_create()`` with ``filter()`` and using :class:`Q objects
2262 <django.db.models.Q>`. For example, to retrieve Robert or Bob Marley if either
2263 exists, and create the latter otherwise::
2264
2265 from django.db.models import Q
2266
2267 obj, created = Person.objects.filter(
2268 Q(first_name="Bob") | Q(first_name="Robert"),
2269 ).get_or_create(last_name="Marley", defaults={"first_name": "Bob"})
2270
2271 If multiple objects are found, ``get_or_create()`` raises
2272 :exc:`~django.core.exceptions.MultipleObjectsReturned`. If an object is *not*
2273 found, ``get_or_create()`` will instantiate and save a new object, returning a
2274 tuple of the new object and ``True``. The new object will be created roughly
2275 according to this algorithm::
2276
2277 params = {k: v for k, v in kwargs.items() if "__" not in k}
2278 params.update({k: v() if callable(v) else v for k, v in defaults.items()})
2279 obj = self.model(**params)
2280 obj.save()
2281
2282 In English, that means start with any non-``'defaults'`` keyword argument that
2283 doesn't contain a double underscore (which would indicate a non-exact lookup).
2284 Then add the contents of ``defaults``, overriding any keys if necessary, and
2285 use the result as the keyword arguments to the model class. If there are any
2286 callables in ``defaults``, evaluate them. As hinted at above, this is a
2287 simplification of the algorithm that is used, but it contains all the pertinent
2288 details. The internal implementation has some more error-checking than this and
2289 handles some extra edge-conditions; if you're interested, read the code.
2290
2291 If you have a field named ``defaults`` and want to use it as an exact lookup in
2292 ``get_or_create()``, use ``'defaults__exact'``, like so::
2293
2294 Foo.objects.get_or_create(defaults__exact="bar", defaults={"defaults": "baz"})
2295
2296 The ``get_or_create()`` method has similar error behavior to :meth:`create()`
2297 when you're using manually specified primary keys. If an object needs to be
2298 created and the key already exists in the database, an
2299 :exc:`~django.db.IntegrityError` will be raised.
2300
2301 Finally, a word on using ``get_or_create()`` in Django views. Please make sure
2302 to use it only in ``POST`` requests unless you have a good reason not to.
2303 ``GET`` requests shouldn't have any effect on data. Instead, use ``POST``
2304 whenever a request to a page has a side effect on your data. For more, see
2305 :rfc:`Safe methods <9110#section-9.2.1>` in the HTTP spec.
2306
2307 .. warning::
2308
2309 You can use ``get_or_create()`` through :class:`~django.db.models.ManyToManyField`
2310 attributes and reverse relations. In that case you will restrict the queries
2311 inside the context of that relation. That could lead you to some integrity
2312 problems if you don't use it consistently.
2313
2314 Being the following models::
2315
2316 class Chapter(models.Model):
2317 title = models.CharField(max_length=255, unique=True)
2318
2319
2320 class Book(models.Model):
2321 title = models.CharField(max_length=256)
2322 chapters = models.ManyToManyField(Chapter)
2323
2324 You can use ``get_or_create()`` through Book's chapters field, but it only
2325 fetches inside the context of that book:
2326
2327 .. code-block:: pycon
2328
2329 >>> book = Book.objects.create(title="Ulysses")
2330 >>> book.chapters.get_or_create(title="Telemachus")
2331 (<Chapter: Telemachus>, True)
2332 >>> book.chapters.get_or_create(title="Telemachus")
2333 (<Chapter: Telemachus>, False)
2334 >>> Chapter.objects.create(title="Chapter 1")
2335 <Chapter: Chapter 1>
2336 >>> book.chapters.get_or_create(title="Chapter 1")
2337 # Raises IntegrityError
2338
2339 This is happening because it's trying to get or create "Chapter 1" through the
2340 book "Ulysses", but it can't do either: the relation can't fetch that chapter
2341 because it isn't related to that book, but it can't create it either because
2342 ``title`` field should be unique.
2343
2344 ``update_or_create()``
2345 ~~~~~~~~~~~~~~~~~~~~~~
2346
2347 .. method:: update_or_create(defaults=None, create_defaults=None, **kwargs)
2348 .. method:: aupdate_or_create(defaults=None, create_defaults=None, **kwargs)
2349
2350 *Asynchronous version*: ``aupdate_or_create()``
2351
2352 A convenience method for updating an object with the given ``kwargs``, creating
2353 a new one if necessary. Both ``create_defaults`` and ``defaults`` are
2354 dictionaries of (field, value) pairs. The values in both ``create_defaults``
2355 and ``defaults`` can be callables. ``defaults`` is used to update the object
2356 while ``create_defaults`` are used for the create operation. If
2357 ``create_defaults`` is not supplied, ``defaults`` will be used for the create
2358 operation.
2359
2360 Returns a tuple of ``(object, created)``, where ``object`` is the created or
2361 updated object and ``created`` is a boolean specifying whether a new object was
2362 created.
2363
2364 The ``update_or_create`` method tries to fetch an object from database based on
2365 the given ``kwargs``. If a match is found, it updates the fields passed in the
2366 ``defaults`` dictionary.
2367
2368 This is meant as a shortcut to boilerplatish code. For example::
2369
2370 defaults = {"first_name": "Bob"}
2371 create_defaults = {"first_name": "Bob", "birthday": date(1940, 10, 9)}
2372 try:
2373 obj = Person.objects.get(first_name="John", last_name="Lennon")
2374 for key, value in defaults.items():
2375 setattr(obj, key, value)
2376 obj.save()
2377 except Person.DoesNotExist:
2378 new_values = {"first_name": "John", "last_name": "Lennon"}
2379 new_values.update(create_defaults)
2380 obj = Person(**new_values)
2381 obj.save()
2382
2383 This pattern gets quite unwieldy as the number of fields in a model goes up.
2384 The above example can be rewritten using ``update_or_create()`` like so::
2385
2386 obj, created = Person.objects.update_or_create(
2387 first_name="John",
2388 last_name="Lennon",
2389 defaults={"first_name": "Bob"},
2390 create_defaults={"first_name": "Bob", "birthday": date(1940, 10, 9)},
2391 )
2392
2393 For a detailed description of how names passed in ``kwargs`` are resolved, see
2394 :meth:`get_or_create`.
2395
2396 As described above in :meth:`get_or_create`, this method is prone to a
2397 race-condition which can result in multiple rows being inserted simultaneously
2398 if uniqueness is not enforced at the database level.
2399
2400 Like :meth:`get_or_create` and :meth:`create`, if you're using manually
2401 specified primary keys and an object needs to be created but the key already
2402 exists in the database, an :exc:`~django.db.IntegrityError` is raised.
2403
2404 ``bulk_create()``
2405 ~~~~~~~~~~~~~~~~~
2406
2407 .. method:: bulk_create(objs, batch_size=None, ignore_conflicts=False, update_conflicts=False, update_fields=None, unique_fields=None)
2408 .. method:: abulk_create(objs, batch_size=None, ignore_conflicts=False, update_conflicts=False, update_fields=None, unique_fields=None)
2409
2410 *Asynchronous version*: ``abulk_create()``
2411
2412 This method inserts the provided list of objects into the database in an
2413 efficient manner (generally only 1 query, no matter how many objects there
2414 are), and returns created objects as a list, in the same order as provided:
2415
2416 .. code-block:: pycon
2417
2418 >>> objs = Entry.objects.bulk_create(
2419 ... [
2420 ... Entry(headline="This is a test"),
2421 ... Entry(headline="This is only a test"),
2422 ... ]
2423 ... )
2424
2425 This has a number of caveats though:
2426
2427 * The model's ``save()`` method will not be called, and the ``pre_save`` and
2428 ``post_save`` signals will not be sent.
2429 * It does not work with child models in a multi-table inheritance scenario.
2430 * If the model's primary key is an :class:`~django.db.models.AutoField` and
2431 ``ignore_conflicts`` is False, the primary key attribute can only be
2432 retrieved on certain databases (currently PostgreSQL, MariaDB, and SQLite
2433 3.35+). On other databases, it will not be set.
2434 * It does not work with many-to-many relationships.
2435 * It casts ``objs`` to a list, which fully evaluates ``objs`` if it's a
2436 generator. The cast allows inspecting all objects so that any objects with a
2437 manually set primary key can be inserted first. If you want to insert objects
2438 in batches without evaluating the entire generator at once, you can use this
2439 technique as long as the objects don't have any manually set primary keys::
2440
2441 from itertools import islice
2442
2443 batch_size = 100
2444 objs = (Entry(headline="Test %s" % i) for i in range(1000))
2445 while True:
2446 batch = list(islice(objs, batch_size))
2447 if not batch:
2448 break
2449 Entry.objects.bulk_create(batch, batch_size)
2450
2451 The ``batch_size`` parameter controls how many objects are created in a single
2452 query. The default is to create as many objects in one batch as the database
2453 will allow. (SQLite and Oracle limit the number of parameters in a query.)
2454
2455 On databases that support it (all but Oracle), setting the ``ignore_conflicts``
2456 parameter to ``True`` tells the database to ignore failure to insert any rows
2457 that fail constraints such as duplicate unique values.
2458
2459 On databases that support it (all except Oracle), setting the
2460 ``update_conflicts`` parameter to ``True``, tells the database to update
2461 ``update_fields`` when a row insertion fails on conflicts. On PostgreSQL and
2462 SQLite, in addition to ``update_fields``, a list of ``unique_fields`` that may
2463 be in conflict must be provided.
2464
2465 Enabling the ``ignore_conflicts`` parameter disables setting the primary key on
2466 each model instance (if the database normally supports it).
2467
2468 .. warning::
2469
2470 On MySQL and MariaDB, setting the ``ignore_conflicts`` parameter to
2471 ``True`` turns certain types of errors, other than duplicate key, into
2472 warnings. Even with Strict Mode. For example: invalid values or
2473 non-nullable violations. See the `MySQL documentation`_ and
2474 `MariaDB documentation`_ for more details.
2475
2476 .. _MySQL documentation: https://dev.mysql.com/doc/refman/en/sql-mode.html#ignore-strict-comparison
2477 .. _MariaDB documentation: https://mariadb.com/kb/en/ignore/
2478
2479 ``bulk_update()``
2480 ~~~~~~~~~~~~~~~~~
2481
2482 .. method:: bulk_update(objs, fields, batch_size=None)
2483 .. method:: abulk_update(objs, fields, batch_size=None)
2484
2485 *Asynchronous version*: ``abulk_update()``
2486
2487 This method efficiently updates the given fields on the provided model
2488 instances, generally with one query, and returns the number of objects
2489 updated:
2490
2491 .. code-block:: pycon
2492
2493 >>> objs = [
2494 ... Entry.objects.create(headline="Entry 1"),
2495 ... Entry.objects.create(headline="Entry 2"),
2496 ... ]
2497 >>> objs[0].headline = "This is entry 1"
2498 >>> objs[1].headline = "This is entry 2"
2499 >>> Entry.objects.bulk_update(objs, ["headline"])
2500 2
2501
2502 :meth:`.QuerySet.update` is used to save the changes, so this is more efficient
2503 than iterating through the list of models and calling ``save()`` on each of
2504 them, but it has a few caveats:
2505
2506 * You cannot update the model's primary key.
2507 * Each model's ``save()`` method isn't called, and the
2508 :attr:`~django.db.models.signals.pre_save` and
2509 :attr:`~django.db.models.signals.post_save` signals aren't sent.
2510 * If updating a large number of columns in a large number of rows, the SQL
2511 generated can be very large. Avoid this by specifying a suitable
2512 ``batch_size``.
2513 * When updating a large number of objects, be aware that ``bulk_update()``
2514 prepares all of the ``WHEN`` clauses for every object across all batches
2515 before executing any queries. This can require more memory than expected. To
2516 reduce memory usage, you can use an approach like this::
2517
2518 from itertools import islice
2519
2520 batch_size = 100
2521 ids_iter = range(1000)
2522 while ids := list(islice(ids_iter, batch_size)):
2523 batch = Entry.objects.filter(ids__in=ids)
2524 for entry in batch:
2525 entry.headline = f"Updated headline {entry.pk}"
2526 Entry.objects.bulk_update(batch, ["headline"], batch_size=batch_size)
2527
2528 * Updating fields defined on multi-table inheritance ancestors will incur an
2529 extra query per ancestor.
2530 * When an individual batch contains duplicates, only the first instance in that
2531 batch will result in an update.
2532 * The number of objects updated returned by the function may be fewer than the
2533 number of objects passed in. This can be due to duplicate objects passed in
2534 which are updated in the same batch or race conditions such that objects are
2535 no longer present in the database.
2536
2537 The ``batch_size`` parameter controls how many objects are saved in a single
2538 query. The default is to update all objects in one batch, except for SQLite
2539 and Oracle which have restrictions on the number of variables used in a query.
2540
2541 ``count()``
2542 ~~~~~~~~~~~
2543
2544 .. method:: count()
2545 .. method:: acount()
2546
2547 *Asynchronous version*: ``acount()``
2548
2549 Returns an integer representing the number of objects in the database matching
2550 the ``QuerySet``.
2551
2552 Example::
2553
2554 # Returns the total number of entries in the database.
2555 Entry.objects.count()
2556
2557 # Returns the number of entries whose headline contains 'Lennon'
2558 Entry.objects.filter(headline__contains="Lennon").count()
2559
2560 A ``count()`` call performs a ``SELECT COUNT(*)`` behind the scenes, so you
2561 should always use ``count()`` rather than loading all of the record into Python
2562 objects and calling ``len()`` on the result (unless you need to load the
2563 objects into memory anyway, in which case ``len()`` will be faster).
2564
2565 Note that if you want the number of items in a ``QuerySet`` and are also
2566 retrieving model instances from it (for example, by iterating over it), it's
2567 probably more efficient to use ``len(queryset)`` which won't cause an extra
2568 database query like ``count()`` would.
2569
2570 If the queryset has already been fully retrieved, ``count()`` will use that
2571 length rather than perform an extra database query.
2572
2573 ``in_bulk()``
2574 ~~~~~~~~~~~~~
2575
2576 .. method:: in_bulk(id_list=None, *, field_name='pk')
2577 .. method:: ain_bulk(id_list=None, *, field_name='pk')
2578
2579 *Asynchronous version*: ``ain_bulk()``
2580
2581 Takes a list of field values (``id_list``) and the ``field_name`` for those
2582 values, and returns a dictionary mapping each value to an instance of the
2583 object with the given field value. No
2584 :exc:`django.core.exceptions.ObjectDoesNotExist` exceptions will ever be raised
2585 by ``in_bulk``; that is, any ``id_list`` value not matching any instance will
2586 simply be ignored. If ``id_list`` isn't provided, all objects
2587 in the queryset are returned. ``field_name`` must be a unique field or a
2588 distinct field (if there's only one field specified in :meth:`distinct`).
2589 ``field_name`` defaults to the primary key.
2590
2591 Example:
2592
2593 .. code-block:: pycon
2594
2595 >>> Blog.objects.in_bulk([1])
2596 {1: <Blog: Beatles Blog>}
2597 >>> Blog.objects.in_bulk([1, 2])
2598 {1: <Blog: Beatles Blog>, 2: <Blog: Cheddar Talk>}
2599 >>> Blog.objects.in_bulk([])
2600 {}
2601 >>> Blog.objects.in_bulk()
2602 {1: <Blog: Beatles Blog>, 2: <Blog: Cheddar Talk>, 3: <Blog: Django Weblog>}
2603 >>> Blog.objects.in_bulk(["beatles_blog"], field_name="slug")
2604 {'beatles_blog': <Blog: Beatles Blog>}
2605 >>> Blog.objects.distinct("name").in_bulk(field_name="name")
2606 {'Beatles Blog': <Blog: Beatles Blog>, 'Cheddar Talk': <Blog: Cheddar Talk>, 'Django Weblog': <Blog: Django Weblog>}
2607
2608 If you pass ``in_bulk()`` an empty list, you'll get an empty dictionary.
2609
2610 ``iterator()``
2611 ~~~~~~~~~~~~~~
2612
2613 .. method:: iterator(chunk_size=None)
2614 .. method:: aiterator(chunk_size=None)
2615
2616 *Asynchronous version*: ``aiterator()``
2617
2618 Evaluates the ``QuerySet`` (by performing the query) and returns an iterator
2619 (see :pep:`234`) over the results, or an asynchronous iterator (see :pep:`492`)
2620 if you call its asynchronous version ``aiterator``.
2621
2622 A ``QuerySet`` typically caches its results internally so that repeated
2623 evaluations do not result in additional queries. In contrast, ``iterator()``
2624 will read results directly, without doing any caching at the ``QuerySet`` level
2625 (internally, the default iterator calls ``iterator()`` and caches the return
2626 value). For a ``QuerySet`` which returns a large number of objects that you
2627 only need to access once, this can result in better performance and a
2628 significant reduction in memory.
2629
2630 Note that using ``iterator()`` on a ``QuerySet`` which has already been
2631 evaluated will force it to evaluate again, repeating the query.
2632
2633 ``iterator()`` is compatible with previous calls to ``prefetch_related()`` as
2634 long as ``chunk_size`` is given. Larger values will necessitate fewer queries
2635 to accomplish the prefetching at the cost of greater memory usage.
2636
2637 On some databases (e.g. Oracle, `SQLite
2638 <https://www.sqlite.org/limits.html#max_variable_number>`_), the maximum number
2639 of terms in an SQL ``IN`` clause might be limited. Hence values below this
2640 limit should be used. (In particular, when prefetching across two or more
2641 relations, a ``chunk_size`` should be small enough that the anticipated number
2642 of results for each prefetched relation still falls below the limit.)
2643
2644 So long as the QuerySet does not prefetch any related objects, providing no
2645 value for ``chunk_size`` will result in Django using an implicit default of
2646 2000.
2647
2648 Depending on the database backend, query results will either be loaded all at
2649 once or streamed from the database using server-side cursors.
2650
2651 With server-side cursors
2652 ^^^^^^^^^^^^^^^^^^^^^^^^
2653
2654 Oracle and :ref:`PostgreSQL <postgresql-server-side-cursors>` use server-side
2655 cursors to stream results from the database without loading the entire result
2656 set into memory.
2657
2658 The Oracle database driver always uses server-side cursors.
2659
2660 With server-side cursors, the ``chunk_size`` parameter specifies the number of
2661 results to cache at the database driver level. Fetching bigger chunks
2662 diminishes the number of round trips between the database driver and the
2663 database, at the expense of memory.
2664
2665 On PostgreSQL, server-side cursors will only be used when the
2666 :setting:`DISABLE_SERVER_SIDE_CURSORS <DATABASE-DISABLE_SERVER_SIDE_CURSORS>`
2667 setting is ``False``. Read :ref:`transaction-pooling-server-side-cursors` if
2668 you're using a connection pooler configured in transaction pooling mode. When
2669 server-side cursors are disabled, the behavior is the same as databases that
2670 don't support server-side cursors.
2671
2672 Without server-side cursors
2673 ^^^^^^^^^^^^^^^^^^^^^^^^^^^
2674
2675 MySQL doesn't support streaming results, hence the Python database driver loads
2676 the entire result set into memory. The result set is then transformed into
2677 Python row objects by the database adapter using the ``fetchmany()`` method
2678 defined in :pep:`249`.
2679
2680 SQLite can fetch results in batches using ``fetchmany()``, but since SQLite
2681 doesn't provide isolation between queries within a connection, be careful when
2682 writing to the table being iterated over. See :ref:`sqlite-isolation` for
2683 more information.
2684
2685 The ``chunk_size`` parameter controls the size of batches Django retrieves from
2686 the database driver. Larger batches decrease the overhead of communicating with
2687 the database driver at the expense of a slight increase in memory consumption.
2688
2689 So long as the QuerySet does not prefetch any related objects, providing no
2690 value for ``chunk_size`` will result in Django using an implicit default of
2691 2000, a value derived from `a calculation on the psycopg mailing list
2692 <https://www.postgresql.org/message-id/4D2F2C71.8080805%40dndg.it>`_:
2693
2694 Assuming rows of 10-20 columns with a mix of textual and numeric data, 2000
2695 is going to fetch less than 100KB of data, which seems a good compromise
2696 between the number of rows transferred and the data discarded if the loop
2697 is exited early.
2698
2699 ``latest()``
2700 ~~~~~~~~~~~~
2701
2702 .. method:: latest(*fields)
2703 .. method:: alatest(*fields)
2704
2705 *Asynchronous version*: ``alatest()``
2706
2707 Returns the latest object in the table based on the given field(s).
2708
2709 This example returns the latest ``Entry`` in the table, according to the
2710 ``pub_date`` field::
2711
2712 Entry.objects.latest("pub_date")
2713
2714 You can also choose the latest based on several fields. For example, to select
2715 the ``Entry`` with the earliest ``expire_date`` when two entries have the same
2716 ``pub_date``::
2717
2718 Entry.objects.latest("pub_date", "-expire_date")
2719
2720 The negative sign in ``'-expire_date'`` means to sort ``expire_date`` in
2721 *descending* order. Since ``latest()`` gets the last result, the ``Entry`` with
2722 the earliest ``expire_date`` is selected.
2723
2724 If your model's :ref:`Meta <meta-options>` specifies
2725 :attr:`~django.db.models.Options.get_latest_by`, you can omit any arguments to
2726 ``earliest()`` or ``latest()``. The fields specified in
2727 :attr:`~django.db.models.Options.get_latest_by` will be used by default.
2728
2729 Like :meth:`get()`, ``earliest()`` and ``latest()`` raise
2730 :exc:`~django.db.models.Model.DoesNotExist` if there is no object with the
2731 given parameters.
2732
2733 Note that ``earliest()`` and ``latest()`` exist purely for convenience and
2734 readability.
2735
2736 .. admonition:: ``earliest()`` and ``latest()`` may return instances with null dates.
2737
2738 Since ordering is delegated to the database, results on fields that allow
2739 null values may be ordered differently if you use different databases. For
2740 example, PostgreSQL and MySQL sort null values as if they are higher than
2741 non-null values, while SQLite does the opposite.
2742
2743 You may want to filter out null values::
2744
2745 Entry.objects.filter(pub_date__isnull=False).latest("pub_date")
2746
2747 ``earliest()``
2748 ~~~~~~~~~~~~~~
2749
2750 .. method:: earliest(*fields)
2751 .. method:: aearliest(*fields)
2752
2753 *Asynchronous version*: ``aearliest()``
2754
2755 Works otherwise like :meth:`~django.db.models.query.QuerySet.latest` except
2756 the direction is changed.
2757
2758 ``first()``
2759 ~~~~~~~~~~~
2760
2761 .. method:: first()
2762 .. method:: afirst()
2763
2764 *Asynchronous version*: ``afirst()``
2765
2766 Returns the first object matched by the queryset, or ``None`` if there
2767 is no matching object. If the ``QuerySet`` has no ordering defined, then the
2768 queryset is automatically ordered by the primary key. This can affect
2769 aggregation results as described in :ref:`aggregation-ordering-interaction`.
2770
2771 Example::
2772
2773 p = Article.objects.order_by("title", "pub_date").first()
2774
2775 Note that ``first()`` is a convenience method, the following code sample is
2776 equivalent to the above example::
2777
2778 try:
2779 p = Article.objects.order_by("title", "pub_date")[0]
2780 except IndexError:
2781 p = None
2782
2783 ``last()``
2784 ~~~~~~~~~~
2785
2786 .. method:: last()
2787 .. method:: alast()
2788
2789 *Asynchronous version*: ``alast()``
2790
2791 Works like :meth:`first()`, but returns the last object in the queryset.
2792
2793 ``aggregate()``
2794 ~~~~~~~~~~~~~~~
2795
2796 .. method:: aggregate(*args, **kwargs)
2797 .. method:: aaggregate(*args, **kwargs)
2798
2799 *Asynchronous version*: ``aaggregate()``
2800
2801 Returns a dictionary of aggregate values (averages, sums, etc.) calculated over
2802 the ``QuerySet``. Each argument to ``aggregate()`` specifies a value that will
2803 be included in the dictionary that is returned.
2804
2805 The aggregation functions that are provided by Django are described in
2806 `Aggregation Functions`_ below. Since aggregates are also :doc:`query
2807 expressions </ref/models/expressions>`, you may combine aggregates with other
2808 aggregates or values to create complex aggregates.
2809
2810 Aggregates specified using keyword arguments will use the keyword as the name
2811 for the annotation. Anonymous arguments will have a name generated for them
2812 based upon the name of the aggregate function and the model field that is being
2813 aggregated. Complex aggregates cannot use anonymous arguments and must specify
2814 a keyword argument as an alias.
2815
2816 For example, when you are working with blog entries, you may want to know the
2817 number of authors that have contributed blog entries:
2818
2819 .. code-block:: pycon
2820
2821 >>> from django.db.models import Count
2822 >>> Blog.objects.aggregate(Count("entry__authors"))
2823 {'entry__authors__count': 16}
2824
2825 By using a keyword argument to specify the aggregate function, you can
2826 control the name of the aggregation value that is returned:
2827
2828 .. code-block:: pycon
2829
2830 >>> Blog.objects.aggregate(number_of_authors=Count("entry__authors"))
2831 {'number_of_authors': 16}
2832
2833 For an in-depth discussion of aggregation, see :doc:`the topic guide on
2834 Aggregation </topics/db/aggregation>`.
2835
2836 ``exists()``
2837 ~~~~~~~~~~~~
2838
2839 .. method:: exists()
2840 .. method:: aexists()
2841
2842 *Asynchronous version*: ``aexists()``
2843
2844 Returns ``True`` if the :class:`.QuerySet` contains any results, and ``False``
2845 if not. This tries to perform the query in the simplest and fastest way
2846 possible, but it *does* execute nearly the same query as a normal
2847 :class:`.QuerySet` query.
2848
2849 :meth:`~.QuerySet.exists` is useful for searches relating to the existence of
2850 any objects in a :class:`.QuerySet`, particularly in the context of a large
2851 :class:`.QuerySet`.
2852
2853 To find whether a queryset contains any items::
2854
2855 if some_queryset.exists():
2856 print("There is at least one object in some_queryset")
2857
2858 Which will be faster than::
2859
2860 if some_queryset:
2861 print("There is at least one object in some_queryset")
2862
2863 ... but not by a large degree (hence needing a large queryset for efficiency
2864 gains).
2865
2866 Additionally, if a ``some_queryset`` has not yet been evaluated, but you know
2867 that it will be at some point, then using ``some_queryset.exists()`` will do
2868 more overall work (one query for the existence check plus an extra one to later
2869 retrieve the results) than using ``bool(some_queryset)``, which retrieves the
2870 results and then checks if any were returned.
2871
2872 ``contains()``
2873 ~~~~~~~~~~~~~~
2874
2875 .. method:: contains(obj)
2876 .. method:: acontains(obj)
2877
2878 *Asynchronous version*: ``acontains()``
2879
2880 Returns ``True`` if the :class:`.QuerySet` contains ``obj``, and ``False`` if
2881 not. This tries to perform the query in the simplest and fastest way possible.
2882
2883 :meth:`contains` is useful for checking an object membership in a
2884 :class:`.QuerySet`, particularly in the context of a large :class:`.QuerySet`.
2885
2886 To check whether a queryset contains a specific item::
2887
2888 if some_queryset.contains(obj):
2889 print("Entry contained in queryset")
2890
2891 This will be faster than the following which requires evaluating and iterating
2892 through the entire queryset::
2893
2894 if obj in some_queryset:
2895 print("Entry contained in queryset")
2896
2897 Like :meth:`exists`, if ``some_queryset`` has not yet been evaluated, but you
2898 know that it will be at some point, then using ``some_queryset.contains(obj)``
2899 will make an additional database query, generally resulting in slower overall
2900 performance.
2901
2902 ``update()``
2903 ~~~~~~~~~~~~
2904
2905 .. method:: update(**kwargs)
2906 .. method:: aupdate(**kwargs)
2907
2908 *Asynchronous version*: ``aupdate()``
2909
2910 Performs an SQL update query for the specified fields, and returns
2911 the number of rows matched (which may not be equal to the number of rows
2912 updated if some rows already have the new value).
2913
2914 For example, to turn comments off for all blog entries published in 2010,
2915 you could do this:
2916
2917 .. code-block:: pycon
2918
2919 >>> Entry.objects.filter(pub_date__year=2010).update(comments_on=False)
2920
2921 (This assumes your ``Entry`` model has fields ``pub_date`` and ``comments_on``.)
2922
2923 You can update multiple fields — there's no limit on how many. For example,
2924 here we update the ``comments_on`` and ``headline`` fields:
2925
2926 .. code-block:: pycon
2927
2928 >>> Entry.objects.filter(pub_date__year=2010).update(
2929 ... comments_on=False, headline="This is old"
2930 ... )
2931
2932 The ``update()`` method is applied instantly, and the only restriction on the
2933 :class:`.QuerySet` that is updated is that it can only update columns in the
2934 model's main table, not on related models. You can't do this, for example:
2935
2936 .. code-block:: pycon
2937
2938 >>> Entry.objects.update(blog__name="foo") # Won't work!
2939
2940 Filtering based on related fields is still possible, though:
2941
2942 .. code-block:: pycon
2943
2944 >>> Entry.objects.filter(blog__id=1).update(comments_on=True)
2945
2946 You cannot call ``update()`` on a :class:`.QuerySet` that has had a slice taken
2947 or can otherwise no longer be filtered.
2948
2949 The ``update()`` method returns the number of affected rows:
2950
2951 .. code-block:: pycon
2952
2953 >>> Entry.objects.filter(id=64).update(comments_on=True)
2954 1
2955
2956 >>> Entry.objects.filter(slug="nonexistent-slug").update(comments_on=True)
2957 0
2958
2959 >>> Entry.objects.filter(pub_date__year=2010).update(comments_on=False)
2960 132
2961
2962 If you're just updating a record and don't need to do anything with the model
2963 object, the most efficient approach is to call ``update()``, rather than
2964 loading the model object into memory. For example, instead of doing this::
2965
2966 e = Entry.objects.get(id=10)
2967 e.comments_on = False
2968 e.save()
2969
2970 ...do this::
2971
2972 Entry.objects.filter(id=10).update(comments_on=False)
2973
2974 Using ``update()`` also prevents a race condition wherein something might
2975 change in your database in the short period of time between loading the object
2976 and calling ``save()``.
2977
2978 .. admonition:: MySQL does not support self-select updates
2979
2980 On MySQL, ``QuerySet.update()`` may execute a ``SELECT`` followed by an
2981 ``UPDATE`` instead of a single ``UPDATE`` when filtering on related tables,
2982 which can introduce a race condition if concurrent changes occur between
2983 the queries. To ensure atomicity, consider using transactions or avoiding
2984 such filter conditions on MySQL.
2985
2986 Finally, realize that ``update()`` does an update at the SQL level and, thus,
2987 does not call any ``save()`` methods on your models, nor does it emit the
2988 :attr:`~django.db.models.signals.pre_save` or
2989 :attr:`~django.db.models.signals.post_save` signals (which are a consequence of
2990 calling :meth:`Model.save() <django.db.models.Model.save>`). If you want to
2991 update a bunch of records for a model that has a custom
2992 :meth:`~django.db.models.Model.save()` method, loop over them and call
2993 :meth:`~django.db.models.Model.save()`, like this::
2994
2995 for e in Entry.objects.filter(pub_date__year=2010):
2996 e.comments_on = False
2997 e.save()
2998
2999 Ordered queryset
3000 ^^^^^^^^^^^^^^^^
3001
3002 Chaining ``order_by()`` with ``update()`` is supported only on MariaDB and
3003 MySQL, and is ignored for different databases. This is useful for updating a
3004 unique field in the order that is specified without conflicts. For example::
3005
3006 Entry.objects.order_by("-number").update(number=F("number") + 1)
3007
3008 .. note::
3009
3010 ``order_by()`` clause will be ignored if it contains annotations, inherited
3011 fields, or lookups spanning relations.
3012
3013 ``delete()``
3014 ~~~~~~~~~~~~
3015
3016 .. method:: delete()
3017 .. method:: adelete()
3018
3019 *Asynchronous version*: ``adelete()``
3020
3021 Performs an SQL delete query on all rows in the :class:`.QuerySet` and
3022 returns the number of objects deleted and a dictionary with the number of
3023 deletions per object type.
3024
3025 The ``delete()`` is applied instantly. You cannot call ``delete()`` on a
3026 :class:`.QuerySet` that has had a slice taken or can otherwise no longer be
3027 filtered.
3028
3029 For example, to delete all the entries in a particular blog:
3030
3031 .. code-block:: pycon
3032
3033 >>> b = Blog.objects.get(pk=1)
3034
3035 # Delete all the entries belonging to this Blog.
3036 >>> Entry.objects.filter(blog=b).delete()
3037 (4, {'blog.Entry': 2, 'blog.Entry_authors': 2})
3038
3039 By default, Django's :class:`~django.db.models.ForeignKey` emulates the SQL
3040 constraint ``ON DELETE CASCADE`` — in other words, any objects with foreign
3041 keys pointing at the objects to be deleted will be deleted along with them.
3042 For example:
3043
3044 .. code-block:: pycon
3045
3046 >>> blogs = Blog.objects.all()
3047
3048 # This will delete all Blogs and all of their Entry objects.
3049 >>> blogs.delete()
3050 (5, {'blog.Blog': 1, 'blog.Entry': 2, 'blog.Entry_authors': 2})
3051
3052 This cascade behavior is customizable via the
3053 :attr:`~django.db.models.ForeignKey.on_delete` argument to the
3054 :class:`~django.db.models.ForeignKey`.
3055
3056 The ``delete()`` method does a bulk delete and does not call any ``delete()``
3057 methods on your models. It does, however, emit the
3058 :data:`~django.db.models.signals.pre_delete` and
3059 :data:`~django.db.models.signals.post_delete` signals for all deleted objects
3060 (including cascaded deletions).
3061
3062 Django needs to fetch objects into memory to send signals and handle cascades.
3063 However, if there are no cascades and no signals, then Django may take a
3064 fast-path and delete objects without fetching into memory. For large
3065 deletes this can result in significantly reduced memory usage. The amount of
3066 executed queries can be reduced, too.
3067
3068 ForeignKeys which are set to :attr:`~django.db.models.ForeignKey.on_delete`
3069 ``DO_NOTHING`` do not prevent taking the fast-path in deletion.
3070
3071 Note that the queries generated in object deletion is an implementation
3072 detail subject to change.
3073
3074 ``as_manager()``
3075 ~~~~~~~~~~~~~~~~
3076
3077 .. classmethod:: as_manager()
3078
3079 Class method that returns an instance of :class:`~django.db.models.Manager`
3080 with a copy of the ``QuerySet``’s methods. See
3081 :ref:`create-manager-with-queryset-methods` for more details.
3082
3083 Note that unlike the other entries in this section, this does not have an
3084 asynchronous variant as it does not execute a query.
3085
3086 ``explain()``
3087 ~~~~~~~~~~~~~
3088
3089 .. method:: explain(format=None, **options)
3090 .. method:: aexplain(format=None, **options)
3091
3092 *Asynchronous version*: ``aexplain()``
3093
3094 Returns a string of the ``QuerySet``’s execution plan, which details how the
3095 database would execute the query, including any indexes or joins that would be
3096 used. Knowing these details may help you improve the performance of slow
3097 queries.
3098
3099 For example, when using PostgreSQL:
3100
3101 .. code-block:: pycon
3102
3103 >>> print(Blog.objects.filter(title="My Blog").explain())
3104 Seq Scan on blog (cost=0.00..35.50 rows=10 width=12)
3105 Filter: (title = 'My Blog'::bpchar)
3106
3107 The output differs significantly between databases.
3108
3109 ``explain()`` is supported by all built-in database backends except Oracle
3110 because an implementation there isn't straightforward.
3111
3112 The ``format`` parameter changes the output format from the databases's
3113 default, which is usually text-based. PostgreSQL supports ``'TEXT'``,
3114 ``'JSON'``, ``'YAML'``, and ``'XML'`` formats. MariaDB and MySQL support
3115 ``'TEXT'`` (also called ``'TRADITIONAL'``) and ``'JSON'`` formats. MySQL
3116 8.0.16+ also supports an improved ``'TREE'`` format, which is similar to
3117 PostgreSQL's ``'TEXT'`` output and is used by default, if supported.
3118
3119 Some databases accept flags that can return more information about the query.
3120 Pass these flags as keyword arguments. For example, when using PostgreSQL:
3121
3122 .. code-block:: pycon
3123
3124 >>> print(Blog.objects.filter(title="My Blog").explain(verbose=True, analyze=True))
3125 Seq Scan on public.blog (cost=0.00..35.50 rows=10 width=12) (actual time=0.004..0.004 rows=10 loops=1)
3126 Output: id, title
3127 Filter: (blog.title = 'My Blog'::bpchar)
3128 Planning time: 0.064 ms
3129 Execution time: 0.058 ms
3130
3131 On some databases, flags may cause the query to be executed which could have
3132 adverse effects on your database. For example, the ``ANALYZE`` flag supported
3133 by MariaDB, MySQL 8.0.18+, and PostgreSQL could result in changes to data if
3134 there are triggers or if a function is called, even for a ``SELECT`` query.
3135
3136 .. versionchanged:: 5.1
3137
3138 Support for the ``generic_plan`` option on PostgreSQL 16+ was added.
3139
3140 .. versionchanged:: 5.2
3141
3142 Support for the ``memory`` and ``serialize`` options on PostgreSQL 17+ was
3143 added.
3144
3145 .. _field-lookups:
3146
3147 ``Field`` lookups
3148 -----------------
3149
3150 Field lookups are how you specify the meat of an SQL ``WHERE`` clause. They're
3151 specified as keyword arguments to the ``QuerySet`` methods :meth:`filter()`,
3152 :meth:`exclude()` and :meth:`get()`.
3153
3154 For an introduction, see :ref:`models and database queries documentation
3155 <field-lookups-intro>`.
3156
3157 Django's built-in lookups are listed below. It is also possible to write
3158 :doc:`custom lookups </howto/custom-lookups>` for model fields.
3159
3160 As a convenience when no lookup type is provided (like in
3161 ``Entry.objects.get(id=14)``) the lookup type is assumed to be :lookup:`exact`.
3162
3163 .. fieldlookup:: exact
3164
3165 ``exact``
3166 ~~~~~~~~~
3167
3168 Exact match. If the value provided for comparison is ``None``, it will be
3169 interpreted as an SQL ``NULL`` (see :lookup:`isnull` for more details).
3170
3171 Examples::
3172
3173 Entry.objects.get(id__exact=14)
3174 Entry.objects.get(id__exact=None)
3175
3176 SQL equivalents:
3177
3178 .. code-block:: sql
3179
3180 SELECT ... WHERE id = 14;
3181 SELECT ... WHERE id IS NULL;
3182
3183 .. admonition:: MySQL comparisons
3184
3185 In MySQL, a database table's "collation" setting determines whether
3186 ``exact`` comparisons are case-sensitive. This is a database setting, *not*
3187 a Django setting. It's possible to configure your MySQL tables to use
3188 case-sensitive comparisons, but some trade-offs are involved. For more
3189 information about this, see the :ref:`collation section <mysql-collation>`
3190 in the :doc:`databases </ref/databases>` documentation.
3191
3192 .. fieldlookup:: iexact
3193
3194 ``iexact``
3195 ~~~~~~~~~~
3196
3197 Case-insensitive exact match. If the value provided for comparison is ``None``,
3198 it will be interpreted as an SQL ``NULL`` (see :lookup:`isnull` for more
3199 details).
3200
3201 Example::
3202
3203 Blog.objects.get(name__iexact="beatles blog")
3204 Blog.objects.get(name__iexact=None)
3205
3206 SQL equivalents:
3207
3208 .. code-block:: sql
3209
3210 SELECT ... WHERE name ILIKE 'beatles blog';
3211 SELECT ... WHERE name IS NULL;
3212
3213 Note the first query will match ``'Beatles Blog'``, ``'beatles blog'``,
3214 ``'BeAtLes BLoG'``, etc.
3215
3216 .. admonition:: SQLite users
3217
3218 When using the SQLite backend and non-ASCII strings, bear in mind the
3219 :ref:`database note <sqlite-string-matching>` about string comparisons.
3220 SQLite does not do case-insensitive matching for non-ASCII strings.
3221
3222 .. fieldlookup:: contains
3223
3224 ``contains``
3225 ~~~~~~~~~~~~
3226
3227 Case-sensitive containment test.
3228
3229 Example::
3230
3231 Entry.objects.get(headline__contains="Lennon")
3232
3233 SQL equivalent:
3234
3235 .. code-block:: sql
3236
3237 SELECT ... WHERE headline LIKE '%Lennon%';
3238
3239 Note this will match the headline ``'Lennon honored today'`` but not ``'lennon
3240 honored today'``.
3241
3242 .. admonition:: SQLite users
3243
3244 SQLite doesn't support case-sensitive ``LIKE`` statements; ``contains``
3245 acts like ``icontains`` for SQLite. See the :ref:`database note
3246 <sqlite-string-matching>` for more information.
3247
3248
3249 .. fieldlookup:: icontains
3250
3251 ``icontains``
3252 ~~~~~~~~~~~~~
3253
3254 Case-insensitive containment test.
3255
3256 Example::
3257
3258 Entry.objects.get(headline__icontains="Lennon")
3259
3260 SQL equivalent:
3261
3262 .. code-block:: sql
3263
3264 SELECT ... WHERE headline ILIKE '%Lennon%';
3265
3266 .. admonition:: SQLite users
3267
3268 When using the SQLite backend and non-ASCII strings, bear in mind the
3269 :ref:`database note <sqlite-string-matching>` about string comparisons.
3270
3271 .. fieldlookup:: in
3272
3273 ``in``
3274 ~~~~~~
3275
3276 In a given iterable; often a list, tuple, or queryset. It's not a common use
3277 case, but strings (being iterables) are accepted.
3278
3279 Examples::
3280
3281 Entry.objects.filter(id__in=[1, 3, 4])
3282 Entry.objects.filter(headline__in="abc")
3283
3284 SQL equivalents:
3285
3286 .. code-block:: sql
3287
3288 SELECT ... WHERE id IN (1, 3, 4);
3289 SELECT ... WHERE headline IN ('a', 'b', 'c');
3290
3291 You can also use a queryset to dynamically evaluate the list of values
3292 instead of providing a list of literal values::
3293
3294 inner_qs = Blog.objects.filter(name__contains="Cheddar")
3295 entries = Entry.objects.filter(blog__in=inner_qs)
3296
3297 This queryset will be evaluated as subselect statement:
3298
3299 .. code-block:: sql
3300
3301 SELECT ... WHERE blog.id IN (SELECT id FROM ... WHERE NAME LIKE '%Cheddar%')
3302
3303 If you pass in a ``QuerySet`` resulting from ``values()`` or ``values_list()``
3304 as the value to an ``__in`` lookup, you need to ensure you are only extracting
3305 one field in the result. For example, this will work (filtering on the blog
3306 names)::
3307
3308 inner_qs = Blog.objects.filter(name__contains="Ch").values("name")
3309 entries = Entry.objects.filter(blog__name__in=inner_qs)
3310
3311 This example will raise an exception, since the inner query is trying to
3312 extract two field values, where only one is expected::
3313
3314 # Bad code! Will raise a TypeError.
3315 inner_qs = Blog.objects.filter(name__contains="Ch").values("name", "id")
3316 entries = Entry.objects.filter(blog__name__in=inner_qs)
3317
3318 .. _nested-queries-performance:
3319
3320 .. admonition:: Performance considerations
3321
3322 Be cautious about using nested queries and understand your database
3323 server's performance characteristics (if in doubt, benchmark!). Some
3324 database backends, most notably MySQL, don't optimize nested queries very
3325 well. It is more efficient, in those cases, to extract a list of values
3326 and then pass that into the second query. That is, execute two queries
3327 instead of one::
3328
3329 values = Blog.objects.filter(name__contains="Cheddar").values_list("pk", flat=True)
3330 entries = Entry.objects.filter(blog__in=list(values))
3331
3332 Note the ``list()`` call around the Blog ``QuerySet`` to force execution of
3333 the first query. Without it, a nested query would be executed, because
3334 :ref:`querysets-are-lazy`.
3335
3336 .. fieldlookup:: gt
3337
3338 ``gt``
3339 ~~~~~~
3340
3341 Greater than.
3342
3343 Example::
3344
3345 Entry.objects.filter(id__gt=4)
3346
3347 SQL equivalent:
3348
3349 .. code-block:: sql
3350
3351 SELECT ... WHERE id > 4;
3352
3353 .. fieldlookup:: gte
3354
3355 ``gte``
3356 ~~~~~~~
3357
3358 Greater than or equal to.
3359
3360 .. fieldlookup:: lt
3361
3362 ``lt``
3363 ~~~~~~
3364
3365 Less than.
3366
3367 .. fieldlookup:: lte
3368
3369 ``lte``
3370 ~~~~~~~
3371
3372 Less than or equal to.
3373
3374 .. fieldlookup:: startswith
3375
3376 ``startswith``
3377 ~~~~~~~~~~~~~~
3378
3379 Case-sensitive starts-with.
3380
3381 Example::
3382
3383 Entry.objects.filter(headline__startswith="Lennon")
3384
3385 SQL equivalent:
3386
3387 .. code-block:: sql
3388
3389 SELECT ... WHERE headline LIKE 'Lennon%';
3390
3391 SQLite doesn't support case-sensitive ``LIKE`` statements; ``startswith`` acts
3392 like ``istartswith`` for SQLite.
3393
3394 .. fieldlookup:: istartswith
3395
3396 ``istartswith``
3397 ~~~~~~~~~~~~~~~
3398
3399 Case-insensitive starts-with.
3400
3401 Example::
3402
3403 Entry.objects.filter(headline__istartswith="Lennon")
3404
3405 SQL equivalent:
3406
3407 .. code-block:: sql
3408
3409 SELECT ... WHERE headline ILIKE 'Lennon%';
3410
3411 .. admonition:: SQLite users
3412
3413 When using the SQLite backend and non-ASCII strings, bear in mind the
3414 :ref:`database note <sqlite-string-matching>` about string comparisons.
3415
3416 .. fieldlookup:: endswith
3417
3418 ``endswith``
3419 ~~~~~~~~~~~~
3420
3421 Case-sensitive ends-with.
3422
3423 Example::
3424
3425 Entry.objects.filter(headline__endswith="Lennon")
3426
3427 SQL equivalent:
3428
3429 .. code-block:: sql
3430
3431 SELECT ... WHERE headline LIKE '%Lennon';
3432
3433 .. admonition:: SQLite users
3434
3435 SQLite doesn't support case-sensitive ``LIKE`` statements; ``endswith``
3436 acts like ``iendswith`` for SQLite. Refer to the :ref:`database note
3437 <sqlite-string-matching>` documentation for more.
3438
3439 .. fieldlookup:: iendswith
3440
3441 ``iendswith``
3442 ~~~~~~~~~~~~~
3443
3444 Case-insensitive ends-with.
3445
3446 Example::
3447
3448 Entry.objects.filter(headline__iendswith="Lennon")
3449
3450 SQL equivalent:
3451
3452 .. code-block:: sql
3453
3454 SELECT ... WHERE headline ILIKE '%Lennon'
3455
3456 .. admonition:: SQLite users
3457
3458 When using the SQLite backend and non-ASCII strings, bear in mind the
3459 :ref:`database note <sqlite-string-matching>` about string comparisons.
3460
3461 .. fieldlookup:: range
3462
3463 ``range``
3464 ~~~~~~~~~
3465
3466 Range test (inclusive).
3467
3468 Example::
3469
3470 import datetime
3471
3472 start_date = datetime.date(2005, 1, 1)
3473 end_date = datetime.date(2005, 3, 31)
3474 Entry.objects.filter(pub_date__range=(start_date, end_date))
3475
3476 SQL equivalent:
3477
3478 .. code-block:: sql
3479
3480 SELECT ... WHERE pub_date BETWEEN '2005-01-01' and '2005-03-31';
3481
3482 You can use ``range`` anywhere you can use ``BETWEEN`` in SQL — for dates,
3483 numbers and even characters.
3484
3485 .. warning::
3486
3487 Filtering a ``DateTimeField`` with dates won't include items on the last
3488 day, because the bounds are interpreted as "0am on the given date". If
3489 ``pub_date`` was a ``DateTimeField``, the above expression would be turned
3490 into this SQL:
3491
3492 .. code-block:: sql
3493
3494 SELECT ... WHERE pub_date BETWEEN '2005-01-01 00:00:00' and '2005-03-31 00:00:00';
3495
3496 Generally speaking, you can't mix dates and datetimes.
3497
3498 .. fieldlookup:: date
3499
3500 ``date``
3501 ~~~~~~~~
3502
3503 For datetime fields, casts the value as date. Allows chaining additional field
3504 lookups. Takes a date value.
3505
3506 Example::
3507
3508 Entry.objects.filter(pub_date__date=datetime.date(2005, 1, 1))
3509 Entry.objects.filter(pub_date__date__gt=datetime.date(2005, 1, 1))
3510
3511 (No equivalent SQL code fragment is included for this lookup because
3512 implementation of the relevant query varies among different database engines.)
3513
3514 When :setting:`USE_TZ` is ``True``, fields are converted to the current time
3515 zone before filtering. This requires :ref:`time zone definitions in the
3516 database <database-time-zone-definitions>`.
3517
3518 .. fieldlookup:: year
3519
3520 ``year``
3521 ~~~~~~~~
3522
3523 For date and datetime fields, an exact year match. Allows chaining additional
3524 field lookups. Takes an integer year.
3525
3526 Example::
3527
3528 Entry.objects.filter(pub_date__year=2005)
3529 Entry.objects.filter(pub_date__year__gte=2005)
3530
3531 SQL equivalent:
3532
3533 .. code-block:: sql
3534
3535 SELECT ... WHERE pub_date BETWEEN '2005-01-01' AND '2005-12-31';
3536 SELECT ... WHERE pub_date >= '2005-01-01';
3537
3538 (The exact SQL syntax varies for each database engine.)
3539
3540 When :setting:`USE_TZ` is ``True``, datetime fields are converted to the
3541 current time zone before filtering. This requires :ref:`time zone definitions
3542 in the database <database-time-zone-definitions>`.
3543
3544 .. fieldlookup:: iso_year
3545
3546 ``iso_year``
3547 ~~~~~~~~~~~~
3548
3549 For date and datetime fields, an exact ISO 8601 week-numbering year match.
3550 Allows chaining additional field lookups. Takes an integer year.
3551
3552 Example::
3553
3554 Entry.objects.filter(pub_date__iso_year=2005)
3555 Entry.objects.filter(pub_date__iso_year__gte=2005)
3556
3557 (The exact SQL syntax varies for each database engine.)
3558
3559 When :setting:`USE_TZ` is ``True``, datetime fields are converted to the
3560 current time zone before filtering. This requires :ref:`time zone definitions
3561 in the database <database-time-zone-definitions>`.
3562
3563 .. fieldlookup:: month
3564
3565 ``month``
3566 ~~~~~~~~~
3567
3568 For date and datetime fields, an exact month match. Allows chaining additional
3569 field lookups. Takes an integer 1 (January) through 12 (December).
3570
3571 Example::
3572
3573 Entry.objects.filter(pub_date__month=12)
3574 Entry.objects.filter(pub_date__month__gte=6)
3575
3576 SQL equivalent:
3577
3578 .. code-block:: sql
3579
3580 SELECT ... WHERE EXTRACT('month' FROM pub_date) = '12';
3581 SELECT ... WHERE EXTRACT('month' FROM pub_date) >= '6';
3582
3583 (The exact SQL syntax varies for each database engine.)
3584
3585 When :setting:`USE_TZ` is ``True``, datetime fields are converted to the
3586 current time zone before filtering. This requires :ref:`time zone definitions
3587 in the database <database-time-zone-definitions>`.
3588
3589 .. fieldlookup:: day
3590
3591 ``day``
3592 ~~~~~~~
3593
3594 For date and datetime fields, an exact day match. Allows chaining additional
3595 field lookups. Takes an integer day.
3596
3597 Example::
3598
3599 Entry.objects.filter(pub_date__day=3)
3600 Entry.objects.filter(pub_date__day__gte=3)
3601
3602 SQL equivalent:
3603
3604 .. code-block:: sql
3605
3606 SELECT ... WHERE EXTRACT('day' FROM pub_date) = '3';
3607 SELECT ... WHERE EXTRACT('day' FROM pub_date) >= '3';
3608
3609 (The exact SQL syntax varies for each database engine.)
3610
3611 Note this will match any record with a pub_date on the third day of the month,
3612 such as January 3, July 3, etc.
3613
3614 When :setting:`USE_TZ` is ``True``, datetime fields are converted to the
3615 current time zone before filtering. This requires :ref:`time zone definitions
3616 in the database <database-time-zone-definitions>`.
3617
3618 .. fieldlookup:: week
3619
3620 ``week``
3621 ~~~~~~~~
3622
3623 For date and datetime fields, return the week number (1-52 or 53) according
3624 to `ISO-8601 <https://en.wikipedia.org/wiki/ISO-8601>`_, i.e., weeks start
3625 on a Monday and the first week contains the year's first Thursday.
3626
3627 Example::
3628
3629 Entry.objects.filter(pub_date__week=52)
3630 Entry.objects.filter(pub_date__week__gte=32, pub_date__week__lte=38)
3631
3632 (No equivalent SQL code fragment is included for this lookup because
3633 implementation of the relevant query varies among different database engines.)
3634
3635 When :setting:`USE_TZ` is ``True``, datetime fields are converted to the
3636 current time zone before filtering. This requires :ref:`time zone definitions
3637 in the database <database-time-zone-definitions>`.
3638
3639 .. fieldlookup:: week_day
3640
3641 ``week_day``
3642 ~~~~~~~~~~~~
3643
3644 For date and datetime fields, a 'day of the week' match. Allows chaining
3645 additional field lookups.
3646
3647 Takes an integer value representing the day of week from 1 (Sunday) to 7
3648 (Saturday).
3649
3650 Example::
3651
3652 Entry.objects.filter(pub_date__week_day=2)
3653 Entry.objects.filter(pub_date__week_day__gte=2)
3654
3655 (No equivalent SQL code fragment is included for this lookup because
3656 implementation of the relevant query varies among different database engines.)
3657
3658 Note this will match any record with a ``pub_date`` that falls on a Monday (day
3659 2 of the week), regardless of the month or year in which it occurs. Week days
3660 are indexed with day 1 being Sunday and day 7 being Saturday.
3661
3662 When :setting:`USE_TZ` is ``True``, datetime fields are converted to the
3663 current time zone before filtering. This requires :ref:`time zone definitions
3664 in the database <database-time-zone-definitions>`.
3665
3666 .. fieldlookup:: iso_week_day
3667
3668 ``iso_week_day``
3669 ~~~~~~~~~~~~~~~~
3670
3671 For date and datetime fields, an exact ISO 8601 day of the week match. Allows
3672 chaining additional field lookups.
3673
3674 Takes an integer value representing the day of the week from 1 (Monday) to 7
3675 (Sunday).
3676
3677 Example::
3678
3679 Entry.objects.filter(pub_date__iso_week_day=1)
3680 Entry.objects.filter(pub_date__iso_week_day__gte=1)
3681
3682 (No equivalent SQL code fragment is included for this lookup because
3683 implementation of the relevant query varies among different database engines.)
3684
3685 Note this will match any record with a ``pub_date`` that falls on a Monday (day
3686 1 of the week), regardless of the month or year in which it occurs. Week days
3687 are indexed with day 1 being Monday and day 7 being Sunday.
3688
3689 When :setting:`USE_TZ` is ``True``, datetime fields are converted to the
3690 current time zone before filtering. This requires :ref:`time zone definitions
3691 in the database <database-time-zone-definitions>`.
3692
3693 .. fieldlookup:: quarter
3694
3695 ``quarter``
3696 ~~~~~~~~~~~
3697
3698 For date and datetime fields, a 'quarter of the year' match. Allows chaining
3699 additional field lookups. Takes an integer value between 1 and 4 representing
3700 the quarter of the year.
3701
3702 Example to retrieve entries in the second quarter (April 1 to June 30)::
3703
3704 Entry.objects.filter(pub_date__quarter=2)
3705
3706 (No equivalent SQL code fragment is included for this lookup because
3707 implementation of the relevant query varies among different database engines.)
3708
3709 When :setting:`USE_TZ` is ``True``, datetime fields are converted to the
3710 current time zone before filtering. This requires :ref:`time zone definitions
3711 in the database <database-time-zone-definitions>`.
3712
3713 .. fieldlookup:: time
3714
3715 ``time``
3716 ~~~~~~~~
3717
3718 For datetime fields, casts the value as time. Allows chaining additional field
3719 lookups. Takes a :class:`datetime.time` value.
3720
3721 Example::
3722
3723 Entry.objects.filter(pub_date__time=datetime.time(14, 30))
3724 Entry.objects.filter(pub_date__time__range=(datetime.time(8), datetime.time(17)))
3725
3726 (No equivalent SQL code fragment is included for this lookup because
3727 implementation of the relevant query varies among different database engines.)
3728
3729 When :setting:`USE_TZ` is ``True``, fields are converted to the current time
3730 zone before filtering. This requires :ref:`time zone definitions in the
3731 database <database-time-zone-definitions>`.
3732
3733 .. fieldlookup:: hour
3734
3735 ``hour``
3736 ~~~~~~~~
3737
3738 For datetime and time fields, an exact hour match. Allows chaining additional
3739 field lookups. Takes an integer between 0 and 23.
3740
3741 Example::
3742
3743 Event.objects.filter(timestamp__hour=23)
3744 Event.objects.filter(time__hour=5)
3745 Event.objects.filter(timestamp__hour__gte=12)
3746
3747 SQL equivalent:
3748
3749 .. code-block:: sql
3750
3751 SELECT ... WHERE EXTRACT('hour' FROM timestamp) = '23';
3752 SELECT ... WHERE EXTRACT('hour' FROM time) = '5';
3753 SELECT ... WHERE EXTRACT('hour' FROM timestamp) >= '12';
3754
3755 (The exact SQL syntax varies for each database engine.)
3756
3757 When :setting:`USE_TZ` is ``True``, datetime fields are converted to the
3758 current time zone before filtering. This requires :ref:`time zone definitions
3759 in the database <database-time-zone-definitions>`.
3760
3761 .. fieldlookup:: minute
3762
3763 ``minute``
3764 ~~~~~~~~~~
3765
3766 For datetime and time fields, an exact minute match. Allows chaining additional
3767 field lookups. Takes an integer between 0 and 59.
3768
3769 Example::
3770
3771 Event.objects.filter(timestamp__minute=29)
3772 Event.objects.filter(time__minute=46)
3773 Event.objects.filter(timestamp__minute__gte=29)
3774
3775 SQL equivalent:
3776
3777 .. code-block:: sql
3778
3779 SELECT ... WHERE EXTRACT('minute' FROM timestamp) = '29';
3780 SELECT ... WHERE EXTRACT('minute' FROM time) = '46';
3781 SELECT ... WHERE EXTRACT('minute' FROM timestamp) >= '29';
3782
3783 (The exact SQL syntax varies for each database engine.)
3784
3785 When :setting:`USE_TZ` is ``True``, datetime fields are converted to the
3786 current time zone before filtering. This requires :ref:`time zone definitions
3787 in the database <database-time-zone-definitions>`.
3788
3789 .. fieldlookup:: second
3790
3791 ``second``
3792 ~~~~~~~~~~
3793
3794 For datetime and time fields, an exact second match. Allows chaining additional
3795 field lookups. Takes an integer between 0 and 59.
3796
3797 Example::
3798
3799 Event.objects.filter(timestamp__second=31)
3800 Event.objects.filter(time__second=2)
3801 Event.objects.filter(timestamp__second__gte=31)
3802
3803 SQL equivalent:
3804
3805 .. code-block:: sql
3806
3807 SELECT ... WHERE EXTRACT('second' FROM timestamp) = '31';
3808 SELECT ... WHERE EXTRACT('second' FROM time) = '2';
3809 SELECT ... WHERE EXTRACT('second' FROM timestamp) >= '31';
3810
3811 (The exact SQL syntax varies for each database engine.)
3812
3813 When :setting:`USE_TZ` is ``True``, datetime fields are converted to the
3814 current time zone before filtering. This requires :ref:`time zone definitions
3815 in the database <database-time-zone-definitions>`.
3816
3817 .. fieldlookup:: isnull
3818
3819 ``isnull``
3820 ~~~~~~~~~~
3821
3822 Takes either ``True`` or ``False``, which correspond to SQL queries of
3823 ``IS NULL`` and ``IS NOT NULL``, respectively.
3824
3825 Example::
3826
3827 Entry.objects.filter(pub_date__isnull=True)
3828
3829 SQL equivalent:
3830
3831 .. code-block:: sql
3832
3833 SELECT ... WHERE pub_date IS NULL;
3834
3835 .. fieldlookup:: regex
3836
3837 ``regex``
3838 ~~~~~~~~~
3839
3840 Case-sensitive regular expression match.
3841
3842 The regular expression syntax is that of the database backend in use.
3843 In the case of SQLite, which has no built in regular expression support,
3844 this feature is provided by a (Python) user-defined REGEXP function, and
3845 the regular expression syntax is therefore that of Python's ``re`` module.
3846
3847 Example::
3848
3849 Entry.objects.get(title__regex=r"^(An?|The) +")
3850
3851 SQL equivalents:
3852
3853 .. code-block:: sql
3854
3855 SELECT ... WHERE title REGEXP BINARY '^(An?|The) +'; -- MySQL
3856
3857 SELECT ... WHERE REGEXP_LIKE(title, '^(An?|The) +', 'c'); -- Oracle
3858
3859 SELECT ... WHERE title ~ '^(An?|The) +'; -- PostgreSQL
3860
3861 SELECT ... WHERE title REGEXP '^(An?|The) +'; -- SQLite
3862
3863 Using raw strings (e.g., ``r'foo'`` instead of ``'foo'``) for passing in the
3864 regular expression syntax is recommended.
3865
3866 .. fieldlookup:: iregex
3867
3868 ``iregex``
3869 ~~~~~~~~~~
3870
3871 Case-insensitive regular expression match.
3872
3873 Example::
3874
3875 Entry.objects.get(title__iregex=r"^(an?|the) +")
3876
3877 SQL equivalents:
3878
3879 .. code-block:: sql
3880
3881 SELECT ... WHERE title REGEXP '^(an?|the) +'; -- MySQL
3882
3883 SELECT ... WHERE REGEXP_LIKE(title, '^(an?|the) +', 'i'); -- Oracle
3884
3885 SELECT ... WHERE title ~* '^(an?|the) +'; -- PostgreSQL
3886
3887 SELECT ... WHERE title REGEXP '(?i)^(an?|the) +'; -- SQLite
3888
3889 .. _aggregation-functions:
3890
3891 Aggregation functions
3892 ---------------------
3893
3894 .. currentmodule:: django.db.models
3895
3896 Django provides the following aggregation functions in the
3897 ``django.db.models`` module. For details on how to use these
3898 aggregate functions, see :doc:`the topic guide on aggregation
3899 </topics/db/aggregation>`. See the :class:`~django.db.models.Aggregate`
3900 documentation to learn how to create your aggregates.
3901
3902 .. warning::
3903
3904 SQLite can't handle aggregation on date/time fields out of the box.
3905 This is because there are no native date/time fields in SQLite and Django
3906 currently emulates these features using a text field. Attempts to use
3907 aggregation on date/time fields in SQLite will raise ``NotSupportedError``.
3908
3909 .. admonition:: Empty querysets or groups
3910
3911 Aggregation functions return ``None`` when used with an empty ``QuerySet``
3912 or group. For example, the ``Sum`` aggregation function returns ``None``
3913 instead of ``0`` if the ``QuerySet`` contains no entries or for any empty
3914 group in a non-empty ``QuerySet``. To return another value instead, define
3915 the ``default`` argument. ``Count`` is an exception to this behavior; it
3916 returns ``0`` if the ``QuerySet`` is empty since ``Count`` does not support
3917 the ``default`` argument.
3918
3919 All aggregates have the following parameters in common:
3920
3921 ``expressions``
3922 ~~~~~~~~~~~~~~~
3923
3924 Strings that reference fields on the model, transforms of the field, or
3925 :doc:`query expressions </ref/models/expressions>`.
3926
3927 ``output_field``
3928 ~~~~~~~~~~~~~~~~
3929
3930 An optional argument that represents the :doc:`model field </ref/models/fields>`
3931 of the return value
3932
3933 .. note::
3934
3935 When combining multiple field types, Django can only determine the
3936 ``output_field`` if all fields are of the same type. Otherwise, you
3937 must provide the ``output_field`` yourself.
3938
3939 .. _aggregate-filter:
3940
3941 ``filter``
3942 ~~~~~~~~~~
3943
3944 An optional :class:`Q object <django.db.models.Q>` that's used to filter the
3945 rows that are aggregated.
3946
3947 See :ref:`conditional-aggregation` and :ref:`filtering-on-annotations` for
3948 example usage.
3949
3950 .. _aggregate-default:
3951
3952 ``default``
3953 ~~~~~~~~~~~
3954
3955 An optional argument that allows specifying a value to use as a default value
3956 when the queryset (or grouping) contains no entries.
3957
3958 ``**extra``
3959 ~~~~~~~~~~~
3960
3961 Keyword arguments that can provide extra context for the SQL generated
3962 by the aggregate.
3963
3964 ``Avg``
3965 ~~~~~~~
3966
3967 .. class:: Avg(expression, output_field=None, distinct=False, filter=None, default=None, **extra)
3968
3969 Returns the mean value of the given expression, which must be numeric
3970 unless you specify a different ``output_field``.
3971
3972 * Default alias: ``<field>__avg``
3973 * Return type: ``float`` if input is ``int``, otherwise same as input
3974 field, or ``output_field`` if supplied. If the queryset or grouping is
3975 empty, ``default`` is returned.
3976
3977 .. attribute:: distinct
3978
3979 Optional. If ``distinct=True``, ``Avg`` returns the mean value of
3980 unique values. This is the SQL equivalent of ``AVG(DISTINCT <field>)``.
3981 The default value is ``False``.
3982
3983 ``Count``
3984 ~~~~~~~~~
3985
3986 .. class:: Count(expression, distinct=False, filter=None, **extra)
3987
3988 Returns the number of objects that are related through the provided
3989 expression. ``Count('*')`` is equivalent to the SQL ``COUNT(*)``
3990 expression.
3991
3992 * Default alias: ``<field>__count``
3993 * Return type: ``int``
3994
3995 .. attribute:: distinct
3996
3997 Optional. If ``distinct=True``, the count will only include unique
3998 instances. This is the SQL equivalent of ``COUNT(DISTINCT <field>)``.
3999 The default value is ``False``.
4000
4001 .. note::
4002
4003 The ``default`` argument is not supported.
4004
4005 ``Max``
4006 ~~~~~~~
4007
4008 .. class:: Max(expression, output_field=None, filter=None, default=None, **extra)
4009
4010 Returns the maximum value of the given expression.
4011
4012 * Default alias: ``<field>__max``
4013 * Return type: same as input field, or ``output_field`` if supplied. If the
4014 queryset or grouping is empty, ``default`` is returned.
4015
4016 ``Min``
4017 ~~~~~~~
4018
4019 .. class:: Min(expression, output_field=None, filter=None, default=None, **extra)
4020
4021 Returns the minimum value of the given expression.
4022
4023 * Default alias: ``<field>__min``
4024 * Return type: same as input field, or ``output_field`` if supplied. If the
4025 queryset or grouping is empty, ``default`` is returned.
4026
4027 ``StdDev``
4028 ~~~~~~~~~~
4029
4030 .. class:: StdDev(expression, output_field=None, sample=False, filter=None, default=None, **extra)
4031
4032 Returns the standard deviation of the data in the provided expression.
4033
4034 * Default alias: ``<field>__stddev``
4035 * Return type: ``float`` if input is ``int``, otherwise same as input
4036 field, or ``output_field`` if supplied. If the queryset or grouping is
4037 empty, ``default`` is returned.
4038
4039 .. attribute:: sample
4040
4041 Optional. By default, ``StdDev`` returns the population standard
4042 deviation. However, if ``sample=True``, the return value will be the
4043 sample standard deviation.
4044
4045 ``Sum``
4046 ~~~~~~~
4047
4048 .. class:: Sum(expression, output_field=None, distinct=False, filter=None, default=None, **extra)
4049
4050 Computes the sum of all values of the given expression.
4051
4052 * Default alias: ``<field>__sum``
4053 * Return type: same as input field, or ``output_field`` if supplied. If the
4054 queryset or grouping is empty, ``default`` is returned.
4055
4056 .. attribute:: distinct
4057
4058 Optional. If ``distinct=True``, ``Sum`` returns the sum of unique
4059 values. This is the SQL equivalent of ``SUM(DISTINCT <field>)``. The
4060 default value is ``False``.
4061
4062 ``Variance``
4063 ~~~~~~~~~~~~
4064
4065 .. class:: Variance(expression, output_field=None, sample=False, filter=None, default=None, **extra)
4066
4067 Returns the variance of the data in the provided expression.
4068
4069 * Default alias: ``<field>__variance``
4070 * Return type: ``float`` if input is ``int``, otherwise same as input
4071 field, or ``output_field`` if supplied. If the queryset or grouping is
4072 empty, ``default`` is returned.
4073
4074 .. attribute:: sample
4075
4076 Optional. By default, ``Variance`` returns the population variance.
4077 However, if ``sample=True``, the return value will be the sample
4078 variance.
4079
4080 Query-related tools
4081 ===================
4082
4083 This section provides reference material for query-related tools not documented
4084 elsewhere.
4085
4086 ``Q()`` objects
4087 ---------------
4088
4089 .. class:: Q
4090
4091 A ``Q()`` object represents an SQL condition that can be used in
4092 database-related operations. It's similar to how an
4093 :class:`F() <django.db.models.F>` object represents the value of a model field
4094 or annotation. They make it possible to define and reuse conditions. These can
4095 be negated using the ``~`` (``NOT``) operator, and combined using operators
4096 such as ``|`` (``OR``), ``&`` (``AND``), and ``^`` (``XOR``). See
4097 :ref:`complex-lookups-with-q`.
4098
4099 ``Prefetch()`` objects
4100 ----------------------
4101
4102 .. class:: Prefetch(lookup, queryset=None, to_attr=None)
4103
4104 The ``Prefetch()`` object can be used to control the operation of
4105 :meth:`~django.db.models.query.QuerySet.prefetch_related()`.
4106
4107 The ``lookup`` argument describes the relations to follow and works the same
4108 as the string based lookups passed to
4109 :meth:`~django.db.models.query.QuerySet.prefetch_related()`. For example:
4110
4111 .. code-block:: pycon
4112
4113 >>> from django.db.models import Prefetch
4114 >>> Question.objects.prefetch_related(Prefetch("choice_set")).get().choice_set.all()
4115 <QuerySet [<Choice: Not much>, <Choice: The sky>, <Choice: Just hacking again>]>
4116 # This will only execute two queries regardless of the number of Question
4117 # and Choice objects.
4118 >>> Question.objects.prefetch_related(Prefetch("choice_set"))
4119 <QuerySet [<Question: What's up?>]>
4120
4121 The ``queryset`` argument supplies a base ``QuerySet`` for the given lookup.
4122 This is useful to further filter down the prefetch operation, or to call
4123 :meth:`~django.db.models.query.QuerySet.select_related()` from the prefetched
4124 relation, hence reducing the number of queries even further:
4125
4126 .. code-block:: pycon
4127
4128 >>> voted_choices = Choice.objects.filter(votes__gt=0)
4129 >>> voted_choices
4130 <QuerySet [<Choice: The sky>]>
4131 >>> prefetch = Prefetch("choice_set", queryset=voted_choices)
4132 >>> Question.objects.prefetch_related(prefetch).get().choice_set.all()
4133 <QuerySet [<Choice: The sky>]>
4134
4135 The ``to_attr`` argument sets the result of the prefetch operation to a custom
4136 attribute:
4137
4138 .. code-block:: pycon
4139
4140 >>> prefetch = Prefetch("choice_set", queryset=voted_choices, to_attr="voted_choices")
4141 >>> Question.objects.prefetch_related(prefetch).get().voted_choices
4142 [<Choice: The sky>]
4143 >>> Question.objects.prefetch_related(prefetch).get().choice_set.all()
4144 <QuerySet [<Choice: Not much>, <Choice: The sky>, <Choice: Just hacking again>]>
4145
4146 .. note::
4147
4148 When using ``to_attr`` the prefetched result is stored in a list. This can
4149 provide a significant speed improvement over traditional
4150 ``prefetch_related`` calls which store the cached result within a
4151 ``QuerySet`` instance.
4152
4153 ``prefetch_related_objects()``
4154 ------------------------------
4155
4156 .. function:: prefetch_related_objects(model_instances, *related_lookups)
4157 .. function:: aprefetch_related_objects(model_instances, *related_lookups)
4158
4159 *Asynchronous version*: ``aprefetch_related_objects()``
4160
4161 Prefetches the given lookups on an iterable of model instances. This is useful
4162 in code that receives a list of model instances as opposed to a ``QuerySet``;
4163 for example, when fetching models from a cache or instantiating them manually.
4164
4165 Pass an iterable of model instances (must all be of the same class) and the
4166 lookups or :class:`Prefetch` objects you want to prefetch for. For example:
4167
4168 .. code-block:: pycon
4169
4170 >>> from django.db.models import prefetch_related_objects
4171 >>> restaurants = fetch_top_restaurants_from_cache() # A list of Restaurants
4172 >>> prefetch_related_objects(restaurants, "pizzas__toppings")
4173
4174 When using multiple databases with ``prefetch_related_objects``, the prefetch
4175 query will use the database associated with the model instance. This can be
4176 overridden by using a custom queryset in a related lookup.
4177
4178 ``FilteredRelation()`` objects
4179 ------------------------------
4180
4181 .. class:: FilteredRelation(relation_name, *, condition=Q())
4182
4183 .. attribute:: FilteredRelation.relation_name
4184
4185 The name of the field on which you'd like to filter the relation.
4186
4187 .. attribute:: FilteredRelation.condition
4188
4189 A :class:`~django.db.models.Q` object to control the filtering.
4190
4191 ``FilteredRelation`` is used with :meth:`~.QuerySet.annotate()` to create an
4192 ``ON`` clause when a ``JOIN`` is performed. It doesn't act on the default
4193 relationship but on the annotation name (``pizzas_vegetarian`` in example
4194 below).
4195
4196 For example, to find restaurants that have vegetarian pizzas with
4197 ``'mozzarella'`` in the name:
4198
4199 .. code-block:: pycon
4200
4201 >>> from django.db.models import FilteredRelation, Q
4202 >>> Restaurant.objects.annotate(
4203 ... pizzas_vegetarian=FilteredRelation(
4204 ... "pizzas",
4205 ... condition=Q(pizzas__vegetarian=True),
4206 ... ),
4207 ... ).filter(pizzas_vegetarian__name__icontains="mozzarella")
4208
4209 If there are a large number of pizzas, this queryset performs better than:
4210
4211 .. code-block:: pycon
4212
4213 >>> Restaurant.objects.filter(
4214 ... pizzas__vegetarian=True,
4215 ... pizzas__name__icontains="mozzarella",
4216 ... )
4217
4218 because the filtering in the ``WHERE`` clause of the first queryset will only
4219 operate on vegetarian pizzas.
4220
4221 ``FilteredRelation`` doesn't support:
4222
4223 * :meth:`.QuerySet.only` and :meth:`~.QuerySet.prefetch_related`.
4224 * A :class:`~django.contrib.contenttypes.fields.GenericForeignKey`
4225 inherited from a parent model.