queries.txt 63 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649
  1. ==============
  2. Making queries
  3. ==============
  4. .. currentmodule:: django.db.models
  5. Once you've created your :doc:`data models </topics/db/models>`, Django
  6. automatically gives you a database-abstraction API that lets you create,
  7. retrieve, update and delete objects. This document explains how to use this
  8. API. Refer to the :doc:`data model reference </ref/models/index>` for full
  9. details of all the various model lookup options.
  10. Throughout this guide (and in the reference), we'll refer to the following
  11. models, which comprise a blog application:
  12. .. _queryset-model-example:
  13. .. code-block:: python
  14. from datetime import date
  15. from django.db import models
  16. class Blog(models.Model):
  17. name = models.CharField(max_length=100)
  18. tagline = models.TextField()
  19. def __str__(self):
  20. return self.name
  21. class Author(models.Model):
  22. name = models.CharField(max_length=200)
  23. email = models.EmailField()
  24. def __str__(self):
  25. return self.name
  26. class Entry(models.Model):
  27. blog = models.ForeignKey(Blog, on_delete=models.CASCADE)
  28. headline = models.CharField(max_length=255)
  29. body_text = models.TextField()
  30. pub_date = models.DateField()
  31. mod_date = models.DateField(default=date.today)
  32. authors = models.ManyToManyField(Author)
  33. number_of_comments = models.IntegerField(default=0)
  34. number_of_pingbacks = models.IntegerField(default=0)
  35. rating = models.IntegerField(default=5)
  36. def __str__(self):
  37. return self.headline
  38. Creating objects
  39. ================
  40. To represent database-table data in Python objects, Django uses an intuitive
  41. system: A model class represents a database table, and an instance of that
  42. class represents a particular record in the database table.
  43. To create an object, instantiate it using keyword arguments to the model class,
  44. then call :meth:`~django.db.models.Model.save` to save it to the database.
  45. Assuming models live in a file ``mysite/blog/models.py``, here's an example::
  46. >>> from blog.models import Blog
  47. >>> b = Blog(name='Beatles Blog', tagline='All the latest Beatles news.')
  48. >>> b.save()
  49. This performs an ``INSERT`` SQL statement behind the scenes. Django doesn't hit
  50. the database until you explicitly call :meth:`~django.db.models.Model.save`.
  51. The :meth:`~django.db.models.Model.save` method has no return value.
  52. .. seealso::
  53. :meth:`~django.db.models.Model.save` takes a number of advanced options not
  54. described here. See the documentation for
  55. :meth:`~django.db.models.Model.save` for complete details.
  56. To create and save an object in a single step, use the
  57. :meth:`~django.db.models.query.QuerySet.create()` method.
  58. Saving changes to objects
  59. =========================
  60. To save changes to an object that's already in the database, use
  61. :meth:`~django.db.models.Model.save`.
  62. Given a ``Blog`` instance ``b5`` that has already been saved to the database,
  63. this example changes its name and updates its record in the database::
  64. >>> b5.name = 'New name'
  65. >>> b5.save()
  66. This performs an ``UPDATE`` SQL statement behind the scenes. Django doesn't hit
  67. the database until you explicitly call :meth:`~django.db.models.Model.save`.
  68. Saving ``ForeignKey`` and ``ManyToManyField`` fields
  69. ----------------------------------------------------
  70. Updating a :class:`~django.db.models.ForeignKey` field works exactly the same
  71. way as saving a normal field -- assign an object of the right type to the field
  72. in question. This example updates the ``blog`` attribute of an ``Entry``
  73. instance ``entry``, assuming appropriate instances of ``Entry`` and ``Blog``
  74. are already saved to the database (so we can retrieve them below)::
  75. >>> from blog.models import Blog, Entry
  76. >>> entry = Entry.objects.get(pk=1)
  77. >>> cheese_blog = Blog.objects.get(name="Cheddar Talk")
  78. >>> entry.blog = cheese_blog
  79. >>> entry.save()
  80. Updating a :class:`~django.db.models.ManyToManyField` works a little
  81. differently -- use the
  82. :meth:`~django.db.models.fields.related.RelatedManager.add` method on the field
  83. to add a record to the relation. This example adds the ``Author`` instance
  84. ``joe`` to the ``entry`` object::
  85. >>> from blog.models import Author
  86. >>> joe = Author.objects.create(name="Joe")
  87. >>> entry.authors.add(joe)
  88. To add multiple records to a :class:`~django.db.models.ManyToManyField` in one
  89. go, include multiple arguments in the call to
  90. :meth:`~django.db.models.fields.related.RelatedManager.add`, like this::
  91. >>> john = Author.objects.create(name="John")
  92. >>> paul = Author.objects.create(name="Paul")
  93. >>> george = Author.objects.create(name="George")
  94. >>> ringo = Author.objects.create(name="Ringo")
  95. >>> entry.authors.add(john, paul, george, ringo)
  96. Django will complain if you try to assign or add an object of the wrong type.
  97. .. _retrieving-objects:
  98. Retrieving objects
  99. ==================
  100. To retrieve objects from your database, construct a
  101. :class:`~django.db.models.query.QuerySet` via a
  102. :class:`~django.db.models.Manager` on your model class.
  103. A :class:`~django.db.models.query.QuerySet` represents a collection of objects
  104. from your database. It can have zero, one or many *filters*. Filters narrow
  105. down the query results based on the given parameters. In SQL terms, a
  106. :class:`~django.db.models.query.QuerySet` equates to a ``SELECT`` statement,
  107. and a filter is a limiting clause such as ``WHERE`` or ``LIMIT``.
  108. You get a :class:`~django.db.models.query.QuerySet` by using your model's
  109. :class:`~django.db.models.Manager`. Each model has at least one
  110. :class:`~django.db.models.Manager`, and it's called
  111. :attr:`~django.db.models.Model.objects` by default. Access it directly via the
  112. model class, like so::
  113. >>> Blog.objects
  114. <django.db.models.manager.Manager object at ...>
  115. >>> b = Blog(name='Foo', tagline='Bar')
  116. >>> b.objects
  117. Traceback:
  118. ...
  119. AttributeError: "Manager isn't accessible via Blog instances."
  120. .. note::
  121. ``Managers`` are accessible only via model classes, rather than from model
  122. instances, to enforce a separation between "table-level" operations and
  123. "record-level" operations.
  124. The :class:`~django.db.models.Manager` is the main source of ``QuerySets`` for
  125. a model. For example, ``Blog.objects.all()`` returns a
  126. :class:`~django.db.models.query.QuerySet` that contains all ``Blog`` objects in
  127. the database.
  128. Retrieving all objects
  129. ----------------------
  130. The simplest way to retrieve objects from a table is to get all of them. To do
  131. this, use the :meth:`~django.db.models.query.QuerySet.all` method on a
  132. :class:`~django.db.models.Manager`::
  133. >>> all_entries = Entry.objects.all()
  134. The :meth:`~django.db.models.query.QuerySet.all` method returns a
  135. :class:`~django.db.models.query.QuerySet` of all the objects in the database.
  136. Retrieving specific objects with filters
  137. ----------------------------------------
  138. The :class:`~django.db.models.query.QuerySet` returned by
  139. :meth:`~django.db.models.query.QuerySet.all` describes all objects in the
  140. database table. Usually, though, you'll need to select only a subset of the
  141. complete set of objects.
  142. To create such a subset, you refine the initial
  143. :class:`~django.db.models.query.QuerySet`, adding filter conditions. The two
  144. most common ways to refine a :class:`~django.db.models.query.QuerySet` are:
  145. ``filter(**kwargs)``
  146. Returns a new :class:`~django.db.models.query.QuerySet` containing objects
  147. that match the given lookup parameters.
  148. ``exclude(**kwargs)``
  149. Returns a new :class:`~django.db.models.query.QuerySet` containing objects
  150. that do *not* match the given lookup parameters.
  151. The lookup parameters (``**kwargs`` in the above function definitions) should
  152. be in the format described in `Field lookups`_ below.
  153. For example, to get a :class:`~django.db.models.query.QuerySet` of blog entries
  154. from the year 2006, use :meth:`~django.db.models.query.QuerySet.filter` like
  155. so::
  156. Entry.objects.filter(pub_date__year=2006)
  157. With the default manager class, it is the same as::
  158. Entry.objects.all().filter(pub_date__year=2006)
  159. .. _chaining-filters:
  160. Chaining filters
  161. ~~~~~~~~~~~~~~~~
  162. The result of refining a :class:`~django.db.models.query.QuerySet` is itself a
  163. :class:`~django.db.models.query.QuerySet`, so it's possible to chain
  164. refinements together. For example::
  165. >>> Entry.objects.filter(
  166. ... headline__startswith='What'
  167. ... ).exclude(
  168. ... pub_date__gte=datetime.date.today()
  169. ... ).filter(
  170. ... pub_date__gte=datetime.date(2005, 1, 30)
  171. ... )
  172. This takes the initial :class:`~django.db.models.query.QuerySet` of all entries
  173. in the database, adds a filter, then an exclusion, then another filter. The
  174. final result is a :class:`~django.db.models.query.QuerySet` containing all
  175. entries with a headline that starts with "What", that were published between
  176. January 30, 2005, and the current day.
  177. .. _filtered-querysets-are-unique:
  178. Filtered ``QuerySet``\s are unique
  179. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  180. Each time you refine a :class:`~django.db.models.query.QuerySet`, you get a
  181. brand-new :class:`~django.db.models.query.QuerySet` that is in no way bound to
  182. the previous :class:`~django.db.models.query.QuerySet`. Each refinement creates
  183. a separate and distinct :class:`~django.db.models.query.QuerySet` that can be
  184. stored, used and reused.
  185. Example::
  186. >>> q1 = Entry.objects.filter(headline__startswith="What")
  187. >>> q2 = q1.exclude(pub_date__gte=datetime.date.today())
  188. >>> q3 = q1.filter(pub_date__gte=datetime.date.today())
  189. These three ``QuerySets`` are separate. The first is a base
  190. :class:`~django.db.models.query.QuerySet` containing all entries that contain a
  191. headline starting with "What". The second is a subset of the first, with an
  192. additional criteria that excludes records whose ``pub_date`` is today or in the
  193. future. The third is a subset of the first, with an additional criteria that
  194. selects only the records whose ``pub_date`` is today or in the future. The
  195. initial :class:`~django.db.models.query.QuerySet` (``q1``) is unaffected by the
  196. refinement process.
  197. .. _querysets-are-lazy:
  198. ``QuerySet``\s are lazy
  199. ~~~~~~~~~~~~~~~~~~~~~~~
  200. ``QuerySets`` are lazy -- the act of creating a
  201. :class:`~django.db.models.query.QuerySet` doesn't involve any database
  202. activity. You can stack filters together all day long, and Django won't
  203. actually run the query until the :class:`~django.db.models.query.QuerySet` is
  204. *evaluated*. Take a look at this example::
  205. >>> q = Entry.objects.filter(headline__startswith="What")
  206. >>> q = q.filter(pub_date__lte=datetime.date.today())
  207. >>> q = q.exclude(body_text__icontains="food")
  208. >>> print(q)
  209. Though this looks like three database hits, in fact it hits the database only
  210. once, at the last line (``print(q)``). In general, the results of a
  211. :class:`~django.db.models.query.QuerySet` aren't fetched from the database
  212. until you "ask" for them. When you do, the
  213. :class:`~django.db.models.query.QuerySet` is *evaluated* by accessing the
  214. database. For more details on exactly when evaluation takes place, see
  215. :ref:`when-querysets-are-evaluated`.
  216. .. _retrieving-single-object-with-get:
  217. Retrieving a single object with ``get()``
  218. -----------------------------------------
  219. :meth:`~django.db.models.query.QuerySet.filter` will always give you a
  220. :class:`~django.db.models.query.QuerySet`, even if only a single object matches
  221. the query - in this case, it will be a
  222. :class:`~django.db.models.query.QuerySet` containing a single element.
  223. If you know there is only one object that matches your query, you can use the
  224. :meth:`~django.db.models.query.QuerySet.get` method on a
  225. :class:`~django.db.models.Manager` which returns the object directly::
  226. >>> one_entry = Entry.objects.get(pk=1)
  227. You can use any query expression with
  228. :meth:`~django.db.models.query.QuerySet.get`, just like with
  229. :meth:`~django.db.models.query.QuerySet.filter` - again, see `Field lookups`_
  230. below.
  231. Note that there is a difference between using
  232. :meth:`~django.db.models.query.QuerySet.get`, and using
  233. :meth:`~django.db.models.query.QuerySet.filter` with a slice of ``[0]``. If
  234. there are no results that match the query,
  235. :meth:`~django.db.models.query.QuerySet.get` will raise a ``DoesNotExist``
  236. exception. This exception is an attribute of the model class that the query is
  237. being performed on - so in the code above, if there is no ``Entry`` object with
  238. a primary key of 1, Django will raise ``Entry.DoesNotExist``.
  239. Similarly, Django will complain if more than one item matches the
  240. :meth:`~django.db.models.query.QuerySet.get` query. In this case, it will raise
  241. :exc:`~django.core.exceptions.MultipleObjectsReturned`, which again is an
  242. attribute of the model class itself.
  243. Other ``QuerySet`` methods
  244. --------------------------
  245. Most of the time you'll use :meth:`~django.db.models.query.QuerySet.all`,
  246. :meth:`~django.db.models.query.QuerySet.get`,
  247. :meth:`~django.db.models.query.QuerySet.filter` and
  248. :meth:`~django.db.models.query.QuerySet.exclude` when you need to look up
  249. objects from the database. However, that's far from all there is; see the
  250. :ref:`QuerySet API Reference <queryset-api>` for a complete list of all the
  251. various :class:`~django.db.models.query.QuerySet` methods.
  252. .. _limiting-querysets:
  253. Limiting ``QuerySet``\s
  254. -----------------------
  255. Use a subset of Python's array-slicing syntax to limit your
  256. :class:`~django.db.models.query.QuerySet` to a certain number of results. This
  257. is the equivalent of SQL's ``LIMIT`` and ``OFFSET`` clauses.
  258. For example, this returns the first 5 objects (``LIMIT 5``)::
  259. >>> Entry.objects.all()[:5]
  260. This returns the sixth through tenth objects (``OFFSET 5 LIMIT 5``)::
  261. >>> Entry.objects.all()[5:10]
  262. Negative indexing (i.e. ``Entry.objects.all()[-1]``) is not supported.
  263. Generally, slicing a :class:`~django.db.models.query.QuerySet` returns a new
  264. :class:`~django.db.models.query.QuerySet` -- it doesn't evaluate the query. An
  265. exception is if you use the "step" parameter of Python slice syntax. For
  266. example, this would actually execute the query in order to return a list of
  267. every *second* object of the first 10::
  268. >>> Entry.objects.all()[:10:2]
  269. Further filtering or ordering of a sliced queryset is prohibited due to the
  270. ambiguous nature of how that might work.
  271. To retrieve a *single* object rather than a list
  272. (e.g. ``SELECT foo FROM bar LIMIT 1``), use an index instead of a slice. For
  273. example, this returns the first ``Entry`` in the database, after ordering
  274. entries alphabetically by headline::
  275. >>> Entry.objects.order_by('headline')[0]
  276. This is roughly equivalent to::
  277. >>> Entry.objects.order_by('headline')[0:1].get()
  278. Note, however, that the first of these will raise ``IndexError`` while the
  279. second will raise ``DoesNotExist`` if no objects match the given criteria. See
  280. :meth:`~django.db.models.query.QuerySet.get` for more details.
  281. .. _field-lookups-intro:
  282. Field lookups
  283. -------------
  284. Field lookups are how you specify the meat of an SQL ``WHERE`` clause. They're
  285. specified as keyword arguments to the :class:`~django.db.models.query.QuerySet`
  286. methods :meth:`~django.db.models.query.QuerySet.filter`,
  287. :meth:`~django.db.models.query.QuerySet.exclude` and
  288. :meth:`~django.db.models.query.QuerySet.get`.
  289. Basic lookups keyword arguments take the form ``field__lookuptype=value``.
  290. (That's a double-underscore). For example::
  291. >>> Entry.objects.filter(pub_date__lte='2006-01-01')
  292. translates (roughly) into the following SQL:
  293. .. code-block:: sql
  294. SELECT * FROM blog_entry WHERE pub_date <= '2006-01-01';
  295. .. admonition:: How this is possible
  296. Python has the ability to define functions that accept arbitrary name-value
  297. arguments whose names and values are evaluated at runtime. For more
  298. information, see :ref:`tut-keywordargs` in the official Python tutorial.
  299. The field specified in a lookup has to be the name of a model field. There's
  300. one exception though, in case of a :class:`~django.db.models.ForeignKey` you
  301. can specify the field name suffixed with ``_id``. In this case, the value
  302. parameter is expected to contain the raw value of the foreign model's primary
  303. key. For example:
  304. >>> Entry.objects.filter(blog_id=4)
  305. If you pass an invalid keyword argument, a lookup function will raise
  306. ``TypeError``.
  307. The database API supports about two dozen lookup types; a complete reference
  308. can be found in the :ref:`field lookup reference <field-lookups>`. To give you
  309. a taste of what's available, here's some of the more common lookups you'll
  310. probably use:
  311. :lookup:`exact`
  312. An "exact" match. For example::
  313. >>> Entry.objects.get(headline__exact="Cat bites dog")
  314. Would generate SQL along these lines:
  315. .. code-block:: sql
  316. SELECT ... WHERE headline = 'Cat bites dog';
  317. If you don't provide a lookup type -- that is, if your keyword argument
  318. doesn't contain a double underscore -- the lookup type is assumed to be
  319. ``exact``.
  320. For example, the following two statements are equivalent::
  321. >>> Blog.objects.get(id__exact=14) # Explicit form
  322. >>> Blog.objects.get(id=14) # __exact is implied
  323. This is for convenience, because ``exact`` lookups are the common case.
  324. :lookup:`iexact`
  325. A case-insensitive match. So, the query::
  326. >>> Blog.objects.get(name__iexact="beatles blog")
  327. Would match a ``Blog`` titled ``"Beatles Blog"``, ``"beatles blog"``, or
  328. even ``"BeAtlES blOG"``.
  329. :lookup:`contains`
  330. Case-sensitive containment test. For example::
  331. Entry.objects.get(headline__contains='Lennon')
  332. Roughly translates to this SQL:
  333. .. code-block:: sql
  334. SELECT ... WHERE headline LIKE '%Lennon%';
  335. Note this will match the headline ``'Today Lennon honored'`` but not
  336. ``'today lennon honored'``.
  337. There's also a case-insensitive version, :lookup:`icontains`.
  338. :lookup:`startswith`, :lookup:`endswith`
  339. Starts-with and ends-with search, respectively. There are also
  340. case-insensitive versions called :lookup:`istartswith` and
  341. :lookup:`iendswith`.
  342. Again, this only scratches the surface. A complete reference can be found in the
  343. :ref:`field lookup reference <field-lookups>`.
  344. .. _lookups-that-span-relationships:
  345. Lookups that span relationships
  346. -------------------------------
  347. Django offers a powerful and intuitive way to "follow" relationships in
  348. lookups, taking care of the SQL ``JOIN``\s for you automatically, behind the
  349. scenes. To span a relationship, use the field name of related fields
  350. across models, separated by double underscores, until you get to the field you
  351. want.
  352. This example retrieves all ``Entry`` objects with a ``Blog`` whose ``name``
  353. is ``'Beatles Blog'``::
  354. >>> Entry.objects.filter(blog__name='Beatles Blog')
  355. This spanning can be as deep as you'd like.
  356. It works backwards, too. While it :attr:`can be customized
  357. <.ForeignKey.related_query_name>`, by default you refer to a "reverse"
  358. relationship in a lookup using the lowercase name of the model.
  359. This example retrieves all ``Blog`` objects which have at least one ``Entry``
  360. whose ``headline`` contains ``'Lennon'``::
  361. >>> Blog.objects.filter(entry__headline__contains='Lennon')
  362. If you are filtering across multiple relationships and one of the intermediate
  363. models doesn't have a value that meets the filter condition, Django will treat
  364. it as if there is an empty (all values are ``NULL``), but valid, object there.
  365. All this means is that no error will be raised. For example, in this filter::
  366. Blog.objects.filter(entry__authors__name='Lennon')
  367. (if there was a related ``Author`` model), if there was no ``author``
  368. associated with an entry, it would be treated as if there was also no ``name``
  369. attached, rather than raising an error because of the missing ``author``.
  370. Usually this is exactly what you want to have happen. The only case where it
  371. might be confusing is if you are using :lookup:`isnull`. Thus::
  372. Blog.objects.filter(entry__authors__name__isnull=True)
  373. will return ``Blog`` objects that have an empty ``name`` on the ``author`` and
  374. also those which have an empty ``author`` on the ``entry``. If you don't want
  375. those latter objects, you could write::
  376. Blog.objects.filter(entry__authors__isnull=False, entry__authors__name__isnull=True)
  377. .. _spanning-multi-valued-relationships:
  378. Spanning multi-valued relationships
  379. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  380. When you are filtering an object based on a
  381. :class:`~django.db.models.ManyToManyField` or a reverse
  382. :class:`~django.db.models.ForeignKey`, there are two different sorts of filter
  383. you may be interested in. Consider the ``Blog``/``Entry`` relationship
  384. (``Blog`` to ``Entry`` is a one-to-many relation). We might be interested in
  385. finding blogs that have an entry which has both *"Lennon"* in the headline and
  386. was published in 2008. Or we might want to find blogs that have an entry with
  387. *"Lennon"* in the headline as well as an entry that was published
  388. in 2008. Since there are multiple entries associated with a single ``Blog``,
  389. both of these queries are possible and make sense in some situations.
  390. The same type of situation arises with a
  391. :class:`~django.db.models.ManyToManyField`. For example, if an ``Entry`` has a
  392. :class:`~django.db.models.ManyToManyField` called ``tags``, we might want to
  393. find entries linked to tags called *"music"* and *"bands"* or we might want an
  394. entry that contains a tag with a name of *"music"* and a status of *"public"*.
  395. To handle both of these situations, Django has a consistent way of processing
  396. :meth:`~django.db.models.query.QuerySet.filter` calls. Everything inside a
  397. single :meth:`~django.db.models.query.QuerySet.filter` call is applied
  398. simultaneously to filter out items matching all those requirements. Successive
  399. :meth:`~django.db.models.query.QuerySet.filter` calls further restrict the set
  400. of objects, but for multi-valued relations, they apply to any object linked to
  401. the primary model, not necessarily those objects that were selected by an
  402. earlier :meth:`~django.db.models.query.QuerySet.filter` call.
  403. That may sound a bit confusing, so hopefully an example will clarify. To
  404. select all blogs that contain entries with both *"Lennon"* in the headline
  405. and that were published in 2008 (the same entry satisfying both conditions),
  406. we would write::
  407. Blog.objects.filter(entry__headline__contains='Lennon', entry__pub_date__year=2008)
  408. To select all blogs that contain an entry with *"Lennon"* in the headline
  409. **as well as** an entry that was published in 2008, we would write::
  410. Blog.objects.filter(entry__headline__contains='Lennon').filter(entry__pub_date__year=2008)
  411. Suppose there is only one blog that had both entries containing *"Lennon"* and
  412. entries from 2008, but that none of the entries from 2008 contained *"Lennon"*.
  413. The first query would not return any blogs, but the second query would return
  414. that one blog.
  415. In the second example, the first filter restricts the queryset to all those
  416. blogs linked to entries with *"Lennon"* in the headline. The second filter
  417. restricts the set of blogs *further* to those that are also linked to entries
  418. that were published in 2008. The entries selected by the second filter may or
  419. may not be the same as the entries in the first filter. We are filtering the
  420. ``Blog`` items with each filter statement, not the ``Entry`` items.
  421. .. note::
  422. The behavior of :meth:`~django.db.models.query.QuerySet.filter` for queries
  423. that span multi-value relationships, as described above, is not implemented
  424. equivalently for :meth:`~django.db.models.query.QuerySet.exclude`. Instead,
  425. the conditions in a single :meth:`~django.db.models.query.QuerySet.exclude`
  426. call will not necessarily refer to the same item.
  427. For example, the following query would exclude blogs that contain *both*
  428. entries with *"Lennon"* in the headline *and* entries published in 2008::
  429. Blog.objects.exclude(
  430. entry__headline__contains='Lennon',
  431. entry__pub_date__year=2008,
  432. )
  433. However, unlike the behavior when using
  434. :meth:`~django.db.models.query.QuerySet.filter`, this will not limit blogs
  435. based on entries that satisfy both conditions. In order to do that, i.e.
  436. to select all blogs that do not contain entries published with *"Lennon"*
  437. that were published in 2008, you need to make two queries::
  438. Blog.objects.exclude(
  439. entry__in=Entry.objects.filter(
  440. headline__contains='Lennon',
  441. pub_date__year=2008,
  442. ),
  443. )
  444. .. _using-f-expressions-in-filters:
  445. Filters can reference fields on the model
  446. -----------------------------------------
  447. In the examples given so far, we have constructed filters that compare
  448. the value of a model field with a constant. But what if you want to compare
  449. the value of a model field with another field on the same model?
  450. Django provides :class:`F expressions <django.db.models.F>` to allow such
  451. comparisons. Instances of ``F()`` act as a reference to a model field within a
  452. query. These references can then be used in query filters to compare the values
  453. of two different fields on the same model instance.
  454. For example, to find a list of all blog entries that have had more comments
  455. than pingbacks, we construct an ``F()`` object to reference the pingback count,
  456. and use that ``F()`` object in the query::
  457. >>> from django.db.models import F
  458. >>> Entry.objects.filter(number_of_comments__gt=F('number_of_pingbacks'))
  459. Django supports the use of addition, subtraction, multiplication,
  460. division, modulo, and power arithmetic with ``F()`` objects, both with constants
  461. and with other ``F()`` objects. To find all the blog entries with more than
  462. *twice* as many comments as pingbacks, we modify the query::
  463. >>> Entry.objects.filter(number_of_comments__gt=F('number_of_pingbacks') * 2)
  464. To find all the entries where the rating of the entry is less than the
  465. sum of the pingback count and comment count, we would issue the
  466. query::
  467. >>> Entry.objects.filter(rating__lt=F('number_of_comments') + F('number_of_pingbacks'))
  468. You can also use the double underscore notation to span relationships in
  469. an ``F()`` object. An ``F()`` object with a double underscore will introduce
  470. any joins needed to access the related object. For example, to retrieve all
  471. the entries where the author's name is the same as the blog name, we could
  472. issue the query::
  473. >>> Entry.objects.filter(authors__name=F('blog__name'))
  474. For date and date/time fields, you can add or subtract a
  475. :class:`~datetime.timedelta` object. The following would return all entries
  476. that were modified more than 3 days after they were published::
  477. >>> from datetime import timedelta
  478. >>> Entry.objects.filter(mod_date__gt=F('pub_date') + timedelta(days=3))
  479. The ``F()`` objects support bitwise operations by ``.bitand()``, ``.bitor()``,
  480. ``.bitxor()``, ``.bitrightshift()``, and ``.bitleftshift()``. For example::
  481. >>> F('somefield').bitand(16)
  482. .. admonition:: Oracle
  483. Oracle doesn't support bitwise XOR operation.
  484. .. _using-transforms-in-expressions:
  485. Expressions can reference transforms
  486. ------------------------------------
  487. Django supports using transforms in expressions.
  488. For example, to find all ``Entry`` objects published in the same year as they
  489. were last modified::
  490. >>> Entry.objects.filter(pub_date__year=F('mod_date__year'))
  491. To find the earliest year an entry was published, we can issue the query::
  492. >>> Entry.objects.aggregate(first_published_year=Min('pub_date__year'))
  493. This example finds the value of the highest rated entry and the total number
  494. of comments on all entries for each year::
  495. >>> Entry.objects.values('pub_date__year').annotate(
  496. ... top_rating=Subquery(
  497. ... Entry.objects.filter(
  498. ... pub_date__year=OuterRef('pub_date__year'),
  499. ... ).order_by('-rating').values('rating')[:1]
  500. ... ),
  501. ... total_comments=Sum('number_of_comments'),
  502. ... )
  503. The ``pk`` lookup shortcut
  504. --------------------------
  505. For convenience, Django provides a ``pk`` lookup shortcut, which stands for
  506. "primary key".
  507. In the example ``Blog`` model, the primary key is the ``id`` field, so these
  508. three statements are equivalent::
  509. >>> Blog.objects.get(id__exact=14) # Explicit form
  510. >>> Blog.objects.get(id=14) # __exact is implied
  511. >>> Blog.objects.get(pk=14) # pk implies id__exact
  512. The use of ``pk`` isn't limited to ``__exact`` queries -- any query term
  513. can be combined with ``pk`` to perform a query on the primary key of a model::
  514. # Get blogs entries with id 1, 4 and 7
  515. >>> Blog.objects.filter(pk__in=[1,4,7])
  516. # Get all blog entries with id > 14
  517. >>> Blog.objects.filter(pk__gt=14)
  518. ``pk`` lookups also work across joins. For example, these three statements are
  519. equivalent::
  520. >>> Entry.objects.filter(blog__id__exact=3) # Explicit form
  521. >>> Entry.objects.filter(blog__id=3) # __exact is implied
  522. >>> Entry.objects.filter(blog__pk=3) # __pk implies __id__exact
  523. Escaping percent signs and underscores in ``LIKE`` statements
  524. -------------------------------------------------------------
  525. The field lookups that equate to ``LIKE`` SQL statements (``iexact``,
  526. ``contains``, ``icontains``, ``startswith``, ``istartswith``, ``endswith``
  527. and ``iendswith``) will automatically escape the two special characters used in
  528. ``LIKE`` statements -- the percent sign and the underscore. (In a ``LIKE``
  529. statement, the percent sign signifies a multiple-character wildcard and the
  530. underscore signifies a single-character wildcard.)
  531. This means things should work intuitively, so the abstraction doesn't leak.
  532. For example, to retrieve all the entries that contain a percent sign, use the
  533. percent sign as any other character::
  534. >>> Entry.objects.filter(headline__contains='%')
  535. Django takes care of the quoting for you; the resulting SQL will look something
  536. like this:
  537. .. code-block:: sql
  538. SELECT ... WHERE headline LIKE '%\%%';
  539. Same goes for underscores. Both percentage signs and underscores are handled
  540. for you transparently.
  541. .. _caching-and-querysets:
  542. Caching and ``QuerySet``\s
  543. --------------------------
  544. Each :class:`~django.db.models.query.QuerySet` contains a cache to minimize
  545. database access. Understanding how it works will allow you to write the most
  546. efficient code.
  547. In a newly created :class:`~django.db.models.query.QuerySet`, the cache is
  548. empty. The first time a :class:`~django.db.models.query.QuerySet` is evaluated
  549. -- and, hence, a database query happens -- Django saves the query results in
  550. the :class:`~django.db.models.query.QuerySet`’s cache and returns the results
  551. that have been explicitly requested (e.g., the next element, if the
  552. :class:`~django.db.models.query.QuerySet` is being iterated over). Subsequent
  553. evaluations of the :class:`~django.db.models.query.QuerySet` reuse the cached
  554. results.
  555. Keep this caching behavior in mind, because it may bite you if you don't use
  556. your :class:`~django.db.models.query.QuerySet`\s correctly. For example, the
  557. following will create two :class:`~django.db.models.query.QuerySet`\s, evaluate
  558. them, and throw them away::
  559. >>> print([e.headline for e in Entry.objects.all()])
  560. >>> print([e.pub_date for e in Entry.objects.all()])
  561. That means the same database query will be executed twice, effectively doubling
  562. your database load. Also, there's a possibility the two lists may not include
  563. the same database records, because an ``Entry`` may have been added or deleted
  564. in the split second between the two requests.
  565. To avoid this problem, save the :class:`~django.db.models.query.QuerySet` and
  566. reuse it::
  567. >>> queryset = Entry.objects.all()
  568. >>> print([p.headline for p in queryset]) # Evaluate the query set.
  569. >>> print([p.pub_date for p in queryset]) # Re-use the cache from the evaluation.
  570. When ``QuerySet``\s are not cached
  571. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  572. Querysets do not always cache their results. When evaluating only *part* of
  573. the queryset, the cache is checked, but if it is not populated then the items
  574. returned by the subsequent query are not cached. Specifically, this means that
  575. :ref:`limiting the queryset <limiting-querysets>` using an array slice or an
  576. index will not populate the cache.
  577. For example, repeatedly getting a certain index in a queryset object will query
  578. the database each time::
  579. >>> queryset = Entry.objects.all()
  580. >>> print(queryset[5]) # Queries the database
  581. >>> print(queryset[5]) # Queries the database again
  582. However, if the entire queryset has already been evaluated, the cache will be
  583. checked instead::
  584. >>> queryset = Entry.objects.all()
  585. >>> [entry for entry in queryset] # Queries the database
  586. >>> print(queryset[5]) # Uses cache
  587. >>> print(queryset[5]) # Uses cache
  588. Here are some examples of other actions that will result in the entire queryset
  589. being evaluated and therefore populate the cache::
  590. >>> [entry for entry in queryset]
  591. >>> bool(queryset)
  592. >>> entry in queryset
  593. >>> list(queryset)
  594. .. note::
  595. Simply printing the queryset will not populate the cache. This is because
  596. the call to ``__repr__()`` only returns a slice of the entire queryset.
  597. .. _querying-jsonfield:
  598. Querying ``JSONField``
  599. ======================
  600. Lookups implementation is different in :class:`~django.db.models.JSONField`,
  601. mainly due to the existence of key transformations. To demonstrate, we will use
  602. the following example model::
  603. from django.db import models
  604. class Dog(models.Model):
  605. name = models.CharField(max_length=200)
  606. data = models.JSONField(null=True)
  607. def __str__(self):
  608. return self.name
  609. Storing and querying for ``None``
  610. ---------------------------------
  611. As with other fields, storing ``None`` as the field's value will store it as
  612. SQL ``NULL``. While not recommended, it is possible to store JSON scalar
  613. ``null`` instead of SQL ``NULL`` by using :class:`Value('null')
  614. <django.db.models.Value>`.
  615. Whichever of the values is stored, when retrieved from the database, the Python
  616. representation of the JSON scalar ``null`` is the same as SQL ``NULL``, i.e.
  617. ``None``. Therefore, it can be hard to distinguish between them.
  618. This only applies to ``None`` as the top-level value of the field. If ``None``
  619. is inside a :py:class:`list` or :py:class:`dict`, it will always be interpreted
  620. as JSON ``null``.
  621. When querying, ``None`` value will always be interpreted as JSON ``null``. To
  622. query for SQL ``NULL``, use :lookup:`isnull`::
  623. >>> Dog.objects.create(name='Max', data=None) # SQL NULL.
  624. <Dog: Max>
  625. >>> Dog.objects.create(name='Archie', data=Value('null')) # JSON null.
  626. <Dog: Archie>
  627. >>> Dog.objects.filter(data=None)
  628. <QuerySet [<Dog: Archie>]>
  629. >>> Dog.objects.filter(data=Value('null'))
  630. <QuerySet [<Dog: Archie>]>
  631. >>> Dog.objects.filter(data__isnull=True)
  632. <QuerySet [<Dog: Max>]>
  633. >>> Dog.objects.filter(data__isnull=False)
  634. <QuerySet [<Dog: Archie>]>
  635. Unless you are sure you wish to work with SQL ``NULL`` values, consider setting
  636. ``null=False`` and providing a suitable default for empty values, such as
  637. ``default=dict``.
  638. .. note::
  639. Storing JSON scalar ``null`` does not violate :attr:`null=False
  640. <django.db.models.Field.null>`.
  641. .. fieldlookup:: jsonfield.key
  642. Key, index, and path transforms
  643. -------------------------------
  644. To query based on a given dictionary key, use that key as the lookup name::
  645. >>> Dog.objects.create(name='Rufus', data={
  646. ... 'breed': 'labrador',
  647. ... 'owner': {
  648. ... 'name': 'Bob',
  649. ... 'other_pets': [{
  650. ... 'name': 'Fishy',
  651. ... }],
  652. ... },
  653. ... })
  654. <Dog: Rufus>
  655. >>> Dog.objects.create(name='Meg', data={'breed': 'collie', 'owner': None})
  656. <Dog: Meg>
  657. >>> Dog.objects.filter(data__breed='collie')
  658. <QuerySet [<Dog: Meg>]>
  659. Multiple keys can be chained together to form a path lookup::
  660. >>> Dog.objects.filter(data__owner__name='Bob')
  661. <QuerySet [<Dog: Rufus>]>
  662. If the key is an integer, it will be interpreted as an index transform in an
  663. array::
  664. >>> Dog.objects.filter(data__owner__other_pets__0__name='Fishy')
  665. <QuerySet [<Dog: Rufus>]>
  666. If the key you wish to query by clashes with the name of another lookup, use
  667. the :lookup:`contains <jsonfield.contains>` lookup instead.
  668. To query for missing keys, use the ``isnull`` lookup::
  669. >>> Dog.objects.create(name='Shep', data={'breed': 'collie'})
  670. <Dog: Shep>
  671. >>> Dog.objects.filter(data__owner__isnull=True)
  672. <QuerySet [<Dog: Shep>]>
  673. .. note::
  674. The lookup examples given above implicitly use the :lookup:`exact` lookup.
  675. Key, index, and path transforms can also be chained with:
  676. :lookup:`icontains`, :lookup:`endswith`, :lookup:`iendswith`,
  677. :lookup:`iexact`, :lookup:`regex`, :lookup:`iregex`, :lookup:`startswith`,
  678. :lookup:`istartswith`, :lookup:`lt`, :lookup:`lte`, :lookup:`gt`, and
  679. :lookup:`gte`, as well as with :ref:`containment-and-key-lookups`.
  680. .. note::
  681. Due to the way in which key-path queries work,
  682. :meth:`~django.db.models.query.QuerySet.exclude` and
  683. :meth:`~django.db.models.query.QuerySet.filter` are not guaranteed to
  684. produce exhaustive sets. If you want to include objects that do not have
  685. the path, add the ``isnull`` lookup.
  686. .. warning::
  687. Since any string could be a key in a JSON object, any lookup other than
  688. those listed below will be interpreted as a key lookup. No errors are
  689. raised. Be extra careful for typing mistakes, and always check your queries
  690. work as you intend.
  691. .. admonition:: MariaDB and Oracle users
  692. Using :meth:`~django.db.models.query.QuerySet.order_by` on key, index, or
  693. path transforms will sort the objects using the string representation of
  694. the values. This is because MariaDB and Oracle Database do not provide a
  695. function that converts JSON values into their equivalent SQL values.
  696. .. admonition:: Oracle users
  697. On Oracle Database, using ``None`` as the lookup value in an
  698. :meth:`~django.db.models.query.QuerySet.exclude` query will return objects
  699. that do not have ``null`` as the value at the given path, including objects
  700. that do not have the path. On other database backends, the query will
  701. return objects that have the path and the value is not ``null``.
  702. .. admonition:: PostgreSQL users
  703. On PostgreSQL, if only one key or index is used, the SQL operator ``->`` is
  704. used. If multiple operators are used then the ``#>`` operator is used.
  705. .. _containment-and-key-lookups:
  706. Containment and key lookups
  707. ---------------------------
  708. .. fieldlookup:: jsonfield.contains
  709. ``contains``
  710. ~~~~~~~~~~~~
  711. The :lookup:`contains` lookup is overridden on ``JSONField``. The returned
  712. objects are those where the given ``dict`` of key-value pairs are all
  713. contained in the top-level of the field. For example::
  714. >>> Dog.objects.create(name='Rufus', data={'breed': 'labrador', 'owner': 'Bob'})
  715. <Dog: Rufus>
  716. >>> Dog.objects.create(name='Meg', data={'breed': 'collie', 'owner': 'Bob'})
  717. <Dog: Meg>
  718. >>> Dog.objects.create(name='Fred', data={})
  719. <Dog: Fred>
  720. >>> Dog.objects.filter(data__contains={'owner': 'Bob'})
  721. <QuerySet [<Dog: Rufus>, <Dog: Meg>]>
  722. >>> Dog.objects.filter(data__contains={'breed': 'collie'})
  723. <QuerySet [<Dog: Meg>]>
  724. .. admonition:: Oracle and SQLite
  725. ``contains`` is not supported on Oracle and SQLite.
  726. .. fieldlookup:: jsonfield.contained_by
  727. ``contained_by``
  728. ~~~~~~~~~~~~~~~~
  729. This is the inverse of the :lookup:`contains <jsonfield.contains>` lookup - the
  730. objects returned will be those where the key-value pairs on the object are a
  731. subset of those in the value passed. For example::
  732. >>> Dog.objects.create(name='Rufus', data={'breed': 'labrador', 'owner': 'Bob'})
  733. <Dog: Rufus>
  734. >>> Dog.objects.create(name='Meg', data={'breed': 'collie', 'owner': 'Bob'})
  735. <Dog: Meg>
  736. >>> Dog.objects.create(name='Fred', data={})
  737. <Dog: Fred>
  738. >>> Dog.objects.filter(data__contained_by={'breed': 'collie', 'owner': 'Bob'})
  739. <QuerySet [<Dog: Meg>, <Dog: Fred>]>
  740. >>> Dog.objects.filter(data__contained_by={'breed': 'collie'})
  741. <QuerySet [<Dog: Fred>]>
  742. .. admonition:: Oracle and SQLite
  743. ``contained_by`` is not supported on Oracle and SQLite.
  744. .. fieldlookup:: jsonfield.has_key
  745. ``has_key``
  746. ~~~~~~~~~~~
  747. Returns objects where the given key is in the top-level of the data. For
  748. example::
  749. >>> Dog.objects.create(name='Rufus', data={'breed': 'labrador'})
  750. <Dog: Rufus>
  751. >>> Dog.objects.create(name='Meg', data={'breed': 'collie', 'owner': 'Bob'})
  752. <Dog: Meg>
  753. >>> Dog.objects.filter(data__has_key='owner')
  754. <QuerySet [<Dog: Meg>]>
  755. .. fieldlookup:: jsonfield.has_any_keys
  756. ``has_keys``
  757. ~~~~~~~~~~~~
  758. Returns objects where all of the given keys are in the top-level of the data.
  759. For example::
  760. >>> Dog.objects.create(name='Rufus', data={'breed': 'labrador'})
  761. <Dog: Rufus>
  762. >>> Dog.objects.create(name='Meg', data={'breed': 'collie', 'owner': 'Bob'})
  763. <Dog: Meg>
  764. >>> Dog.objects.filter(data__has_keys=['breed', 'owner'])
  765. <QuerySet [<Dog: Meg>]>
  766. .. fieldlookup:: jsonfield.has_keys
  767. ``has_any_keys``
  768. ~~~~~~~~~~~~~~~~
  769. Returns objects where any of the given keys are in the top-level of the data.
  770. For example::
  771. >>> Dog.objects.create(name='Rufus', data={'breed': 'labrador'})
  772. <Dog: Rufus>
  773. >>> Dog.objects.create(name='Meg', data={'owner': 'Bob'})
  774. <Dog: Meg>
  775. >>> Dog.objects.filter(data__has_any_keys=['owner', 'breed'])
  776. <QuerySet [<Dog: Rufus>, <Dog: Meg>]>
  777. .. _complex-lookups-with-q:
  778. Complex lookups with ``Q`` objects
  779. ==================================
  780. Keyword argument queries -- in :meth:`~django.db.models.query.QuerySet.filter`,
  781. etc. -- are "AND"ed together. If you need to execute more complex queries (for
  782. example, queries with ``OR`` statements), you can use :class:`Q objects <django.db.models.Q>`.
  783. A :class:`Q object <django.db.models.Q>` (``django.db.models.Q``) is an object
  784. used to encapsulate a collection of keyword arguments. These keyword arguments
  785. are specified as in "Field lookups" above.
  786. For example, this ``Q`` object encapsulates a single ``LIKE`` query::
  787. from django.db.models import Q
  788. Q(question__startswith='What')
  789. ``Q`` objects can be combined using the ``&`` and ``|`` operators. When an
  790. operator is used on two ``Q`` objects, it yields a new ``Q`` object.
  791. For example, this statement yields a single ``Q`` object that represents the
  792. "OR" of two ``"question__startswith"`` queries::
  793. Q(question__startswith='Who') | Q(question__startswith='What')
  794. This is equivalent to the following SQL ``WHERE`` clause::
  795. WHERE question LIKE 'Who%' OR question LIKE 'What%'
  796. You can compose statements of arbitrary complexity by combining ``Q`` objects
  797. with the ``&`` and ``|`` operators and use parenthetical grouping. Also, ``Q``
  798. objects can be negated using the ``~`` operator, allowing for combined lookups
  799. that combine both a normal query and a negated (``NOT``) query::
  800. Q(question__startswith='Who') | ~Q(pub_date__year=2005)
  801. Each lookup function that takes keyword-arguments
  802. (e.g. :meth:`~django.db.models.query.QuerySet.filter`,
  803. :meth:`~django.db.models.query.QuerySet.exclude`,
  804. :meth:`~django.db.models.query.QuerySet.get`) can also be passed one or more
  805. ``Q`` objects as positional (not-named) arguments. If you provide multiple
  806. ``Q`` object arguments to a lookup function, the arguments will be "AND"ed
  807. together. For example::
  808. Poll.objects.get(
  809. Q(question__startswith='Who'),
  810. Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6))
  811. )
  812. ... roughly translates into the SQL:
  813. .. code-block:: sql
  814. SELECT * from polls WHERE question LIKE 'Who%'
  815. AND (pub_date = '2005-05-02' OR pub_date = '2005-05-06')
  816. Lookup functions can mix the use of ``Q`` objects and keyword arguments. All
  817. arguments provided to a lookup function (be they keyword arguments or ``Q``
  818. objects) are "AND"ed together. However, if a ``Q`` object is provided, it must
  819. precede the definition of any keyword arguments. For example::
  820. Poll.objects.get(
  821. Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)),
  822. question__startswith='Who',
  823. )
  824. ... would be a valid query, equivalent to the previous example; but::
  825. # INVALID QUERY
  826. Poll.objects.get(
  827. question__startswith='Who',
  828. Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6))
  829. )
  830. ... would not be valid.
  831. .. seealso::
  832. The :source:`OR lookups examples <tests/or_lookups/tests.py>` in Django's
  833. unit tests show some possible uses of ``Q``.
  834. Comparing objects
  835. =================
  836. To compare two model instances, use the standard Python comparison operator,
  837. the double equals sign: ``==``. Behind the scenes, that compares the primary
  838. key values of two models.
  839. Using the ``Entry`` example above, the following two statements are equivalent::
  840. >>> some_entry == other_entry
  841. >>> some_entry.id == other_entry.id
  842. If a model's primary key isn't called ``id``, no problem. Comparisons will
  843. always use the primary key, whatever it's called. For example, if a model's
  844. primary key field is called ``name``, these two statements are equivalent::
  845. >>> some_obj == other_obj
  846. >>> some_obj.name == other_obj.name
  847. .. _topics-db-queries-delete:
  848. Deleting objects
  849. ================
  850. The delete method, conveniently, is named
  851. :meth:`~django.db.models.Model.delete`. This method immediately deletes the
  852. object and returns the number of objects deleted and a dictionary with
  853. the number of deletions per object type. Example::
  854. >>> e.delete()
  855. (1, {'blog.Entry': 1})
  856. You can also delete objects in bulk. Every
  857. :class:`~django.db.models.query.QuerySet` has a
  858. :meth:`~django.db.models.query.QuerySet.delete` method, which deletes all
  859. members of that :class:`~django.db.models.query.QuerySet`.
  860. For example, this deletes all ``Entry`` objects with a ``pub_date`` year of
  861. 2005::
  862. >>> Entry.objects.filter(pub_date__year=2005).delete()
  863. (5, {'webapp.Entry': 5})
  864. Keep in mind that this will, whenever possible, be executed purely in SQL, and
  865. so the ``delete()`` methods of individual object instances will not necessarily
  866. be called during the process. If you've provided a custom ``delete()`` method
  867. on a model class and want to ensure that it is called, you will need to
  868. "manually" delete instances of that model (e.g., by iterating over a
  869. :class:`~django.db.models.query.QuerySet` and calling ``delete()`` on each
  870. object individually) rather than using the bulk
  871. :meth:`~django.db.models.query.QuerySet.delete` method of a
  872. :class:`~django.db.models.query.QuerySet`.
  873. When Django deletes an object, by default it emulates the behavior of the SQL
  874. constraint ``ON DELETE CASCADE`` -- in other words, any objects which had
  875. foreign keys pointing at the object to be deleted will be deleted along with
  876. it. For example::
  877. b = Blog.objects.get(pk=1)
  878. # This will delete the Blog and all of its Entry objects.
  879. b.delete()
  880. This cascade behavior is customizable via the
  881. :attr:`~django.db.models.ForeignKey.on_delete` argument to the
  882. :class:`~django.db.models.ForeignKey`.
  883. Note that :meth:`~django.db.models.query.QuerySet.delete` is the only
  884. :class:`~django.db.models.query.QuerySet` method that is not exposed on a
  885. :class:`~django.db.models.Manager` itself. This is a safety mechanism to
  886. prevent you from accidentally requesting ``Entry.objects.delete()``, and
  887. deleting *all* the entries. If you *do* want to delete all the objects, then
  888. you have to explicitly request a complete query set::
  889. Entry.objects.all().delete()
  890. .. _topics-db-queries-copy:
  891. Copying model instances
  892. =======================
  893. Although there is no built-in method for copying model instances, it is
  894. possible to easily create new instance with all fields' values copied. In the
  895. simplest case, you can set ``pk`` to ``None`` and
  896. :attr:`_state.adding <django.db.models.Model._state>` to ``True``. Using our
  897. blog example::
  898. blog = Blog(name='My blog', tagline='Blogging is easy')
  899. blog.save() # blog.pk == 1
  900. blog.pk = None
  901. blog._state.adding = True
  902. blog.save() # blog.pk == 2
  903. Things get more complicated if you use inheritance. Consider a subclass of
  904. ``Blog``::
  905. class ThemeBlog(Blog):
  906. theme = models.CharField(max_length=200)
  907. django_blog = ThemeBlog(name='Django', tagline='Django is easy', theme='python')
  908. django_blog.save() # django_blog.pk == 3
  909. Due to how inheritance works, you have to set both ``pk`` and ``id`` to
  910. ``None``, and ``_state.adding`` to ``True``::
  911. django_blog.pk = None
  912. django_blog.id = None
  913. django_blog._state.adding = True
  914. django_blog.save() # django_blog.pk == 4
  915. This process doesn't copy relations that aren't part of the model's database
  916. table. For example, ``Entry`` has a ``ManyToManyField`` to ``Author``. After
  917. duplicating an entry, you must set the many-to-many relations for the new
  918. entry::
  919. entry = Entry.objects.all()[0] # some previous entry
  920. old_authors = entry.authors.all()
  921. entry.pk = None
  922. entry._state.adding = True
  923. entry.save()
  924. entry.authors.set(old_authors)
  925. For a ``OneToOneField``, you must duplicate the related object and assign it
  926. to the new object's field to avoid violating the one-to-one unique constraint.
  927. For example, assuming ``entry`` is already duplicated as above::
  928. detail = EntryDetail.objects.all()[0]
  929. detail.pk = None
  930. detail._state.adding = True
  931. detail.entry = entry
  932. detail.save()
  933. .. _topics-db-queries-update:
  934. Updating multiple objects at once
  935. =================================
  936. Sometimes you want to set a field to a particular value for all the objects in
  937. a :class:`~django.db.models.query.QuerySet`. You can do this with the
  938. :meth:`~django.db.models.query.QuerySet.update` method. For example::
  939. # Update all the headlines with pub_date in 2007.
  940. Entry.objects.filter(pub_date__year=2007).update(headline='Everything is the same')
  941. You can only set non-relation fields and :class:`~django.db.models.ForeignKey`
  942. fields using this method. To update a non-relation field, provide the new value
  943. as a constant. To update :class:`~django.db.models.ForeignKey` fields, set the
  944. new value to be the new model instance you want to point to. For example::
  945. >>> b = Blog.objects.get(pk=1)
  946. # Change every Entry so that it belongs to this Blog.
  947. >>> Entry.objects.all().update(blog=b)
  948. The ``update()`` method is applied instantly and returns the number of rows
  949. matched by the query (which may not be equal to the number of rows updated if
  950. some rows already have the new value). The only restriction on the
  951. :class:`~django.db.models.query.QuerySet` being updated is that it can only
  952. access one database table: the model's main table. You can filter based on
  953. related fields, but you can only update columns in the model's main
  954. table. Example::
  955. >>> b = Blog.objects.get(pk=1)
  956. # Update all the headlines belonging to this Blog.
  957. >>> Entry.objects.filter(blog=b).update(headline='Everything is the same')
  958. Be aware that the ``update()`` method is converted directly to an SQL
  959. statement. It is a bulk operation for direct updates. It doesn't run any
  960. :meth:`~django.db.models.Model.save` methods on your models, or emit the
  961. ``pre_save`` or ``post_save`` signals (which are a consequence of calling
  962. :meth:`~django.db.models.Model.save`), or honor the
  963. :attr:`~django.db.models.DateField.auto_now` field option.
  964. If you want to save every item in a :class:`~django.db.models.query.QuerySet`
  965. and make sure that the :meth:`~django.db.models.Model.save` method is called on
  966. each instance, you don't need any special function to handle that. Loop over
  967. them and call :meth:`~django.db.models.Model.save`::
  968. for item in my_queryset:
  969. item.save()
  970. Calls to update can also use :class:`F expressions <django.db.models.F>` to
  971. update one field based on the value of another field in the model. This is
  972. especially useful for incrementing counters based upon their current value. For
  973. example, to increment the pingback count for every entry in the blog::
  974. >>> Entry.objects.all().update(number_of_pingbacks=F('number_of_pingbacks') + 1)
  975. However, unlike ``F()`` objects in filter and exclude clauses, you can't
  976. introduce joins when you use ``F()`` objects in an update -- you can only
  977. reference fields local to the model being updated. If you attempt to introduce
  978. a join with an ``F()`` object, a ``FieldError`` will be raised::
  979. # This will raise a FieldError
  980. >>> Entry.objects.update(headline=F('blog__name'))
  981. .. _topics-db-queries-related:
  982. Related objects
  983. ===============
  984. When you define a relationship in a model (i.e., a
  985. :class:`~django.db.models.ForeignKey`,
  986. :class:`~django.db.models.OneToOneField`, or
  987. :class:`~django.db.models.ManyToManyField`), instances of that model will have
  988. a convenient API to access the related object(s).
  989. Using the models at the top of this page, for example, an ``Entry`` object ``e``
  990. can get its associated ``Blog`` object by accessing the ``blog`` attribute:
  991. ``e.blog``.
  992. (Behind the scenes, this functionality is implemented by Python
  993. :doc:`descriptors <python:howto/descriptor>`. This shouldn't really matter to
  994. you, but we point it out here for the curious.)
  995. Django also creates API accessors for the "other" side of the relationship --
  996. the link from the related model to the model that defines the relationship.
  997. For example, a ``Blog`` object ``b`` has access to a list of all related
  998. ``Entry`` objects via the ``entry_set`` attribute: ``b.entry_set.all()``.
  999. All examples in this section use the sample ``Blog``, ``Author`` and ``Entry``
  1000. models defined at the top of this page.
  1001. One-to-many relationships
  1002. -------------------------
  1003. Forward
  1004. ~~~~~~~
  1005. If a model has a :class:`~django.db.models.ForeignKey`, instances of that model
  1006. will have access to the related (foreign) object via an attribute of the model.
  1007. Example::
  1008. >>> e = Entry.objects.get(id=2)
  1009. >>> e.blog # Returns the related Blog object.
  1010. You can get and set via a foreign-key attribute. As you may expect, changes to
  1011. the foreign key aren't saved to the database until you call
  1012. :meth:`~django.db.models.Model.save`. Example::
  1013. >>> e = Entry.objects.get(id=2)
  1014. >>> e.blog = some_blog
  1015. >>> e.save()
  1016. If a :class:`~django.db.models.ForeignKey` field has ``null=True`` set (i.e.,
  1017. it allows ``NULL`` values), you can assign ``None`` to remove the relation.
  1018. Example::
  1019. >>> e = Entry.objects.get(id=2)
  1020. >>> e.blog = None
  1021. >>> e.save() # "UPDATE blog_entry SET blog_id = NULL ...;"
  1022. Forward access to one-to-many relationships is cached the first time the
  1023. related object is accessed. Subsequent accesses to the foreign key on the same
  1024. object instance are cached. Example::
  1025. >>> e = Entry.objects.get(id=2)
  1026. >>> print(e.blog) # Hits the database to retrieve the associated Blog.
  1027. >>> print(e.blog) # Doesn't hit the database; uses cached version.
  1028. Note that the :meth:`~django.db.models.query.QuerySet.select_related`
  1029. :class:`~django.db.models.query.QuerySet` method recursively prepopulates the
  1030. cache of all one-to-many relationships ahead of time. Example::
  1031. >>> e = Entry.objects.select_related().get(id=2)
  1032. >>> print(e.blog) # Doesn't hit the database; uses cached version.
  1033. >>> print(e.blog) # Doesn't hit the database; uses cached version.
  1034. .. _backwards-related-objects:
  1035. Following relationships "backward"
  1036. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  1037. If a model has a :class:`~django.db.models.ForeignKey`, instances of the
  1038. foreign-key model will have access to a :class:`~django.db.models.Manager` that
  1039. returns all instances of the first model. By default, this
  1040. :class:`~django.db.models.Manager` is named ``FOO_set``, where ``FOO`` is the
  1041. source model name, lowercased. This :class:`~django.db.models.Manager` returns
  1042. ``QuerySets``, which can be filtered and manipulated as described in the
  1043. "Retrieving objects" section above.
  1044. Example::
  1045. >>> b = Blog.objects.get(id=1)
  1046. >>> b.entry_set.all() # Returns all Entry objects related to Blog.
  1047. # b.entry_set is a Manager that returns QuerySets.
  1048. >>> b.entry_set.filter(headline__contains='Lennon')
  1049. >>> b.entry_set.count()
  1050. You can override the ``FOO_set`` name by setting the
  1051. :attr:`~django.db.models.ForeignKey.related_name` parameter in the
  1052. :class:`~django.db.models.ForeignKey` definition. For example, if the ``Entry``
  1053. model was altered to ``blog = ForeignKey(Blog, on_delete=models.CASCADE,
  1054. related_name='entries')``, the above example code would look like this::
  1055. >>> b = Blog.objects.get(id=1)
  1056. >>> b.entries.all() # Returns all Entry objects related to Blog.
  1057. # b.entries is a Manager that returns QuerySets.
  1058. >>> b.entries.filter(headline__contains='Lennon')
  1059. >>> b.entries.count()
  1060. .. _using-custom-reverse-manager:
  1061. Using a custom reverse manager
  1062. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  1063. By default the :class:`~django.db.models.fields.related.RelatedManager` used
  1064. for reverse relations is a subclass of the :ref:`default manager <manager-names>`
  1065. for that model. If you would like to specify a different manager for a given
  1066. query you can use the following syntax::
  1067. from django.db import models
  1068. class Entry(models.Model):
  1069. #...
  1070. objects = models.Manager() # Default Manager
  1071. entries = EntryManager() # Custom Manager
  1072. b = Blog.objects.get(id=1)
  1073. b.entry_set(manager='entries').all()
  1074. If ``EntryManager`` performed default filtering in its ``get_queryset()``
  1075. method, that filtering would apply to the ``all()`` call.
  1076. Specifying a custom reverse manager also enables you to call its custom
  1077. methods::
  1078. b.entry_set(manager='entries').is_published()
  1079. Additional methods to handle related objects
  1080. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  1081. In addition to the :class:`~django.db.models.query.QuerySet` methods defined in
  1082. "Retrieving objects" above, the :class:`~django.db.models.ForeignKey`
  1083. :class:`~django.db.models.Manager` has additional methods used to handle the
  1084. set of related objects. A synopsis of each is below, and complete details can
  1085. be found in the :doc:`related objects reference </ref/models/relations>`.
  1086. ``add(obj1, obj2, ...)``
  1087. Adds the specified model objects to the related object set.
  1088. ``create(**kwargs)``
  1089. Creates a new object, saves it and puts it in the related object set.
  1090. Returns the newly created object.
  1091. ``remove(obj1, obj2, ...)``
  1092. Removes the specified model objects from the related object set.
  1093. ``clear()``
  1094. Removes all objects from the related object set.
  1095. ``set(objs)``
  1096. Replace the set of related objects.
  1097. To assign the members of a related set, use the ``set()`` method with an
  1098. iterable of object instances. For example, if ``e1`` and ``e2`` are ``Entry``
  1099. instances::
  1100. b = Blog.objects.get(id=1)
  1101. b.entry_set.set([e1, e2])
  1102. If the ``clear()`` method is available, any pre-existing objects will be
  1103. removed from the ``entry_set`` before all objects in the iterable (in this
  1104. case, a list) are added to the set. If the ``clear()`` method is *not*
  1105. available, all objects in the iterable will be added without removing any
  1106. existing elements.
  1107. Each "reverse" operation described in this section has an immediate effect on
  1108. the database. Every addition, creation and deletion is immediately and
  1109. automatically saved to the database.
  1110. .. _m2m-reverse-relationships:
  1111. Many-to-many relationships
  1112. --------------------------
  1113. Both ends of a many-to-many relationship get automatic API access to the other
  1114. end. The API works similar to a "backward" one-to-many relationship, above.
  1115. One difference is in the attribute naming: The model that defines the
  1116. :class:`~django.db.models.ManyToManyField` uses the attribute name of that
  1117. field itself, whereas the "reverse" model uses the lowercased model name of the
  1118. original model, plus ``'_set'`` (just like reverse one-to-many relationships).
  1119. An example makes this easier to understand::
  1120. e = Entry.objects.get(id=3)
  1121. e.authors.all() # Returns all Author objects for this Entry.
  1122. e.authors.count()
  1123. e.authors.filter(name__contains='John')
  1124. a = Author.objects.get(id=5)
  1125. a.entry_set.all() # Returns all Entry objects for this Author.
  1126. Like :class:`~django.db.models.ForeignKey`,
  1127. :class:`~django.db.models.ManyToManyField` can specify
  1128. :attr:`~django.db.models.ManyToManyField.related_name`. In the above example,
  1129. if the :class:`~django.db.models.ManyToManyField` in ``Entry`` had specified
  1130. ``related_name='entries'``, then each ``Author`` instance would have an
  1131. ``entries`` attribute instead of ``entry_set``.
  1132. Another difference from one-to-many relationships is that in addition to model
  1133. instances, the ``add()``, ``set()``, and ``remove()`` methods on many-to-many
  1134. relationships accept primary key values. For example, if ``e1`` and ``e2`` are
  1135. ``Entry`` instances, then these ``set()`` calls work identically::
  1136. a = Author.objects.get(id=5)
  1137. a.entry_set.set([e1, e2])
  1138. a.entry_set.set([e1.pk, e2.pk])
  1139. One-to-one relationships
  1140. ------------------------
  1141. One-to-one relationships are very similar to many-to-one relationships. If you
  1142. define a :class:`~django.db.models.OneToOneField` on your model, instances of
  1143. that model will have access to the related object via an attribute of the
  1144. model.
  1145. For example::
  1146. class EntryDetail(models.Model):
  1147. entry = models.OneToOneField(Entry, on_delete=models.CASCADE)
  1148. details = models.TextField()
  1149. ed = EntryDetail.objects.get(id=2)
  1150. ed.entry # Returns the related Entry object.
  1151. The difference comes in "reverse" queries. The related model in a one-to-one
  1152. relationship also has access to a :class:`~django.db.models.Manager` object, but
  1153. that :class:`~django.db.models.Manager` represents a single object, rather than
  1154. a collection of objects::
  1155. e = Entry.objects.get(id=2)
  1156. e.entrydetail # returns the related EntryDetail object
  1157. If no object has been assigned to this relationship, Django will raise
  1158. a ``DoesNotExist`` exception.
  1159. Instances can be assigned to the reverse relationship in the same way as
  1160. you would assign the forward relationship::
  1161. e.entrydetail = ed
  1162. How are the backward relationships possible?
  1163. --------------------------------------------
  1164. Other object-relational mappers require you to define relationships on both
  1165. sides. The Django developers believe this is a violation of the DRY (Don't
  1166. Repeat Yourself) principle, so Django only requires you to define the
  1167. relationship on one end.
  1168. But how is this possible, given that a model class doesn't know which other
  1169. model classes are related to it until those other model classes are loaded?
  1170. The answer lies in the :data:`app registry <django.apps.apps>`. When Django
  1171. starts, it imports each application listed in :setting:`INSTALLED_APPS`, and
  1172. then the ``models`` module inside each application. Whenever a new model class
  1173. is created, Django adds backward-relationships to any related models. If the
  1174. related models haven't been imported yet, Django keeps tracks of the
  1175. relationships and adds them when the related models eventually are imported.
  1176. For this reason, it's particularly important that all the models you're using
  1177. be defined in applications listed in :setting:`INSTALLED_APPS`. Otherwise,
  1178. backwards relations may not work properly.
  1179. Queries over related objects
  1180. ----------------------------
  1181. Queries involving related objects follow the same rules as queries involving
  1182. normal value fields. When specifying the value for a query to match, you may
  1183. use either an object instance itself, or the primary key value for the object.
  1184. For example, if you have a Blog object ``b`` with ``id=5``, the following
  1185. three queries would be identical::
  1186. Entry.objects.filter(blog=b) # Query using object instance
  1187. Entry.objects.filter(blog=b.id) # Query using id from instance
  1188. Entry.objects.filter(blog=5) # Query using id directly
  1189. Falling back to raw SQL
  1190. =======================
  1191. If you find yourself needing to write an SQL query that is too complex for
  1192. Django's database-mapper to handle, you can fall back on writing SQL by hand.
  1193. Django has a couple of options for writing raw SQL queries; see
  1194. :doc:`/topics/db/sql`.
  1195. Finally, it's important to note that the Django database layer is merely an
  1196. interface to your database. You can access your database via other tools,
  1197. programming languages or database frameworks; there's nothing Django-specific
  1198. about your database.