Debian Codegraph @

docs/topics/db/queries.txt

raw
1 ==============
2 Making queries
3 ==============
4
5 .. currentmodule:: django.db.models
6
7 Once you've created your :doc:`data models </topics/db/models>`, Django
8 automatically gives you a database-abstraction API that lets you create,
9 retrieve, update and delete objects. This document explains how to use this
10 API. Refer to the :doc:`data model reference </ref/models/index>` for full
11 details of all the various model lookup options.
12
13 Throughout this guide (and in the reference), we'll refer to the following
14 models, which comprise a blog application:
15
16 .. _queryset-model-example:
17
18 .. code-block:: python
19
20 from datetime import date
21
22 from django.db import models
23
24
25 class Blog(models.Model):
26 name = models.CharField(max_length=100)
27 tagline = models.TextField()
28
29 def __str__(self):
30 return self.name
31
32
33 class Author(models.Model):
34 name = models.CharField(max_length=200)
35 email = models.EmailField()
36
37 def __str__(self):
38 return self.name
39
40
41 class Entry(models.Model):
42 blog = models.ForeignKey(Blog, on_delete=models.CASCADE)
43 headline = models.CharField(max_length=255)
44 body_text = models.TextField()
45 pub_date = models.DateField()
46 mod_date = models.DateField(default=date.today)
47 authors = models.ManyToManyField(Author)
48 number_of_comments = models.IntegerField(default=0)
49 number_of_pingbacks = models.IntegerField(default=0)
50 rating = models.IntegerField(default=5)
51
52 def __str__(self):
53 return self.headline
54
55 Creating objects
56 ================
57
58 To represent database-table data in Python objects, Django uses an intuitive
59 system: A model class represents a database table, and an instance of that
60 class represents a particular record in the database table.
61
62 To create an object, instantiate it using keyword arguments to the model class,
63 then call :meth:`~django.db.models.Model.save` to save it to the database.
64
65 Assuming models live in a ``models.py`` file inside a ``blog`` Django app, here
66 is an example:
67
68 .. code-block:: pycon
69
70 >>> from blog.models import Blog
71 >>> b = Blog(name="Beatles Blog", tagline="All the latest Beatles news.")
72 >>> b.save()
73
74 This performs an ``INSERT`` SQL statement behind the scenes. Django doesn't hit
75 the database until you explicitly call :meth:`~django.db.models.Model.save`.
76
77 The :meth:`~django.db.models.Model.save` method has no return value.
78
79 .. seealso::
80
81 :meth:`~django.db.models.Model.save` takes a number of advanced options not
82 described here. See the documentation for
83 :meth:`~django.db.models.Model.save` for complete details.
84
85 To create and save an object in a single step, use the
86 :meth:`~django.db.models.query.QuerySet.create()` method.
87
88 Saving changes to objects
89 =========================
90
91 To save changes to an object that's already in the database, use
92 :meth:`~django.db.models.Model.save`.
93
94 Given a ``Blog`` instance ``b5`` that has already been saved to the database,
95 this example changes its name and updates its record in the database:
96
97 .. code-block:: pycon
98
99 >>> b5.name = "New name"
100 >>> b5.save()
101
102 This performs an ``UPDATE`` SQL statement behind the scenes. Django doesn't hit
103 the database until you explicitly call :meth:`~django.db.models.Model.save`.
104
105 Saving ``ForeignKey`` and ``ManyToManyField`` fields
106 ----------------------------------------------------
107
108 Updating a :class:`~django.db.models.ForeignKey` field works exactly the same
109 way as saving a normal field -- assign an object of the right type to the field
110 in question. This example updates the ``blog`` attribute of an ``Entry``
111 instance ``entry``, assuming appropriate instances of ``Entry`` and ``Blog``
112 are already saved to the database (so we can retrieve them below):
113
114 .. code-block:: pycon
115
116 >>> from blog.models import Blog, Entry
117 >>> entry = Entry.objects.get(pk=1)
118 >>> cheese_blog = Blog.objects.get(name="Cheddar Talk")
119 >>> entry.blog = cheese_blog
120 >>> entry.save()
121
122 Updating a :class:`~django.db.models.ManyToManyField` works a little
123 differently -- use the
124 :meth:`~django.db.models.fields.related.RelatedManager.add` method on the field
125 to add a record to the relation. This example adds the ``Author`` instance
126 ``joe`` to the ``entry`` object:
127
128 .. code-block:: pycon
129
130 >>> from blog.models import Author
131 >>> joe = Author.objects.create(name="Joe")
132 >>> entry.authors.add(joe)
133
134 To add multiple records to a :class:`~django.db.models.ManyToManyField` in one
135 go, include multiple arguments in the call to
136 :meth:`~django.db.models.fields.related.RelatedManager.add`, like this:
137
138 .. code-block:: pycon
139
140 >>> john = Author.objects.create(name="John")
141 >>> paul = Author.objects.create(name="Paul")
142 >>> george = Author.objects.create(name="George")
143 >>> ringo = Author.objects.create(name="Ringo")
144 >>> entry.authors.add(john, paul, george, ringo)
145
146 Django will complain if you try to assign or add an object of the wrong type.
147
148 .. _retrieving-objects:
149
150 Retrieving objects
151 ==================
152
153 To retrieve objects from your database, construct a
154 :class:`~django.db.models.query.QuerySet` via a
155 :class:`~django.db.models.Manager` on your model class.
156
157 A :class:`~django.db.models.query.QuerySet` represents a collection of objects
158 from your database. It can have zero, one or many *filters*. Filters narrow
159 down the query results based on the given parameters. In SQL terms, a
160 :class:`~django.db.models.query.QuerySet` equates to a ``SELECT`` statement,
161 and a filter is a limiting clause such as ``WHERE`` or ``LIMIT``.
162
163 You get a :class:`~django.db.models.query.QuerySet` by using your model's
164 :class:`~django.db.models.Manager`. Each model has at least one
165 :class:`~django.db.models.Manager`, and it's called
166 :attr:`~django.db.models.Model.objects` by default. Access it directly via the
167 model class, like so:
168
169 .. code-block:: pycon
170
171 >>> Blog.objects
172 <django.db.models.manager.Manager object at ...>
173 >>> b = Blog(name="Foo", tagline="Bar")
174 >>> b.objects
175 Traceback:
176 ...
177 AttributeError: "Manager isn't accessible via Blog instances."
178
179 .. note::
180
181 A ``Manager`` is accessible only via model classes, rather than from model
182 instances, to enforce a separation between "table-level" operations and
183 "record-level" operations.
184
185 The :class:`~django.db.models.Manager` is the main source of querysets for
186 a model. For example, ``Blog.objects.all()`` returns a
187 :class:`~django.db.models.query.QuerySet` that contains all ``Blog`` objects in
188 the database.
189
190 Retrieving all objects
191 ----------------------
192
193 The simplest way to retrieve objects from a table is to get all of them. To do
194 this, use the :meth:`~django.db.models.query.QuerySet.all` method on a
195 :class:`~django.db.models.Manager`:
196
197 .. code-block:: pycon
198
199 >>> all_entries = Entry.objects.all()
200
201 The :meth:`~django.db.models.query.QuerySet.all` method returns a
202 :class:`~django.db.models.query.QuerySet` of all the objects in the database.
203
204 Retrieving specific objects with filters
205 ----------------------------------------
206
207 The :class:`~django.db.models.query.QuerySet` returned by
208 :meth:`~django.db.models.query.QuerySet.all` describes all objects in the
209 database table. Usually, though, you'll need to select only a subset of the
210 complete set of objects.
211
212 To create such a subset, you refine the initial
213 :class:`~django.db.models.query.QuerySet`, adding filter conditions. The two
214 most common ways to refine a :class:`~django.db.models.query.QuerySet` are:
215
216 ``filter(**kwargs)``
217 Returns a new :class:`~django.db.models.query.QuerySet` containing objects
218 that match the given lookup parameters.
219
220 ``exclude(**kwargs)``
221 Returns a new :class:`~django.db.models.query.QuerySet` containing objects
222 that do *not* match the given lookup parameters.
223
224 The lookup parameters (``**kwargs`` in the above function definitions) should
225 be in the format described in `Field lookups`_ below.
226
227 For example, to get a :class:`~django.db.models.query.QuerySet` of blog entries
228 from the year 2006, use :meth:`~django.db.models.query.QuerySet.filter` like
229 so::
230
231 Entry.objects.filter(pub_date__year=2006)
232
233 With the default manager class, it is the same as::
234
235 Entry.objects.all().filter(pub_date__year=2006)
236
237 .. _chaining-filters:
238
239 Chaining filters
240 ~~~~~~~~~~~~~~~~
241
242 The result of refining a :class:`~django.db.models.query.QuerySet` is itself a
243 :class:`~django.db.models.query.QuerySet`, so it's possible to chain
244 refinements together. For example:
245
246 .. code-block:: pycon
247
248 >>> Entry.objects.filter(headline__startswith="What").exclude(
249 ... pub_date__gte=datetime.date.today()
250 ... ).filter(pub_date__gte=datetime.date(2005, 1, 30))
251
252 This takes the initial :class:`~django.db.models.query.QuerySet` of all entries
253 in the database, adds a filter, then an exclusion, then another filter. The
254 final result is a :class:`~django.db.models.query.QuerySet` containing all
255 entries with a headline that starts with "What", that were published between
256 January 30, 2005, and the current day.
257
258 .. _filtered-querysets-are-unique:
259
260 Filtered ``QuerySet``\s are unique
261 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
262
263 Each time you refine a :class:`~django.db.models.query.QuerySet`, you get a
264 brand-new :class:`~django.db.models.query.QuerySet` that is in no way bound to
265 the previous :class:`~django.db.models.query.QuerySet`. Each refinement creates
266 a separate and distinct :class:`~django.db.models.query.QuerySet` that can be
267 stored, used and reused.
268
269 Example:
270
271 .. code-block:: pycon
272
273 >>> q1 = Entry.objects.filter(headline__startswith="What")
274 >>> q2 = q1.exclude(pub_date__gte=datetime.date.today())
275 >>> q3 = q1.filter(pub_date__gte=datetime.date.today())
276
277 These three querysets are separate. The first is a base
278 :class:`~django.db.models.query.QuerySet` containing all entries that contain a
279 headline starting with "What". The second is a subset of the first, with an
280 additional criteria that excludes records whose ``pub_date`` is today or in the
281 future. The third is a subset of the first, with an additional criteria that
282 selects only the records whose ``pub_date`` is today or in the future. The
283 initial :class:`~django.db.models.query.QuerySet` (``q1``) is unaffected by the
284 refinement process.
285
286 .. _querysets-are-lazy:
287
288 ``QuerySet``\s are lazy
289 ~~~~~~~~~~~~~~~~~~~~~~~
290
291 ``QuerySet`` objects are lazy -- the act of creating a
292 :class:`~django.db.models.query.QuerySet` doesn't involve any database
293 activity. You can stack filters together all day long, and Django won't
294 actually run the query until the :class:`~django.db.models.query.QuerySet` is
295 *evaluated*. Take a look at this example:
296
297 .. code-block:: pycon
298
299 >>> q = Entry.objects.filter(headline__startswith="What")
300 >>> q = q.filter(pub_date__lte=datetime.date.today())
301 >>> q = q.exclude(body_text__icontains="food")
302 >>> print(q)
303
304 Though this looks like three database hits, in fact it hits the database only
305 once, at the last line (``print(q)``). In general, the results of a
306 :class:`~django.db.models.query.QuerySet` aren't fetched from the database
307 until you "ask" for them. When you do, the
308 :class:`~django.db.models.query.QuerySet` is *evaluated* by accessing the
309 database. For more details on exactly when evaluation takes place, see
310 :ref:`when-querysets-are-evaluated`.
311
312 .. _retrieving-single-object-with-get:
313
314 Retrieving a single object with ``get()``
315 -----------------------------------------
316
317 :meth:`~django.db.models.query.QuerySet.filter` will always give you a
318 :class:`~django.db.models.query.QuerySet`, even if only a single object matches
319 the query - in this case, it will be a
320 :class:`~django.db.models.query.QuerySet` containing a single element.
321
322 If you know there is only one object that matches your query, you can use the
323 :meth:`~django.db.models.query.QuerySet.get` method on a
324 :class:`~django.db.models.Manager` which returns the object directly:
325
326 .. code-block:: pycon
327
328 >>> one_entry = Entry.objects.get(pk=1)
329
330 You can use any query expression with
331 :meth:`~django.db.models.query.QuerySet.get`, just like with
332 :meth:`~django.db.models.query.QuerySet.filter` - again, see `Field lookups`_
333 below.
334
335 Note that there is a difference between using
336 :meth:`~django.db.models.query.QuerySet.get`, and using
337 :meth:`~django.db.models.query.QuerySet.filter` with a slice of ``[0]``. If
338 there are no results that match the query,
339 :meth:`~django.db.models.query.QuerySet.get` will raise a ``DoesNotExist``
340 exception. This exception is an attribute of the model class that the query is
341 being performed on - so in the code above, if there is no ``Entry`` object with
342 a primary key of 1, Django will raise ``Entry.DoesNotExist``.
343
344 Similarly, Django will complain if more than one item matches the
345 :meth:`~django.db.models.query.QuerySet.get` query. In this case, it will raise
346 :exc:`~django.core.exceptions.MultipleObjectsReturned`, which again is an
347 attribute of the model class itself.
348
349
350 Other ``QuerySet`` methods
351 --------------------------
352
353 Most of the time you'll use :meth:`~django.db.models.query.QuerySet.all`,
354 :meth:`~django.db.models.query.QuerySet.get`,
355 :meth:`~django.db.models.query.QuerySet.filter` and
356 :meth:`~django.db.models.query.QuerySet.exclude` when you need to look up
357 objects from the database. However, that's far from all there is; see the
358 :ref:`QuerySet API Reference <queryset-api>` for a complete list of all the
359 various :class:`~django.db.models.query.QuerySet` methods.
360
361 .. _limiting-querysets:
362
363 Limiting ``QuerySet``\s
364 -----------------------
365
366 Use a subset of Python's array-slicing syntax to limit your
367 :class:`~django.db.models.query.QuerySet` to a certain number of results. This
368 is the equivalent of SQL's ``LIMIT`` and ``OFFSET`` clauses.
369
370 For example, this returns the first 5 objects (``LIMIT 5``):
371
372 .. code-block:: pycon
373
374 >>> Entry.objects.all()[:5]
375
376 This returns the sixth through tenth objects (``OFFSET 5 LIMIT 5``):
377
378 .. code-block:: pycon
379
380 >>> Entry.objects.all()[5:10]
381
382 Negative indexing (i.e. ``Entry.objects.all()[-1]``) is not supported.
383
384 Generally, slicing a :class:`~django.db.models.query.QuerySet` returns a new
385 :class:`~django.db.models.query.QuerySet` -- it doesn't evaluate the query. An
386 exception is if you use the "step" parameter of Python slice syntax. For
387 example, this would actually execute the query in order to return a list of
388 every *second* object of the first 10:
389
390 .. code-block:: pycon
391
392 >>> Entry.objects.all()[:10:2]
393
394 Further filtering or ordering of a sliced queryset is prohibited due to the
395 ambiguous nature of how that might work.
396
397 To retrieve a *single* object rather than a list
398 (e.g. ``SELECT foo FROM bar LIMIT 1``), use an index instead of a slice. For
399 example, this returns the first ``Entry`` in the database, after ordering
400 entries alphabetically by headline:
401
402 .. code-block:: pycon
403
404 >>> Entry.objects.order_by("headline")[0]
405
406 This is roughly equivalent to:
407
408 .. code-block:: pycon
409
410 >>> Entry.objects.order_by("headline")[0:1].get()
411
412 Note, however, that the first of these will raise ``IndexError`` while the
413 second will raise ``DoesNotExist`` if no objects match the given criteria. See
414 :meth:`~django.db.models.query.QuerySet.get` for more details.
415
416 .. _field-lookups-intro:
417
418 Field lookups
419 -------------
420
421 Field lookups are how you specify the meat of an SQL ``WHERE`` clause. They're
422 specified as keyword arguments to the :class:`~django.db.models.query.QuerySet`
423 methods :meth:`~django.db.models.query.QuerySet.filter`,
424 :meth:`~django.db.models.query.QuerySet.exclude` and
425 :meth:`~django.db.models.query.QuerySet.get`.
426
427 Basic lookups keyword arguments take the form ``field__lookuptype=value``.
428 (That's a double-underscore). For example:
429
430 .. code-block:: pycon
431
432 >>> Entry.objects.filter(pub_date__lte="2006-01-01")
433
434 translates (roughly) into the following SQL:
435
436 .. code-block:: sql
437
438 SELECT * FROM blog_entry WHERE pub_date <= '2006-01-01';
439
440 .. admonition:: How this is possible
441
442 Python has the ability to define functions that accept arbitrary name-value
443 arguments whose names and values are evaluated at runtime. For more
444 information, see :ref:`tut-keywordargs` in the official Python tutorial.
445
446 The field specified in a lookup has to be the name of a model field. There's
447 one exception though, in case of a :class:`~django.db.models.ForeignKey` you
448 can specify the field name suffixed with ``_id``. In this case, the value
449 parameter is expected to contain the raw value of the foreign model's primary
450 key. For example:
451
452 .. code-block:: pycon
453
454 >>> Entry.objects.filter(blog_id=4)
455
456 If you pass an invalid keyword argument, a lookup function will raise
457 ``TypeError``.
458
459 The database API supports about two dozen lookup types; a complete reference
460 can be found in the :ref:`field lookup reference <field-lookups>`. To give you
461 a taste of what's available, here's some of the more common lookups you'll
462 probably use:
463
464 :lookup:`exact`
465 An "exact" match. For example:
466
467 .. code-block:: pycon
468
469 >>> Entry.objects.get(headline__exact="Cat bites dog")
470
471 Would generate SQL along these lines:
472
473 .. code-block:: sql
474
475 SELECT ... WHERE headline = 'Cat bites dog';
476
477 If you don't provide a lookup type -- that is, if your keyword argument
478 doesn't contain a double underscore -- the lookup type is assumed to be
479 ``exact``.
480
481 For example, the following two statements are equivalent:
482
483 .. code-block:: pycon
484
485 >>> Blog.objects.get(id__exact=14) # Explicit form
486 >>> Blog.objects.get(id=14) # __exact is implied
487
488 This is for convenience, because ``exact`` lookups are the common case.
489
490 :lookup:`iexact`
491 A case-insensitive match. So, the query:
492
493 .. code-block:: pycon
494
495 >>> Blog.objects.get(name__iexact="beatles blog")
496
497 Would match a ``Blog`` titled ``"Beatles Blog"``, ``"beatles blog"``, or
498 even ``"BeAtlES blOG"``.
499
500 :lookup:`contains`
501 Case-sensitive containment test. For example::
502
503 Entry.objects.get(headline__contains="Lennon")
504
505 Roughly translates to this SQL:
506
507 .. code-block:: sql
508
509 SELECT ... WHERE headline LIKE '%Lennon%';
510
511 Note this will match the headline ``'Today Lennon honored'`` but not
512 ``'today lennon honored'``.
513
514 There's also a case-insensitive version, :lookup:`icontains`.
515
516 :lookup:`startswith`, :lookup:`endswith`
517 Starts-with and ends-with search, respectively. There are also
518 case-insensitive versions called :lookup:`istartswith` and
519 :lookup:`iendswith`.
520
521 Again, this only scratches the surface. A complete reference can be found in the
522 :ref:`field lookup reference <field-lookups>`.
523
524 .. _lookups-that-span-relationships:
525
526 Lookups that span relationships
527 -------------------------------
528
529 Django offers a powerful and intuitive way to "follow" relationships in
530 lookups, taking care of the SQL ``JOIN``\s for you automatically, behind the
531 scenes. To span a relationship, use the field name of related fields
532 across models, separated by double underscores, until you get to the field you
533 want.
534
535 This example retrieves all ``Entry`` objects with a ``Blog`` whose ``name``
536 is ``'Beatles Blog'``:
537
538 .. code-block:: pycon
539
540 >>> Entry.objects.filter(blog__name="Beatles Blog")
541
542 This spanning can be as deep as you'd like.
543
544 It works backwards, too. While it :attr:`can be customized
545 <.ForeignKey.related_query_name>`, by default you refer to a "reverse"
546 relationship in a lookup using the lowercase name of the model.
547
548 This example retrieves all ``Blog`` objects which have at least one ``Entry``
549 whose ``headline`` contains ``'Lennon'``:
550
551 .. code-block:: pycon
552
553 >>> Blog.objects.filter(entry__headline__contains="Lennon")
554
555 If you are filtering across multiple relationships and one of the intermediate
556 models doesn't have a value that meets the filter condition, Django will treat
557 it as if there is an empty (all values are ``NULL``), but valid, object there.
558 All this means is that no error will be raised. For example, in this filter::
559
560 Blog.objects.filter(entry__authors__name="Lennon")
561
562 (if there was a related ``Author`` model), if there was no ``author``
563 associated with an entry, it would be treated as if there was also no ``name``
564 attached, rather than raising an error because of the missing ``author``.
565 Usually this is exactly what you want to have happen. The only case where it
566 might be confusing is if you are using :lookup:`isnull`. Thus::
567
568 Blog.objects.filter(entry__authors__name__isnull=True)
569
570 will return ``Blog`` objects that have an empty ``name`` on the ``author`` and
571 also those which have an empty ``author`` on the ``entry``. If you don't want
572 those latter objects, you could write::
573
574 Blog.objects.filter(entry__authors__isnull=False, entry__authors__name__isnull=True)
575
576 .. _spanning-multi-valued-relationships:
577
578 Spanning multi-valued relationships
579 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
580
581 When spanning a :class:`~django.db.models.ManyToManyField` or a reverse
582 :class:`~django.db.models.ForeignKey` (such as from ``Blog`` to ``Entry``),
583 filtering on multiple attributes raises the question of whether to require each
584 attribute to coincide in the same related object. We might seek blogs that have
585 an entry from 2008 with *“Lennon”* in its headline, or we might seek blogs that
586 merely have any entry from 2008 as well as some newer or older entry with
587 *“Lennon”* in its headline.
588
589 To select all blogs containing at least one entry from 2008 having *"Lennon"*
590 in its headline (the same entry satisfying both conditions), we would write::
591
592 Blog.objects.filter(entry__headline__contains="Lennon", entry__pub_date__year=2008)
593
594 Otherwise, to perform a more permissive query selecting any blogs with merely
595 *some* entry with *"Lennon"* in its headline and *some* entry from 2008, we
596 would write::
597
598 Blog.objects.filter(entry__headline__contains="Lennon").filter(
599 entry__pub_date__year=2008
600 )
601
602 Suppose there is only one blog that has both entries containing *"Lennon"* and
603 entries from 2008, but that none of the entries from 2008 contained *"Lennon"*.
604 The first query would not return any blogs, but the second query would return
605 that one blog. (This is because the entries selected by the second filter may
606 or may not be the same as the entries in the first filter. We are filtering the
607 ``Blog`` items with each filter statement, not the ``Entry`` items.) In short,
608 if each condition needs to match the same related object, then each should be
609 contained in a single :meth:`~django.db.models.query.QuerySet.filter` call.
610
611 .. note::
612
613 As the second (more permissive) query chains multiple filters, it performs
614 multiple joins to the primary model, potentially yielding duplicates.
615
616 .. code-block:: pycon
617
618 >>> from datetime import date
619 >>> beatles = Blog.objects.create(name="Beatles Blog")
620 >>> pop = Blog.objects.create(name="Pop Music Blog")
621 >>> Entry.objects.create(
622 ... blog=beatles,
623 ... headline="New Lennon Biography",
624 ... pub_date=date(2008, 6, 1),
625 ... )
626 <Entry: New Lennon Biography>
627 >>> Entry.objects.create(
628 ... blog=beatles,
629 ... headline="New Lennon Biography in Paperback",
630 ... pub_date=date(2009, 6, 1),
631 ... )
632 <Entry: New Lennon Biography in Paperback>
633 >>> Entry.objects.create(
634 ... blog=pop,
635 ... headline="Best Albums of 2008",
636 ... pub_date=date(2008, 12, 15),
637 ... )
638 <Entry: Best Albums of 2008>
639 >>> Entry.objects.create(
640 ... blog=pop,
641 ... headline="Lennon Would Have Loved Hip Hop",
642 ... pub_date=date(2020, 4, 1),
643 ... )
644 <Entry: Lennon Would Have Loved Hip Hop>
645 >>> Blog.objects.filter(
646 ... entry__headline__contains="Lennon",
647 ... entry__pub_date__year=2008,
648 ... )
649 <QuerySet [<Blog: Beatles Blog>]>
650 >>> Blog.objects.filter(
651 ... entry__headline__contains="Lennon",
652 ... ).filter(
653 ... entry__pub_date__year=2008,
654 ... )
655 <QuerySet [<Blog: Beatles Blog>, <Blog: Beatles Blog>, <Blog: Pop Music Blog]>
656
657 .. note::
658
659 The behavior of :meth:`~django.db.models.query.QuerySet.filter` for queries
660 that span multi-value relationships, as described above, is not implemented
661 equivalently for :meth:`~django.db.models.query.QuerySet.exclude`. Instead,
662 the conditions in a single :meth:`~django.db.models.query.QuerySet.exclude`
663 call will not necessarily refer to the same item.
664
665 For example, the following query would exclude blogs that contain *both*
666 entries with *"Lennon"* in the headline *and* entries published in 2008::
667
668 Blog.objects.exclude(
669 entry__headline__contains="Lennon",
670 entry__pub_date__year=2008,
671 )
672
673 However, unlike the behavior when using
674 :meth:`~django.db.models.query.QuerySet.filter`, this will not limit blogs
675 based on entries that satisfy both conditions. In order to do that, i.e.
676 to select all blogs that do not contain entries published with *"Lennon"*
677 that were published in 2008, you need to make two queries::
678
679 Blog.objects.exclude(
680 entry__in=Entry.objects.filter(
681 headline__contains="Lennon",
682 pub_date__year=2008,
683 ),
684 )
685
686 .. _using-f-expressions-in-filters:
687
688 Filters can reference fields on the model
689 -----------------------------------------
690
691 In the examples given so far, we have constructed filters that compare
692 the value of a model field with a constant. But what if you want to compare
693 the value of a model field with another field on the same model?
694
695 Django provides :class:`F expressions <django.db.models.F>` to allow such
696 comparisons. Instances of ``F()`` act as a reference to a model field within a
697 query. These references can then be used in query filters to compare the values
698 of two different fields on the same model instance.
699
700 For example, to find a list of all blog entries that have had more comments
701 than pingbacks, we construct an ``F()`` object to reference the pingback count,
702 and use that ``F()`` object in the query:
703
704 .. code-block:: pycon
705
706 >>> from django.db.models import F
707 >>> Entry.objects.filter(number_of_comments__gt=F("number_of_pingbacks"))
708
709 Django supports the use of addition, subtraction, multiplication,
710 division, modulo, and power arithmetic with ``F()`` objects, both with constants
711 and with other ``F()`` objects. To find all the blog entries with more than
712 *twice* as many comments as pingbacks, we modify the query:
713
714 .. code-block:: pycon
715
716 >>> Entry.objects.filter(number_of_comments__gt=F("number_of_pingbacks") * 2)
717
718 To find all the entries where the rating of the entry is less than the
719 sum of the pingback count and comment count, we would issue the
720 query:
721
722 .. code-block:: pycon
723
724 >>> Entry.objects.filter(rating__lt=F("number_of_comments") + F("number_of_pingbacks"))
725
726 You can also use the double underscore notation to span relationships in
727 an ``F()`` object. An ``F()`` object with a double underscore will introduce
728 any joins needed to access the related object. For example, to retrieve all
729 the entries where the author's name is the same as the blog name, we could
730 issue the query:
731
732 .. code-block:: pycon
733
734 >>> Entry.objects.filter(authors__name=F("blog__name"))
735
736 For date and date/time fields, you can add or subtract a
737 :class:`~datetime.timedelta` object. The following would return all entries
738 that were modified more than 3 days after they were published:
739
740 .. code-block:: pycon
741
742 >>> from datetime import timedelta
743 >>> Entry.objects.filter(mod_date__gt=F("pub_date") + timedelta(days=3))
744
745 The ``F()`` objects support bitwise operations by ``.bitand()``, ``.bitor()``,
746 ``.bitxor()``, ``.bitrightshift()``, and ``.bitleftshift()``. For example:
747
748 .. code-block:: pycon
749
750 >>> F("somefield").bitand(16)
751
752 .. admonition:: Oracle
753
754 Oracle doesn't support bitwise XOR operation.
755
756 .. _using-transforms-in-expressions:
757
758 Expressions can reference transforms
759 ------------------------------------
760
761 Django supports using transforms in expressions.
762
763 For example, to find all ``Entry`` objects published in the same year as they
764 were last modified:
765
766 .. code-block:: pycon
767
768 >>> from django.db.models import F
769 >>> Entry.objects.filter(pub_date__year=F("mod_date__year"))
770
771 To find the earliest year an entry was published, we can issue the query:
772
773 .. code-block:: pycon
774
775 >>> from django.db.models import Min
776 >>> Entry.objects.aggregate(first_published_year=Min("pub_date__year"))
777
778 This example finds the value of the highest rated entry and the total number
779 of comments on all entries for each year:
780
781 .. code-block:: pycon
782
783 >>> from django.db.models import OuterRef, Subquery, Sum
784 >>> Entry.objects.values("pub_date__year").annotate(
785 ... top_rating=Subquery(
786 ... Entry.objects.filter(
787 ... pub_date__year=OuterRef("pub_date__year"),
788 ... )
789 ... .order_by("-rating")
790 ... .values("rating")[:1]
791 ... ),
792 ... total_comments=Sum("number_of_comments"),
793 ... )
794
795 The ``pk`` lookup shortcut
796 --------------------------
797
798 For convenience, Django provides a ``pk`` lookup shortcut, which stands for
799 "primary key".
800
801 In the example ``Blog`` model, the primary key is the ``id`` field, so these
802 three statements are equivalent:
803
804 .. code-block:: pycon
805
806 >>> Blog.objects.get(id__exact=14) # Explicit form
807 >>> Blog.objects.get(id=14) # __exact is implied
808 >>> Blog.objects.get(pk=14) # pk implies id__exact
809
810 The use of ``pk`` isn't limited to ``__exact`` queries -- any query term
811 can be combined with ``pk`` to perform a query on the primary key of a model:
812
813 .. code-block:: pycon
814
815 # Get blogs entries with id 1, 4 and 7
816 >>> Blog.objects.filter(pk__in=[1, 4, 7])
817
818 # Get all blog entries with id > 14
819 >>> Blog.objects.filter(pk__gt=14)
820
821 ``pk`` lookups also work across joins. For example, these three statements are
822 equivalent:
823
824 .. code-block:: pycon
825
826 >>> Entry.objects.filter(blog__id__exact=3) # Explicit form
827 >>> Entry.objects.filter(blog__id=3) # __exact is implied
828 >>> Entry.objects.filter(blog__pk=3) # __pk implies __id__exact
829
830 Escaping percent signs and underscores in ``LIKE`` statements
831 -------------------------------------------------------------
832
833 The field lookups that equate to ``LIKE`` SQL statements (``iexact``,
834 ``contains``, ``icontains``, ``startswith``, ``istartswith``, ``endswith``
835 and ``iendswith``) will automatically escape the two special characters used in
836 ``LIKE`` statements -- the percent sign and the underscore. (In a ``LIKE``
837 statement, the percent sign signifies a multiple-character wildcard and the
838 underscore signifies a single-character wildcard.)
839
840 This means things should work intuitively, so the abstraction doesn't leak.
841 For example, to retrieve all the entries that contain a percent sign, use the
842 percent sign as any other character:
843
844 .. code-block:: pycon
845
846 >>> Entry.objects.filter(headline__contains="%")
847
848 Django takes care of the quoting for you; the resulting SQL will look something
849 like this:
850
851 .. code-block:: sql
852
853 SELECT ... WHERE headline LIKE '%\%%';
854
855 Same goes for underscores. Both percentage signs and underscores are handled
856 for you transparently.
857
858 .. _caching-and-querysets:
859
860 Caching and ``QuerySet``\s
861 --------------------------
862
863 Each :class:`~django.db.models.query.QuerySet` contains a cache to minimize
864 database access. Understanding how it works will allow you to write the most
865 efficient code.
866
867 In a newly created :class:`~django.db.models.query.QuerySet`, the cache is
868 empty. The first time a :class:`~django.db.models.query.QuerySet` is evaluated
869 -- and, hence, a database query happens -- Django saves the query results in
870 the :class:`~django.db.models.query.QuerySet`’s cache and returns the results
871 that have been explicitly requested (e.g., the next element, if the
872 :class:`~django.db.models.query.QuerySet` is being iterated over). Subsequent
873 evaluations of the :class:`~django.db.models.query.QuerySet` reuse the cached
874 results.
875
876 Keep this caching behavior in mind, because it may bite you if you don't use
877 your :class:`~django.db.models.query.QuerySet`\s correctly. For example, the
878 following will create two :class:`~django.db.models.query.QuerySet`\s, evaluate
879 them, and throw them away:
880
881 .. code-block:: pycon
882
883 >>> print([e.headline for e in Entry.objects.all()])
884 >>> print([e.pub_date for e in Entry.objects.all()])
885
886 That means the same database query will be executed twice, effectively doubling
887 your database load. Also, there's a possibility the two lists may not include
888 the same database records, because an ``Entry`` may have been added or deleted
889 in the split second between the two requests.
890
891 To avoid this problem, save the :class:`~django.db.models.query.QuerySet` and
892 reuse it:
893
894 .. code-block:: pycon
895
896 >>> queryset = Entry.objects.all()
897 >>> print([p.headline for p in queryset]) # Evaluate the query set.
898 >>> print([p.pub_date for p in queryset]) # Reuse the cache from the evaluation.
899
900 When ``QuerySet``\s are not cached
901 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
902
903 Querysets do not always cache their results. When evaluating only *part* of
904 the queryset, the cache is checked, but if it is not populated then the items
905 returned by the subsequent query are not cached. Specifically, this means that
906 :ref:`limiting the queryset <limiting-querysets>` using an array slice or an
907 index will not populate the cache.
908
909 For example, repeatedly getting a certain index in a queryset object will query
910 the database each time:
911
912 .. code-block:: pycon
913
914 >>> queryset = Entry.objects.all()
915 >>> print(queryset[5]) # Queries the database
916 >>> print(queryset[5]) # Queries the database again
917
918 However, if the entire queryset has already been evaluated, the cache will be
919 checked instead:
920
921 .. code-block:: pycon
922
923 >>> queryset = Entry.objects.all()
924 >>> [entry for entry in queryset] # Queries the database
925 >>> print(queryset[5]) # Uses cache
926 >>> print(queryset[5]) # Uses cache
927
928 Here are some examples of other actions that will result in the entire queryset
929 being evaluated and therefore populate the cache:
930
931 .. code-block:: pycon
932
933 >>> [entry for entry in queryset]
934 >>> bool(queryset)
935 >>> entry in queryset
936 >>> list(queryset)
937
938 .. note::
939
940 Simply printing the queryset will not populate the cache. This is because
941 the call to ``__repr__()`` only returns a slice of the entire queryset.
942
943 .. _async-queries:
944
945 Asynchronous queries
946 ====================
947
948 If you are writing asynchronous views or code, you cannot use the ORM for
949 queries in quite the way we have described above, as you cannot call *blocking*
950 synchronous code from asynchronous code - it will block up the event loop
951 (or, more likely, Django will notice and raise a ``SynchronousOnlyOperation``
952 to stop that from happening).
953
954 Fortunately, you can do many queries using Django's asynchronous query APIs.
955 Every method that might block - such as ``get()`` or ``delete()`` - has an
956 asynchronous variant (``aget()`` or ``adelete()``), and when you iterate over
957 results, you can use asynchronous iteration (``async for``) instead.
958
959 Query iteration
960 ---------------
961
962 The default way of iterating over a query - with ``for`` - will result in a
963 blocking database query behind the scenes as Django loads the results at
964 iteration time. To fix this, you can swap to ``async for``::
965
966 async for entry in Authors.objects.filter(name__startswith="A"):
967 ...
968
969 Be aware that you also can't do other things that might iterate over the
970 queryset, such as wrapping ``list()`` around it to force its evaluation (you
971 can use ``async for`` in a comprehension, if you want it).
972
973 Because ``QuerySet`` methods like ``filter()`` and ``exclude()`` do not
974 actually run the query - they set up the queryset to run when it's iterated
975 over - you can use those freely in asynchronous code. For a guide to which
976 methods can keep being used like this, and which have asynchronous versions,
977 read the next section.
978
979 ``QuerySet`` and manager methods
980 --------------------------------
981
982 Some methods on managers and querysets - like ``get()`` and ``first()`` - force
983 execution of the queryset and are blocking. Some, like ``filter()`` and
984 ``exclude()``, don't force execution and so are safe to run from asynchronous
985 code. But how are you supposed to tell the difference?
986
987 While you could poke around and see if there is an ``a``-prefixed version of
988 the method (for example, we have ``aget()`` but not ``afilter()``), there is a
989 more logical way - look up what kind of method it is in the
990 :doc:`QuerySet reference </ref/models/querysets>`.
991
992 In there, you'll find the methods on QuerySets grouped into two sections:
993
994 * *Methods that return new querysets*: These are the non-blocking ones,
995 and don't have asynchronous versions. You're free to use these in any
996 situation, though read the notes on ``defer()`` and ``only()`` before you use
997 them.
998
999 * *Methods that do not return querysets*: These are the blocking ones, and
1000 have asynchronous versions - the asynchronous name for each is noted in its
1001 documentation, though our standard pattern is to add an ``a`` prefix.
1002
1003 Using this distinction, you can work out when you need to use asynchronous
1004 versions, and when you don't. For example, here's a valid asynchronous query::
1005
1006 user = await User.objects.filter(username=my_input).afirst()
1007
1008 ``filter()`` returns a queryset, and so it's fine to keep chaining it inside an
1009 asynchronous environment, whereas ``first()`` evaluates and returns a model
1010 instance - thus, we change to ``afirst()``, and use ``await`` at the front of
1011 the whole expression in order to call it in an asynchronous-friendly way.
1012
1013 .. note::
1014
1015 If you forget to put the ``await`` part in, you may see errors like
1016 *"coroutine object has no attribute x"* or *"<coroutine …>"* strings in
1017 place of your model instances. If you ever see these, you are missing an
1018 ``await`` somewhere to turn that coroutine into a real value.
1019
1020 Transactions
1021 ------------
1022
1023 Transactions are **not** currently supported with asynchronous queries and
1024 updates. You will find that trying to use one raises
1025 ``SynchronousOnlyOperation``.
1026
1027 If you wish to use a transaction, we suggest you write your ORM code inside a
1028 separate, synchronous function and then call that using ``sync_to_async`` - see
1029 :doc:`/topics/async` for more.
1030
1031 .. _querying-jsonfield:
1032
1033 Querying ``JSONField``
1034 ======================
1035
1036 Lookups implementation is different in :class:`~django.db.models.JSONField`,
1037 mainly due to the existence of key transformations. To demonstrate, we will use
1038 the following example model::
1039
1040 from django.db import models
1041
1042
1043 class Dog(models.Model):
1044 name = models.CharField(max_length=200)
1045 data = models.JSONField(null=True)
1046
1047 def __str__(self):
1048 return self.name
1049
1050 Storing and querying for ``None``
1051 ---------------------------------
1052
1053 As with other fields, storing ``None`` as the field's value will store it as
1054 SQL ``NULL``. While not recommended, it is possible to store JSON scalar
1055 ``null`` instead of SQL ``NULL`` by using :class:`Value(None, JSONField())
1056 <django.db.models.Value>`.
1057
1058 Whichever of the values is stored, when retrieved from the database, the Python
1059 representation of the JSON scalar ``null`` is the same as SQL ``NULL``, i.e.
1060 ``None``. Therefore, it can be hard to distinguish between them.
1061
1062 This only applies to ``None`` as the top-level value of the field. If ``None``
1063 is inside a :py:class:`list` or :py:class:`dict`, it will always be interpreted
1064 as JSON ``null``.
1065
1066 When querying, ``None`` value will always be interpreted as JSON ``null``. To
1067 query for SQL ``NULL``, use :lookup:`isnull`:
1068
1069 .. code-block:: pycon
1070
1071 >>> Dog.objects.create(name="Max", data=None) # SQL NULL.
1072 <Dog: Max>
1073 >>> Dog.objects.create(name="Archie", data=Value(None, JSONField())) # JSON null.
1074 <Dog: Archie>
1075 >>> Dog.objects.filter(data=None)
1076 <QuerySet [<Dog: Archie>]>
1077 >>> Dog.objects.filter(data=Value(None, JSONField()))
1078 <QuerySet [<Dog: Archie>]>
1079 >>> Dog.objects.filter(data__isnull=True)
1080 <QuerySet [<Dog: Max>]>
1081 >>> Dog.objects.filter(data__isnull=False)
1082 <QuerySet [<Dog: Archie>]>
1083
1084 Unless you are sure you wish to work with SQL ``NULL`` values, consider setting
1085 ``null=False`` and providing a suitable default for empty values, such as
1086 ``default=dict``.
1087
1088 .. note::
1089
1090 Storing JSON scalar ``null`` does not violate :attr:`null=False
1091 <django.db.models.Field.null>`.
1092
1093 .. fieldlookup:: jsonfield.key
1094
1095 Key, index, and path transforms
1096 -------------------------------
1097
1098 To query based on a given dictionary key, use that key as the lookup name:
1099
1100 .. code-block:: pycon
1101
1102 >>> Dog.objects.create(
1103 ... name="Rufus",
1104 ... data={
1105 ... "breed": "labrador",
1106 ... "owner": {
1107 ... "name": "Bob",
1108 ... "other_pets": [
1109 ... {
1110 ... "name": "Fishy",
1111 ... }
1112 ... ],
1113 ... },
1114 ... },
1115 ... )
1116 <Dog: Rufus>
1117 >>> Dog.objects.create(name="Meg", data={"breed": "collie", "owner": None})
1118 <Dog: Meg>
1119 >>> Dog.objects.filter(data__breed="collie")
1120 <QuerySet [<Dog: Meg>]>
1121
1122 Multiple keys can be chained together to form a path lookup:
1123
1124 .. code-block:: pycon
1125
1126 >>> Dog.objects.filter(data__owner__name="Bob")
1127 <QuerySet [<Dog: Rufus>]>
1128
1129 If the key is an integer, it will be interpreted as an index transform in an
1130 array:
1131
1132 .. code-block:: pycon
1133
1134 >>> Dog.objects.filter(data__owner__other_pets__0__name="Fishy")
1135 <QuerySet [<Dog: Rufus>]>
1136
1137 If the key you wish to query by clashes with the name of another lookup, use
1138 the :lookup:`contains <jsonfield.contains>` lookup instead.
1139
1140 To query for missing keys, use the ``isnull`` lookup:
1141
1142 .. code-block:: pycon
1143
1144 >>> Dog.objects.create(name="Shep", data={"breed": "collie"})
1145 <Dog: Shep>
1146 >>> Dog.objects.filter(data__owner__isnull=True)
1147 <QuerySet [<Dog: Shep>]>
1148
1149 .. note::
1150
1151 The lookup examples given above implicitly use the :lookup:`exact` lookup.
1152 Key, index, and path transforms can also be chained with:
1153 :lookup:`icontains`, :lookup:`endswith`, :lookup:`iendswith`,
1154 :lookup:`iexact`, :lookup:`regex`, :lookup:`iregex`, :lookup:`startswith`,
1155 :lookup:`istartswith`, :lookup:`lt`, :lookup:`lte`, :lookup:`gt`, and
1156 :lookup:`gte`, as well as with :ref:`containment-and-key-lookups`.
1157
1158 ``KT()`` expressions
1159 ~~~~~~~~~~~~~~~~~~~~
1160
1161 .. module:: django.db.models.fields.json
1162
1163 .. class:: KT(lookup)
1164
1165 Represents the text value of a key, index, or path transform of
1166 :class:`~django.db.models.JSONField`. You can use the double underscore
1167 notation in ``lookup`` to chain dictionary key and index transforms.
1168
1169 For example:
1170
1171 .. code-block:: pycon
1172
1173 >>> from django.db.models.fields.json import KT
1174 >>> Dog.objects.create(
1175 ... name="Shep",
1176 ... data={
1177 ... "owner": {"name": "Bob"},
1178 ... "breed": ["collie", "lhasa apso"],
1179 ... },
1180 ... )
1181 <Dog: Shep>
1182 >>> Dog.objects.annotate(
1183 ... first_breed=KT("data__breed__1"), owner_name=KT("data__owner__name")
1184 ... ).filter(first_breed__startswith="lhasa", owner_name="Bob")
1185 <QuerySet [<Dog: Shep>]>
1186
1187 .. note::
1188
1189 Due to the way in which key-path queries work,
1190 :meth:`~django.db.models.query.QuerySet.exclude` and
1191 :meth:`~django.db.models.query.QuerySet.filter` are not guaranteed to
1192 produce exhaustive sets. If you want to include objects that do not have
1193 the path, add the ``isnull`` lookup.
1194
1195 .. warning::
1196
1197 Since any string could be a key in a JSON object, any lookup other than
1198 those listed below will be interpreted as a key lookup. No errors are
1199 raised. Be extra careful for typing mistakes, and always check your queries
1200 work as you intend.
1201
1202 .. admonition:: MariaDB and Oracle users
1203
1204 Using :meth:`~django.db.models.query.QuerySet.order_by` on key, index, or
1205 path transforms will sort the objects using the string representation of
1206 the values. This is because MariaDB and Oracle Database do not provide a
1207 function that converts JSON values into their equivalent SQL values.
1208
1209 .. admonition:: Oracle users
1210
1211 On Oracle Database, using ``None`` as the lookup value in an
1212 :meth:`~django.db.models.query.QuerySet.exclude` query will return objects
1213 that do not have ``null`` as the value at the given path, including objects
1214 that do not have the path. On other database backends, the query will
1215 return objects that have the path and the value is not ``null``.
1216
1217 .. admonition:: PostgreSQL users
1218
1219 On PostgreSQL, if only one key or index is used, the SQL operator ``->`` is
1220 used. If multiple operators are used then the ``#>`` operator is used.
1221
1222 .. admonition:: SQLite users
1223
1224 On SQLite, ``"true"``, ``"false"``, and ``"null"`` string values will
1225 always be interpreted as ``True``, ``False``, and JSON ``null``
1226 respectively.
1227
1228 .. _containment-and-key-lookups:
1229
1230 Containment and key lookups
1231 ---------------------------
1232
1233 .. fieldlookup:: jsonfield.contains
1234
1235 ``contains``
1236 ~~~~~~~~~~~~
1237
1238 The :lookup:`contains` lookup is overridden on ``JSONField``. The returned
1239 objects are those where the given ``dict`` of key-value pairs are all
1240 contained in the top-level of the field. For example:
1241
1242 .. code-block:: pycon
1243
1244 >>> Dog.objects.create(name="Rufus", data={"breed": "labrador", "owner": "Bob"})
1245 <Dog: Rufus>
1246 >>> Dog.objects.create(name="Meg", data={"breed": "collie", "owner": "Bob"})
1247 <Dog: Meg>
1248 >>> Dog.objects.create(name="Fred", data={})
1249 <Dog: Fred>
1250 >>> Dog.objects.create(
1251 ... name="Merry", data={"breed": "pekingese", "tricks": ["fetch", "dance"]}
1252 ... )
1253 >>> Dog.objects.filter(data__contains={"owner": "Bob"})
1254 <QuerySet [<Dog: Rufus>, <Dog: Meg>]>
1255 >>> Dog.objects.filter(data__contains={"breed": "collie"})
1256 <QuerySet [<Dog: Meg>]>
1257 >>> Dog.objects.filter(data__contains={"tricks": ["dance"]})
1258 <QuerySet [<Dog: Merry>]>
1259
1260 .. admonition:: Oracle and SQLite
1261
1262 ``contains`` is not supported on Oracle and SQLite.
1263
1264 .. fieldlookup:: jsonfield.contained_by
1265
1266 ``contained_by``
1267 ~~~~~~~~~~~~~~~~
1268
1269 This is the inverse of the :lookup:`contains <jsonfield.contains>` lookup - the
1270 objects returned will be those where the key-value pairs on the object are a
1271 subset of those in the value passed. For example:
1272
1273 .. code-block:: pycon
1274
1275 >>> Dog.objects.create(name="Rufus", data={"breed": "labrador", "owner": "Bob"})
1276 <Dog: Rufus>
1277 >>> Dog.objects.create(name="Meg", data={"breed": "collie", "owner": "Bob"})
1278 <Dog: Meg>
1279 >>> Dog.objects.create(name="Fred", data={})
1280 <Dog: Fred>
1281 >>> Dog.objects.create(
1282 ... name="Merry", data={"breed": "pekingese", "tricks": ["fetch", "dance"]}
1283 ... )
1284 >>> Dog.objects.filter(data__contained_by={"breed": "collie", "owner": "Bob"})
1285 <QuerySet [<Dog: Meg>, <Dog: Fred>]>
1286 >>> Dog.objects.filter(data__contained_by={"breed": "collie"})
1287 <QuerySet [<Dog: Fred>]>
1288 >>> Dog.objects.filter(
1289 ... data__contained_by={"breed": "pekingese", "tricks": ["dance", "fetch", "hug"]}
1290 ... )
1291 <QuerySet [<Dog: Merry>, <Dog: Fred>]>
1292
1293 .. admonition:: Oracle and SQLite
1294
1295 ``contained_by`` is not supported on Oracle and SQLite.
1296
1297 .. fieldlookup:: jsonfield.has_key
1298
1299 ``has_key``
1300 ~~~~~~~~~~~
1301
1302 Returns objects where the given key is in the top-level of the data. For
1303 example:
1304
1305 .. code-block:: pycon
1306
1307 >>> Dog.objects.create(name="Rufus", data={"breed": "labrador"})
1308 <Dog: Rufus>
1309 >>> Dog.objects.create(name="Meg", data={"breed": "collie", "owner": "Bob"})
1310 <Dog: Meg>
1311 >>> Dog.objects.filter(data__has_key="owner")
1312 <QuerySet [<Dog: Meg>]>
1313
1314 .. fieldlookup:: jsonfield.has_keys
1315
1316 ``has_keys``
1317 ~~~~~~~~~~~~
1318
1319 Returns objects where all of the given keys are in the top-level of the data.
1320 For example:
1321
1322 .. code-block:: pycon
1323
1324 >>> Dog.objects.create(name="Rufus", data={"breed": "labrador"})
1325 <Dog: Rufus>
1326 >>> Dog.objects.create(name="Meg", data={"breed": "collie", "owner": "Bob"})
1327 <Dog: Meg>
1328 >>> Dog.objects.filter(data__has_keys=["breed", "owner"])
1329 <QuerySet [<Dog: Meg>]>
1330
1331 .. fieldlookup:: jsonfield.has_any_keys
1332
1333 ``has_any_keys``
1334 ~~~~~~~~~~~~~~~~
1335
1336 Returns objects where any of the given keys are in the top-level of the data.
1337 For example:
1338
1339 .. code-block:: pycon
1340
1341 >>> Dog.objects.create(name="Rufus", data={"breed": "labrador"})
1342 <Dog: Rufus>
1343 >>> Dog.objects.create(name="Meg", data={"owner": "Bob"})
1344 <Dog: Meg>
1345 >>> Dog.objects.filter(data__has_any_keys=["owner", "breed"])
1346 <QuerySet [<Dog: Rufus>, <Dog: Meg>]>
1347
1348 .. _complex-lookups-with-q:
1349
1350 Complex lookups with ``Q`` objects
1351 ==================================
1352
1353 Keyword argument queries -- in :meth:`~django.db.models.query.QuerySet.filter`,
1354 etc. -- are "AND"ed together. If you need to execute more complex queries (for
1355 example, queries with ``OR`` statements), you can use :class:`Q objects <django.db.models.Q>`.
1356
1357 A :class:`Q object <django.db.models.Q>` (``django.db.models.Q``) is an object
1358 used to encapsulate a collection of keyword arguments. These keyword arguments
1359 are specified as in "Field lookups" above.
1360
1361 For example, this ``Q`` object encapsulates a single ``LIKE`` query::
1362
1363 from django.db.models import Q
1364
1365 Q(question__startswith="What")
1366
1367 ``Q`` objects can be combined using the ``&``, ``|``, and ``^`` operators. When
1368 an operator is used on two ``Q`` objects, it yields a new ``Q`` object.
1369
1370 For example, this statement yields a single ``Q`` object that represents the
1371 "OR" of two ``"question__startswith"`` queries::
1372
1373 Q(question__startswith="Who") | Q(question__startswith="What")
1374
1375 This is equivalent to the following SQL ``WHERE`` clause:
1376
1377 .. code-block:: sql
1378
1379 WHERE question LIKE 'Who%' OR question LIKE 'What%'
1380
1381 You can compose statements of arbitrary complexity by combining ``Q`` objects
1382 with the ``&``, ``|``, and ``^`` operators and use parenthetical grouping.
1383 Also, ``Q`` objects can be negated using the ``~`` operator, allowing for
1384 combined lookups that combine both a normal query and a negated (``NOT``)
1385 query::
1386
1387 Q(question__startswith="Who") | ~Q(pub_date__year=2005)
1388
1389 Each lookup function that takes keyword-arguments
1390 (e.g. :meth:`~django.db.models.query.QuerySet.filter`,
1391 :meth:`~django.db.models.query.QuerySet.exclude`,
1392 :meth:`~django.db.models.query.QuerySet.get`) can also be passed one or more
1393 ``Q`` objects as positional (not-named) arguments. If you provide multiple
1394 ``Q`` object arguments to a lookup function, the arguments will be "AND"ed
1395 together. For example::
1396
1397 Poll.objects.get(
1398 Q(question__startswith="Who"),
1399 Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)),
1400 )
1401
1402 ... roughly translates into the SQL:
1403
1404 .. code-block:: sql
1405
1406 SELECT * from polls WHERE question LIKE 'Who%'
1407 AND (pub_date = '2005-05-02' OR pub_date = '2005-05-06')
1408
1409 Lookup functions can mix the use of ``Q`` objects and keyword arguments. All
1410 arguments provided to a lookup function (be they keyword arguments or ``Q``
1411 objects) are "AND"ed together. However, if a ``Q`` object is provided, it must
1412 precede the definition of any keyword arguments. For example::
1413
1414 Poll.objects.get(
1415 Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)),
1416 question__startswith="Who",
1417 )
1418
1419 ... would be a valid query, equivalent to the previous example; but::
1420
1421 # INVALID QUERY
1422 Poll.objects.get(
1423 question__startswith="Who",
1424 Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)),
1425 )
1426
1427 ... would not be valid.
1428
1429 .. seealso::
1430
1431 The :source:`OR lookups examples <tests/or_lookups/tests.py>` in Django's
1432 unit tests show some possible uses of ``Q``.
1433
1434 Comparing objects
1435 =================
1436
1437 To compare two model instances, use the standard Python comparison operator,
1438 the double equals sign: ``==``. Behind the scenes, that compares the primary
1439 key values of two models.
1440
1441 Using the ``Entry`` example above, the following two statements are equivalent:
1442
1443 .. code-block:: pycon
1444
1445 >>> some_entry == other_entry
1446 >>> some_entry.id == other_entry.id
1447
1448 If a model's primary key isn't called ``id``, no problem. Comparisons will
1449 always use the primary key, whatever it's called. For example, if a model's
1450 primary key field is called ``name``, these two statements are equivalent:
1451
1452 .. code-block:: pycon
1453
1454 >>> some_obj == other_obj
1455 >>> some_obj.name == other_obj.name
1456
1457 .. _topics-db-queries-delete:
1458
1459 Deleting objects
1460 ================
1461
1462 The delete method, conveniently, is named
1463 :meth:`~django.db.models.Model.delete`. This method immediately deletes the
1464 object and returns the number of objects deleted and a dictionary with
1465 the number of deletions per object type. Example:
1466
1467 .. code-block:: pycon
1468
1469 >>> e.delete()
1470 (1, {'blog.Entry': 1})
1471
1472 You can also delete objects in bulk. Every
1473 :class:`~django.db.models.query.QuerySet` has a
1474 :meth:`~django.db.models.query.QuerySet.delete` method, which deletes all
1475 members of that :class:`~django.db.models.query.QuerySet`.
1476
1477 For example, this deletes all ``Entry`` objects with a ``pub_date`` year of
1478 2005:
1479
1480 .. code-block:: pycon
1481
1482 >>> Entry.objects.filter(pub_date__year=2005).delete()
1483 (5, {'webapp.Entry': 5})
1484
1485 Keep in mind that this will, whenever possible, be executed purely in SQL, and
1486 so the ``delete()`` methods of individual object instances will not necessarily
1487 be called during the process. If you've provided a custom ``delete()`` method
1488 on a model class and want to ensure that it is called, you will need to
1489 "manually" delete instances of that model (e.g., by iterating over a
1490 :class:`~django.db.models.query.QuerySet` and calling ``delete()`` on each
1491 object individually) rather than using the bulk
1492 :meth:`~django.db.models.query.QuerySet.delete` method of a
1493 :class:`~django.db.models.query.QuerySet`.
1494
1495 When Django deletes an object, by default it emulates the behavior of the SQL
1496 constraint ``ON DELETE CASCADE`` -- in other words, any objects which had
1497 foreign keys pointing at the object to be deleted will be deleted along with
1498 it. For example::
1499
1500 b = Blog.objects.get(pk=1)
1501 # This will delete the Blog and all of its Entry objects.
1502 b.delete()
1503
1504 This cascade behavior is customizable via the
1505 :attr:`~django.db.models.ForeignKey.on_delete` argument to the
1506 :class:`~django.db.models.ForeignKey`.
1507
1508 Note that :meth:`~django.db.models.query.QuerySet.delete` is the only
1509 :class:`~django.db.models.query.QuerySet` method that is not exposed on a
1510 :class:`~django.db.models.Manager` itself. This is a safety mechanism to
1511 prevent you from accidentally requesting ``Entry.objects.delete()``, and
1512 deleting *all* the entries. If you *do* want to delete all the objects, then
1513 you have to explicitly request a complete query set::
1514
1515 Entry.objects.all().delete()
1516
1517 .. _topics-db-queries-copy:
1518
1519 Copying model instances
1520 =======================
1521
1522 Although there is no built-in method for copying model instances, it is
1523 possible to easily create new instance with all fields' values copied. In the
1524 simplest case, you can set ``pk`` to ``None`` and
1525 :attr:`_state.adding <django.db.models.Model._state>` to ``True``. Using our
1526 blog example::
1527
1528 blog = Blog(name="My blog", tagline="Blogging is easy")
1529 blog.save() # blog.pk == 1
1530
1531 blog.pk = None
1532 blog._state.adding = True
1533 blog.save() # blog.pk == 2
1534
1535 Things get more complicated if you use inheritance. Consider a subclass of
1536 ``Blog``::
1537
1538 class ThemeBlog(Blog):
1539 theme = models.CharField(max_length=200)
1540
1541
1542 django_blog = ThemeBlog(name="Django", tagline="Django is easy", theme="python")
1543 django_blog.save() # django_blog.pk == 3
1544
1545 Due to how inheritance works, you have to set both ``pk`` and ``id`` to
1546 ``None``, and ``_state.adding`` to ``True``::
1547
1548 django_blog.pk = None
1549 django_blog.id = None
1550 django_blog._state.adding = True
1551 django_blog.save() # django_blog.pk == 4
1552
1553 This process doesn't copy relations that aren't part of the model's database
1554 table. For example, ``Entry`` has a ``ManyToManyField`` to ``Author``. After
1555 duplicating an entry, you must set the many-to-many relations for the new
1556 entry::
1557
1558 entry = Entry.objects.all()[0] # some previous entry
1559 old_authors = entry.authors.all()
1560 entry.pk = None
1561 entry._state.adding = True
1562 entry.save()
1563 entry.authors.set(old_authors)
1564
1565 For a ``OneToOneField``, you must duplicate the related object and assign it
1566 to the new object's field to avoid violating the one-to-one unique constraint.
1567 For example, assuming ``entry`` is already duplicated as above::
1568
1569 detail = EntryDetail.objects.all()[0]
1570 detail.pk = None
1571 detail._state.adding = True
1572 detail.entry = entry
1573 detail.save()
1574
1575 .. _topics-db-queries-update:
1576
1577 Updating multiple objects at once
1578 =================================
1579
1580 Sometimes you want to set a field to a particular value for all the objects in
1581 a :class:`~django.db.models.query.QuerySet`. You can do this with the
1582 :meth:`~django.db.models.query.QuerySet.update` method. For example::
1583
1584 # Update all the headlines with pub_date in 2007.
1585 Entry.objects.filter(pub_date__year=2007).update(headline="Everything is the same")
1586
1587 You can only set non-relation fields and :class:`~django.db.models.ForeignKey`
1588 fields using this method. To update a non-relation field, provide the new value
1589 as a constant. To update :class:`~django.db.models.ForeignKey` fields, set the
1590 new value to be the new model instance you want to point to. For example:
1591
1592 .. code-block:: pycon
1593
1594 >>> b = Blog.objects.get(pk=1)
1595
1596 # Change every Entry so that it belongs to this Blog.
1597 >>> Entry.objects.update(blog=b)
1598
1599 The ``update()`` method is applied instantly and returns the number of rows
1600 matched by the query (which may not be equal to the number of rows updated if
1601 some rows already have the new value). The only restriction on the
1602 :class:`~django.db.models.query.QuerySet` being updated is that it can only
1603 access one database table: the model's main table. You can filter based on
1604 related fields, but you can only update columns in the model's main
1605 table. Example:
1606
1607 .. code-block:: pycon
1608
1609 >>> b = Blog.objects.get(pk=1)
1610
1611 # Update all the headlines belonging to this Blog.
1612 >>> Entry.objects.filter(blog=b).update(headline="Everything is the same")
1613
1614 Be aware that the ``update()`` method is converted directly to an SQL
1615 statement. It is a bulk operation for direct updates. It doesn't run any
1616 :meth:`~django.db.models.Model.save` methods on your models, or emit the
1617 ``pre_save`` or ``post_save`` signals (which are a consequence of calling
1618 :meth:`~django.db.models.Model.save`), or honor the
1619 :attr:`~django.db.models.DateField.auto_now` field option.
1620 If you want to save every item in a :class:`~django.db.models.query.QuerySet`
1621 and make sure that the :meth:`~django.db.models.Model.save` method is called on
1622 each instance, you don't need any special function to handle that. Loop over
1623 them and call :meth:`~django.db.models.Model.save`::
1624
1625 for item in my_queryset:
1626 item.save()
1627
1628 Calls to update can also use :class:`F expressions <django.db.models.F>` to
1629 update one field based on the value of another field in the model. This is
1630 especially useful for incrementing counters based upon their current value. For
1631 example, to increment the pingback count for every entry in the blog:
1632
1633 .. code-block:: pycon
1634
1635 >>> Entry.objects.update(number_of_pingbacks=F("number_of_pingbacks") + 1)
1636
1637 However, unlike ``F()`` objects in filter and exclude clauses, you can't
1638 introduce joins when you use ``F()`` objects in an update -- you can only
1639 reference fields local to the model being updated. If you attempt to introduce
1640 a join with an ``F()`` object, a ``FieldError`` will be raised:
1641
1642 .. code-block:: pycon
1643
1644 # This will raise a FieldError
1645 >>> Entry.objects.update(headline=F("blog__name"))
1646
1647 .. _topics-db-queries-related:
1648
1649 Related objects
1650 ===============
1651
1652 When you define a relationship in a model (i.e., a
1653 :class:`~django.db.models.ForeignKey`,
1654 :class:`~django.db.models.OneToOneField`, or
1655 :class:`~django.db.models.ManyToManyField`), instances of that model will have
1656 a convenient API to access the related object(s).
1657
1658 Using the models at the top of this page, for example, an ``Entry`` object ``e``
1659 can get its associated ``Blog`` object by accessing the ``blog`` attribute:
1660 ``e.blog``.
1661
1662 (Behind the scenes, this functionality is implemented by Python
1663 :doc:`descriptors <python:howto/descriptor>`. This shouldn't really matter to
1664 you, but we point it out here for the curious.)
1665
1666 Django also creates API accessors for the "other" side of the relationship --
1667 the link from the related model to the model that defines the relationship.
1668 For example, a ``Blog`` object ``b`` has access to a list of all related
1669 ``Entry`` objects via the ``entry_set`` attribute: ``b.entry_set.all()``.
1670
1671 All examples in this section use the sample ``Blog``, ``Author`` and ``Entry``
1672 models defined at the top of this page.
1673
1674 One-to-many relationships
1675 -------------------------
1676
1677 Forward
1678 ~~~~~~~
1679
1680 If a model has a :class:`~django.db.models.ForeignKey`, instances of that model
1681 will have access to the related (foreign) object via an attribute of the model.
1682
1683 Example:
1684
1685 .. code-block:: pycon
1686
1687 >>> e = Entry.objects.get(id=2)
1688 >>> e.blog # Returns the related Blog object.
1689
1690 You can get and set via a foreign-key attribute. As you may expect, changes to
1691 the foreign key aren't saved to the database until you call
1692 :meth:`~django.db.models.Model.save`. Example:
1693
1694 .. code-block:: pycon
1695
1696 >>> e = Entry.objects.get(id=2)
1697 >>> e.blog = some_blog
1698 >>> e.save()
1699
1700 If a :class:`~django.db.models.ForeignKey` field has ``null=True`` set (i.e.,
1701 it allows ``NULL`` values), you can assign ``None`` to remove the relation.
1702 Example:
1703
1704 .. code-block:: pycon
1705
1706 >>> e = Entry.objects.get(id=2)
1707 >>> e.blog = None
1708 >>> e.save() # "UPDATE blog_entry SET blog_id = NULL ...;"
1709
1710 Forward access to one-to-many relationships is cached the first time the
1711 related object is accessed. Subsequent accesses to the foreign key on the same
1712 object instance are cached. Example:
1713
1714 .. code-block:: pycon
1715
1716 >>> e = Entry.objects.get(id=2)
1717 >>> print(e.blog) # Hits the database to retrieve the associated Blog.
1718 >>> print(e.blog) # Doesn't hit the database; uses cached version.
1719
1720 Note that the :meth:`~django.db.models.query.QuerySet.select_related`
1721 :class:`~django.db.models.query.QuerySet` method recursively prepopulates the
1722 cache of all one-to-many relationships ahead of time. Example:
1723
1724 .. code-block:: pycon
1725
1726 >>> e = Entry.objects.select_related().get(id=2)
1727 >>> print(e.blog) # Doesn't hit the database; uses cached version.
1728 >>> print(e.blog) # Doesn't hit the database; uses cached version.
1729
1730 .. _backwards-related-objects:
1731
1732 Following relationships "backward"
1733 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1734
1735 If a model has a :class:`~django.db.models.ForeignKey`, instances of the
1736 foreign-key model will have access to a :class:`~django.db.models.Manager` that
1737 returns all instances of the first model. By default, this
1738 :class:`~django.db.models.Manager` is named ``FOO_set``, where ``FOO`` is the
1739 source model name, lowercased. This :class:`~django.db.models.Manager` returns
1740 ``QuerySet`` instances, which can be filtered and manipulated as described in
1741 the "Retrieving objects" section above.
1742
1743 Example:
1744
1745 .. code-block:: pycon
1746
1747 >>> b = Blog.objects.get(id=1)
1748 >>> b.entry_set.all() # Returns all Entry objects related to Blog.
1749
1750 # b.entry_set is a Manager that returns QuerySets.
1751 >>> b.entry_set.filter(headline__contains="Lennon")
1752 >>> b.entry_set.count()
1753
1754 You can override the ``FOO_set`` name by setting the
1755 :attr:`~django.db.models.ForeignKey.related_name` parameter in the
1756 :class:`~django.db.models.ForeignKey` definition. For example, if the ``Entry``
1757 model was altered to ``blog = ForeignKey(Blog, on_delete=models.CASCADE,
1758 related_name='entries')``, the above example code would look like this:
1759
1760 .. code-block:: pycon
1761
1762 >>> b = Blog.objects.get(id=1)
1763 >>> b.entries.all() # Returns all Entry objects related to Blog.
1764
1765 # b.entries is a Manager that returns ``QuerySet`` instances.
1766 >>> b.entries.filter(headline__contains="Lennon")
1767 >>> b.entries.count()
1768
1769 .. _using-custom-reverse-manager:
1770
1771 Using a custom reverse manager
1772 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1773
1774 By default the :class:`~django.db.models.fields.related.RelatedManager` used
1775 for reverse relations is a subclass of the :ref:`default manager <manager-names>`
1776 for that model. If you would like to specify a different manager for a given
1777 query you can use the following syntax::
1778
1779 from django.db import models
1780
1781
1782 class Entry(models.Model):
1783 # ...
1784 objects = models.Manager() # Default Manager
1785 entries = EntryManager() # Custom Manager
1786
1787
1788 b = Blog.objects.get(id=1)
1789 b.entry_set(manager="entries").all()
1790
1791 If ``EntryManager`` performed default filtering in its ``get_queryset()``
1792 method, that filtering would apply to the ``all()`` call.
1793
1794 Specifying a custom reverse manager also enables you to call its custom
1795 methods::
1796
1797 b.entry_set(manager="entries").is_published()
1798
1799 .. admonition:: Interaction with prefetching
1800
1801 When calling :meth:`~django.db.models.query.QuerySet.prefetch_related` with
1802 a reverse relation, the default manager will be used. If you want to
1803 prefetch related objects using a custom reverse manager, use
1804 :class:`Prefetch() <django.db.models.Prefetch>`. For example::
1805
1806 from django.db.models import Prefetch
1807
1808 prefetch_manager = Prefetch("entry_set", queryset=Entry.entries.all())
1809 Blog.objects.prefetch_related(prefetch_manager)
1810
1811 Additional methods to handle related objects
1812 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1813
1814 In addition to the :class:`~django.db.models.query.QuerySet` methods defined in
1815 "Retrieving objects" above, the :class:`~django.db.models.ForeignKey`
1816 :class:`~django.db.models.Manager` has additional methods used to handle the
1817 set of related objects. A synopsis of each is below, and complete details can
1818 be found in the :doc:`related objects reference </ref/models/relations>`.
1819
1820 ``add(obj1, obj2, ...)``
1821 Adds the specified model objects to the related object set.
1822
1823 ``create(**kwargs)``
1824 Creates a new object, saves it and puts it in the related object set.
1825 Returns the newly created object.
1826
1827 ``remove(obj1, obj2, ...)``
1828 Removes the specified model objects from the related object set.
1829
1830 ``clear()``
1831 Removes all objects from the related object set.
1832
1833 ``set(objs)``
1834 Replace the set of related objects.
1835
1836 To assign the members of a related set, use the ``set()`` method with an
1837 iterable of object instances. For example, if ``e1`` and ``e2`` are ``Entry``
1838 instances::
1839
1840 b = Blog.objects.get(id=1)
1841 b.entry_set.set([e1, e2])
1842
1843 If the ``clear()`` method is available, any preexisting objects will be
1844 removed from the ``entry_set`` before all objects in the iterable (in this
1845 case, a list) are added to the set. If the ``clear()`` method is *not*
1846 available, all objects in the iterable will be added without removing any
1847 existing elements.
1848
1849 Each "reverse" operation described in this section has an immediate effect on
1850 the database. Every addition, creation and deletion is immediately and
1851 automatically saved to the database.
1852
1853 .. _m2m-reverse-relationships:
1854
1855 Many-to-many relationships
1856 --------------------------
1857
1858 Both ends of a many-to-many relationship get automatic API access to the other
1859 end. The API works similar to a "backward" one-to-many relationship, above.
1860
1861 One difference is in the attribute naming: The model that defines the
1862 :class:`~django.db.models.ManyToManyField` uses the attribute name of that
1863 field itself, whereas the "reverse" model uses the lowercased model name of the
1864 original model, plus ``'_set'`` (just like reverse one-to-many relationships).
1865
1866 An example makes this easier to understand::
1867
1868 e = Entry.objects.get(id=3)
1869 e.authors.all() # Returns all Author objects for this Entry.
1870 e.authors.count()
1871 e.authors.filter(name__contains="John")
1872
1873 a = Author.objects.get(id=5)
1874 a.entry_set.all() # Returns all Entry objects for this Author.
1875
1876 Like :class:`~django.db.models.ForeignKey`,
1877 :class:`~django.db.models.ManyToManyField` can specify
1878 :attr:`~django.db.models.ManyToManyField.related_name`. In the above example,
1879 if the :class:`~django.db.models.ManyToManyField` in ``Entry`` had specified
1880 ``related_name='entries'``, then each ``Author`` instance would have an
1881 ``entries`` attribute instead of ``entry_set``.
1882
1883 Another difference from one-to-many relationships is that in addition to model
1884 instances, the ``add()``, ``set()``, and ``remove()`` methods on many-to-many
1885 relationships accept primary key values. For example, if ``e1`` and ``e2`` are
1886 ``Entry`` instances, then these ``set()`` calls work identically::
1887
1888 a = Author.objects.get(id=5)
1889 a.entry_set.set([e1, e2])
1890 a.entry_set.set([e1.pk, e2.pk])
1891
1892 One-to-one relationships
1893 ------------------------
1894
1895 One-to-one relationships are very similar to many-to-one relationships. If you
1896 define a :class:`~django.db.models.OneToOneField` on your model, instances of
1897 that model will have access to the related object via an attribute of the
1898 model.
1899
1900 For example::
1901
1902 class EntryDetail(models.Model):
1903 entry = models.OneToOneField(Entry, on_delete=models.CASCADE)
1904 details = models.TextField()
1905
1906
1907 ed = EntryDetail.objects.get(id=2)
1908 ed.entry # Returns the related Entry object.
1909
1910 The difference comes in "reverse" queries. The related model in a one-to-one
1911 relationship also has access to a :class:`~django.db.models.Manager` object, but
1912 that :class:`~django.db.models.Manager` represents a single object, rather than
1913 a collection of objects::
1914
1915 e = Entry.objects.get(id=2)
1916 e.entrydetail # returns the related EntryDetail object
1917
1918 If no object has been assigned to this relationship, Django will raise
1919 a ``DoesNotExist`` exception.
1920
1921 Instances can be assigned to the reverse relationship in the same way as
1922 you would assign the forward relationship::
1923
1924 e.entrydetail = ed
1925
1926 How are the backward relationships possible?
1927 --------------------------------------------
1928
1929 Other object-relational mappers require you to define relationships on both
1930 sides. The Django developers believe this is a violation of the DRY (Don't
1931 Repeat Yourself) principle, so Django only requires you to define the
1932 relationship on one end.
1933
1934 But how is this possible, given that a model class doesn't know which other
1935 model classes are related to it until those other model classes are loaded?
1936
1937 The answer lies in the :data:`app registry <django.apps.apps>`. When Django
1938 starts, it imports each application listed in :setting:`INSTALLED_APPS`, and
1939 then the ``models`` module inside each application. Whenever a new model class
1940 is created, Django adds backward-relationships to any related models. If the
1941 related models haven't been imported yet, Django keeps tracks of the
1942 relationships and adds them when the related models eventually are imported.
1943
1944 For this reason, it's particularly important that all the models you're using
1945 be defined in applications listed in :setting:`INSTALLED_APPS`. Otherwise,
1946 backwards relations may not work properly.
1947
1948 Queries over related objects
1949 ----------------------------
1950
1951 Queries involving related objects follow the same rules as queries involving
1952 normal value fields. When specifying the value for a query to match, you may
1953 use either an object instance itself, or the primary key value for the object.
1954
1955 For example, if you have a Blog object ``b`` with ``id=5``, the following
1956 three queries would be identical::
1957
1958 Entry.objects.filter(blog=b) # Query using object instance
1959 Entry.objects.filter(blog=b.id) # Query using id from instance
1960 Entry.objects.filter(blog=5) # Query using id directly
1961
1962 Falling back to raw SQL
1963 =======================
1964
1965 If you find yourself needing to write an SQL query that is too complex for
1966 Django's database-mapper to handle, you can fall back on writing SQL by hand.
1967 Django has a couple of options for writing raw SQL queries; see
1968 :doc:`/topics/db/sql`.
1969
1970 Finally, it's important to note that the Django database layer is merely an
1971 interface to your database. You can access your database via other tools,
1972 programming languages or database frameworks; there's nothing Django-specific
1973 about your database.