/docs/ref/models/querysets.txt
Plain Text | 1886 lines | 1284 code | 602 blank | 0 comment | 0 complexity | e4beb2f635ed95d4fb9106c7e881b6a5 MD5 | raw file
Large files files are truncated, but you can click here to view the full file
1====================== 2QuerySet API reference 3====================== 4 5.. currentmodule:: django.db.models.query 6 7This document describes the details of the ``QuerySet`` API. It builds on the 8material presented in the :doc:`model </topics/db/models>` and :doc:`database 9query </topics/db/queries>` guides, so you'll probably want to read and 10understand those documents before reading this one. 11 12Throughout this reference we'll use the :ref:`example Weblog models 13<queryset-model-example>` presented in the :doc:`database query guide 14</topics/db/queries>`. 15 16.. _when-querysets-are-evaluated: 17 18When QuerySets are evaluated 19============================ 20 21Internally, a ``QuerySet`` can be constructed, filtered, sliced, and generally 22passed around without actually hitting the database. No database activity 23actually occurs until you do something to evaluate the queryset. 24 25You can evaluate a ``QuerySet`` in the following ways: 26 27 * **Iteration.** A ``QuerySet`` is iterable, and it executes its database 28 query the first time you iterate over it. For example, this will print 29 the headline of all entries in the database:: 30 31 for e in Entry.objects.all(): 32 print e.headline 33 34 * **Slicing.** As explained in :ref:`limiting-querysets`, a ``QuerySet`` can 35 be sliced, using Python's array-slicing syntax. Usually slicing a 36 ``QuerySet`` returns another (unevaluated) ``QuerySet``, but Django will 37 execute the database query if you use the "step" parameter of slice 38 syntax. 39 40 * **Pickling/Caching.** See the following section for details of what 41 is involved when `pickling QuerySets`_. The important thing for the 42 purposes of this section is that the results are read from the database. 43 44 * **repr().** A ``QuerySet`` is evaluated when you call ``repr()`` on it. 45 This is for convenience in the Python interactive interpreter, so you can 46 immediately see your results when using the API interactively. 47 48 * **len().** A ``QuerySet`` is evaluated when you call ``len()`` on it. 49 This, as you might expect, returns the length of the result list. 50 51 Note: *Don't* use ``len()`` on ``QuerySet``\s if all you want to do is 52 determine the number of records in the set. It's much more efficient to 53 handle a count at the database level, using SQL's ``SELECT COUNT(*)``, 54 and Django provides a ``count()`` method for precisely this reason. See 55 ``count()`` below. 56 57 * **list().** Force evaluation of a ``QuerySet`` by calling ``list()`` on 58 it. For example:: 59 60 entry_list = list(Entry.objects.all()) 61 62 Be warned, though, that this could have a large memory overhead, because 63 Django will load each element of the list into memory. In contrast, 64 iterating over a ``QuerySet`` will take advantage of your database to 65 load data and instantiate objects only as you need them. 66 67 * **bool().** Testing a ``QuerySet`` in a boolean context, such as using 68 ``bool()``, ``or``, ``and`` or an ``if`` statement, will cause the query 69 to be executed. If there is at least one result, the ``QuerySet`` is 70 ``True``, otherwise ``False``. For example:: 71 72 if Entry.objects.filter(headline="Test"): 73 print "There is at least one Entry with the headline Test" 74 75 Note: *Don't* use this if all you want to do is determine if at least one 76 result exists, and don't need the actual objects. It's more efficient to 77 use ``exists()`` (see below). 78 79.. _pickling QuerySets: 80 81Pickling QuerySets 82------------------ 83 84If you pickle_ a ``QuerySet``, this will force all the results to be loaded 85into memory prior to pickling. Pickling is usually used as a precursor to 86caching and when the cached queryset is reloaded, you want the results to 87already be present and ready for use (reading from the database can take some 88time, defeating the purpose of caching). This means that when you unpickle a 89``QuerySet``, it contains the results at the moment it was pickled, rather 90than the results that are currently in the database. 91 92If you only want to pickle the necessary information to recreate the 93``QuerySet`` from the database at a later time, pickle the ``query`` attribute 94of the ``QuerySet``. You can then recreate the original ``QuerySet`` (without 95any results loaded) using some code like this:: 96 97 >>> import pickle 98 >>> query = pickle.loads(s) # Assuming 's' is the pickled string. 99 >>> qs = MyModel.objects.all() 100 >>> qs.query = query # Restore the original 'query'. 101 102The ``query`` attribute is an opaque object. It represents the internals of 103the query construction and is not part of the public API. However, it is safe 104(and fully supported) to pickle and unpickle the attribute's contents as 105described here. 106 107.. admonition:: You can't share pickles between versions 108 109 Pickles of QuerySets are only valid for the version of Django that 110 was used to generate them. If you generate a pickle using Django 111 version N, there is no guarantee that pickle will be readable with 112 Django version N+1. Pickles should not be used as part of a long-term 113 archival strategy. 114 115.. _pickle: http://docs.python.org/library/pickle.html 116 117.. _queryset-api: 118 119QuerySet API 120============ 121 122Though you usually won't create one manually -- you'll go through a 123:class:`~django.db.models.Manager` -- here's the formal declaration of a 124``QuerySet``: 125 126.. class:: QuerySet([model=None, query=None, using=None]) 127 128 Usually when you'll interact with a ``QuerySet`` you'll use it by 129 :ref:`chaining filters <chaining-filters>`. To make this work, most 130 ``QuerySet`` methods return new querysets. These methods are covered in 131 detail later in this section. 132 133 The ``QuerySet`` class has two public attributes you can use for 134 introspection: 135 136 .. attribute:: ordered 137 138 ``True`` if the ``QuerySet`` is ordered -- i.e. has an order_by() 139 clause or a default ordering on the model. ``False`` otherwise. 140 141 .. attribute:: db 142 143 The database that will be used if this query is executed now. 144 145 .. note:: 146 147 The ``query`` parameter to :class:`QuerySet` exists so that specialized 148 query subclasses such as 149 :class:`~django.contrib.gis.db.models.GeoQuerySet` can reconstruct 150 internal query state. The value of the parameter is an opaque 151 representation of that query state and is not part of a public API. 152 To put it simply: if you need to ask, you don't need to use it. 153 154.. currentmodule:: django.db.models.query.QuerySet 155 156Methods that return new QuerySets 157--------------------------------- 158 159Django provides a range of ``QuerySet`` refinement methods that modify either 160the types of results returned by the ``QuerySet`` or the way its SQL query is 161executed. 162 163filter 164~~~~~~ 165 166.. method:: filter(**kwargs) 167 168Returns a new ``QuerySet`` containing objects that match the given lookup 169parameters. 170 171The lookup parameters (``**kwargs``) should be in the format described in 172`Field lookups`_ below. Multiple parameters are joined via ``AND`` in the 173underlying SQL statement. 174 175exclude 176~~~~~~~ 177 178.. method:: exclude(**kwargs) 179 180Returns a new ``QuerySet`` containing objects that do *not* match the given 181lookup parameters. 182 183The lookup parameters (``**kwargs``) should be in the format described in 184`Field lookups`_ below. Multiple parameters are joined via ``AND`` in the 185underlying SQL statement, and the whole thing is enclosed in a ``NOT()``. 186 187This example excludes all entries whose ``pub_date`` is later than 2005-1-3 188AND whose ``headline`` is "Hello":: 189 190 Entry.objects.exclude(pub_date__gt=datetime.date(2005, 1, 3), headline='Hello') 191 192In SQL terms, that evaluates to:: 193 194 SELECT ... 195 WHERE NOT (pub_date > '2005-1-3' AND headline = 'Hello') 196 197This example excludes all entries whose ``pub_date`` is later than 2005-1-3 198OR whose headline is "Hello":: 199 200 Entry.objects.exclude(pub_date__gt=datetime.date(2005, 1, 3)).exclude(headline='Hello') 201 202In SQL terms, that evaluates to:: 203 204 SELECT ... 205 WHERE NOT pub_date > '2005-1-3' 206 AND NOT headline = 'Hello' 207 208Note the second example is more restrictive. 209 210annotate 211~~~~~~~~ 212 213.. method:: annotate(*args, **kwargs) 214 215Annotates each object in the ``QuerySet`` with the provided list of 216aggregate values (averages, sums, etc) that have been computed over 217the objects that are related to the objects in the ``QuerySet``. 218Each argument to ``annotate()`` is an annotation that will be added 219to each object in the ``QuerySet`` that is returned. 220 221The aggregation functions that are provided by Django are described 222in `Aggregation Functions`_ below. 223 224Annotations specified using keyword arguments will use the keyword as 225the alias for the annotation. Anonymous arguments will have an alias 226generated for them based upon the name of the aggregate function and 227the model field that is being aggregated. 228 229For example, if you were manipulating a list of blogs, you may want 230to determine how many entries have been made in each blog:: 231 232 >>> q = Blog.objects.annotate(Count('entry')) 233 # The name of the first blog 234 >>> q[0].name 235 'Blogasaurus' 236 # The number of entries on the first blog 237 >>> q[0].entry__count 238 42 239 240The ``Blog`` model doesn't define an ``entry__count`` attribute by itself, 241but by using a keyword argument to specify the aggregate function, you can 242control the name of the annotation:: 243 244 >>> q = Blog.objects.annotate(number_of_entries=Count('entry')) 245 # The number of entries on the first blog, using the name provided 246 >>> q[0].number_of_entries 247 42 248 249For an in-depth discussion of aggregation, see :doc:`the topic guide on 250Aggregation </topics/db/aggregation>`. 251 252order_by 253~~~~~~~~ 254 255.. method:: order_by(*fields) 256 257By default, results returned by a ``QuerySet`` are ordered by the ordering 258tuple given by the ``ordering`` option in the model's ``Meta``. You can 259override this on a per-``QuerySet`` basis by using the ``order_by`` method. 260 261Example:: 262 263 Entry.objects.filter(pub_date__year=2005).order_by('-pub_date', 'headline') 264 265The result above will be ordered by ``pub_date`` descending, then by 266``headline`` ascending. The negative sign in front of ``"-pub_date"`` indicates 267*descending* order. Ascending order is implied. To order randomly, use ``"?"``, 268like so:: 269 270 Entry.objects.order_by('?') 271 272Note: ``order_by('?')`` queries may be expensive and slow, depending on the 273database backend you're using. 274 275To order by a field in a different model, use the same syntax as when you are 276querying across model relations. That is, the name of the field, followed by a 277double underscore (``__``), followed by the name of the field in the new model, 278and so on for as many models as you want to join. For example:: 279 280 Entry.objects.order_by('blog__name', 'headline') 281 282If you try to order by a field that is a relation to another model, Django will 283use the default ordering on the related model (or order by the related model's 284primary key if there is no ``Meta.ordering`` specified. For example:: 285 286 Entry.objects.order_by('blog') 287 288...is identical to:: 289 290 Entry.objects.order_by('blog__id') 291 292...since the ``Blog`` model has no default ordering specified. 293 294Be cautious when ordering by fields in related models if you are also using 295``distinct()``. See the note in :meth:`distinct` for an explanation of how 296related model ordering can change the expected results. 297 298It is permissible to specify a multi-valued field to order the results by (for 299example, a ``ManyToMany`` field). Normally this won't be a sensible thing to 300do and it's really an advanced usage feature. However, if you know that your 301queryset's filtering or available data implies that there will only be one 302ordering piece of data for each of the main items you are selecting, the 303ordering may well be exactly what you want to do. Use ordering on multi-valued 304fields with care and make sure the results are what you expect. 305 306There's no way to specify whether ordering should be case sensitive. With 307respect to case-sensitivity, Django will order results however your database 308backend normally orders them. 309 310If you don't want any ordering to be applied to a query, not even the default 311ordering, call ``order_by()`` with no parameters. 312 313You can tell if a query is ordered or not by checking the 314:attr:`.QuerySet.ordered` attribute, which will be ``True`` if the 315``QuerySet`` has been ordered in any way. 316 317reverse 318~~~~~~~ 319 320.. method:: reverse() 321 322Use the ``reverse()`` method to reverse the order in which a queryset's 323elements are returned. Calling ``reverse()`` a second time restores the 324ordering back to the normal direction. 325 326To retrieve the ''last'' five items in a queryset, you could do this:: 327 328 my_queryset.reverse()[:5] 329 330Note that this is not quite the same as slicing from the end of a sequence in 331Python. The above example will return the last item first, then the 332penultimate item and so on. If we had a Python sequence and looked at 333``seq[-5:]``, we would see the fifth-last item first. Django doesn't support 334that mode of access (slicing from the end), because it's not possible to do it 335efficiently in SQL. 336 337Also, note that ``reverse()`` should generally only be called on a 338``QuerySet`` which has a defined ordering (e.g., when querying against 339a model which defines a default ordering, or when using 340``order_by()``). If no such ordering is defined for a given 341``QuerySet``, calling ``reverse()`` on it has no real effect (the 342ordering was undefined prior to calling ``reverse()``, and will remain 343undefined afterward). 344 345distinct 346~~~~~~~~ 347 348.. method:: distinct() 349 350Returns a new ``QuerySet`` that uses ``SELECT DISTINCT`` in its SQL query. This 351eliminates duplicate rows from the query results. 352 353By default, a ``QuerySet`` will not eliminate duplicate rows. In practice, this 354is rarely a problem, because simple queries such as ``Blog.objects.all()`` 355don't introduce the possibility of duplicate result rows. However, if your 356query spans multiple tables, it's possible to get duplicate results when a 357``QuerySet`` is evaluated. That's when you'd use ``distinct()``. 358 359.. note:: 360 Any fields used in an :meth:`order_by` call are included in the SQL 361 ``SELECT`` columns. This can sometimes lead to unexpected results when 362 used in conjunction with ``distinct()``. If you order by fields from a 363 related model, those fields will be added to the selected columns and they 364 may make otherwise duplicate rows appear to be distinct. Since the extra 365 columns don't appear in the returned results (they are only there to 366 support ordering), it sometimes looks like non-distinct results are being 367 returned. 368 369 Similarly, if you use a ``values()`` query to restrict the columns 370 selected, the columns used in any ``order_by()`` (or default model 371 ordering) will still be involved and may affect uniqueness of the results. 372 373 The moral here is that if you are using ``distinct()`` be careful about 374 ordering by related models. Similarly, when using ``distinct()`` and 375 ``values()`` together, be careful when ordering by fields not in the 376 ``values()`` call. 377 378values 379~~~~~~ 380 381.. method:: values(*fields) 382 383Returns a ``ValuesQuerySet`` -- a ``QuerySet`` that returns dictionaries when 384used as an iterable, rather than model-instance objects. 385 386Each of those dictionaries represents an object, with the keys corresponding to 387the attribute names of model objects. 388 389This example compares the dictionaries of ``values()`` with the normal model 390objects:: 391 392 # This list contains a Blog object. 393 >>> Blog.objects.filter(name__startswith='Beatles') 394 [<Blog: Beatles Blog>] 395 396 # This list contains a dictionary. 397 >>> Blog.objects.filter(name__startswith='Beatles').values() 398 [{'id': 1, 'name': 'Beatles Blog', 'tagline': 'All the latest Beatles news.'}] 399 400``values()`` takes optional positional arguments, ``*fields``, which specify 401field names to which the ``SELECT`` should be limited. If you specify the 402fields, each dictionary will contain only the field keys/values for the fields 403you specify. If you don't specify the fields, each dictionary will contain a 404key and value for every field in the database table. 405 406Example:: 407 408 >>> Blog.objects.values() 409 [{'id': 1, 'name': 'Beatles Blog', 'tagline': 'All the latest Beatles news.'}], 410 >>> Blog.objects.values('id', 'name') 411 [{'id': 1, 'name': 'Beatles Blog'}] 412 413A few subtleties that are worth mentioning: 414 415 * If you have a field called ``foo`` that is a 416 :class:`~django.db.models.ForeignKey`, the default ``values()`` call 417 will return a dictionary key called ``foo_id``, since this is the name 418 of the hidden model attribute that stores the actual value (the ``foo`` 419 attribute refers to the related model). When you are calling 420 ``values()`` and passing in field names, you can pass in either ``foo`` 421 or ``foo_id`` and you will get back the same thing (the dictionary key 422 will match the field name you passed in). 423 424 For example:: 425 426 >>> Entry.objects.values() 427 [{'blog_id': 1, 'headline': u'First Entry', ...}, ...] 428 429 >>> Entry.objects.values('blog') 430 [{'blog': 1}, ...] 431 432 >>> Entry.objects.values('blog_id') 433 [{'blog_id': 1}, ...] 434 435 * When using ``values()`` together with ``distinct()``, be aware that 436 ordering can affect the results. See the note in :meth:`distinct` for 437 details. 438 439 * If you use a ``values()`` clause after an ``extra()`` clause, 440 any fields defined by a ``select`` argument in the ``extra()`` 441 must be explicitly included in the ``values()`` clause. However, 442 if the ``extra()`` clause is used after the ``values()``, the 443 fields added by the select will be included automatically. 444 445A ``ValuesQuerySet`` is useful when you know you're only going to need values 446from a small number of the available fields and you won't need the 447functionality of a model instance object. It's more efficient to select only 448the fields you need to use. 449 450Finally, note a ``ValuesQuerySet`` is a subclass of ``QuerySet``, so it has all 451methods of ``QuerySet``. You can call ``filter()`` on it, or ``order_by()``, or 452whatever. Yes, that means these two calls are identical:: 453 454 Blog.objects.values().order_by('id') 455 Blog.objects.order_by('id').values() 456 457The people who made Django prefer to put all the SQL-affecting methods first, 458followed (optionally) by any output-affecting methods (such as ``values()``), 459but it doesn't really matter. This is your chance to really flaunt your 460individualism. 461 462.. versionchanged:: 1.3 463 464The ``values()`` method previously did not return anything for 465:class:`~django.db.models.ManyToManyField` attributes and would raise an error 466if you tried to pass this type of field to it. 467 468This restriction has been lifted, and you can now also refer to fields on 469related models with reverse relations through ``OneToOneField``, ``ForeignKey`` 470and ``ManyToManyField`` attributes:: 471 472 Blog.objects.values('name', 'entry__headline') 473 [{'name': 'My blog', 'entry__headline': 'An entry'}, 474 {'name': 'My blog', 'entry__headline': 'Another entry'}, ...] 475 476.. warning:: 477 478 Because :class:`~django.db.models.ManyToManyField` attributes and reverse 479 relations can have multiple related rows, including these can have a 480 multiplier effect on the size of your result set. This will be especially 481 pronounced if you include multiple such fields in your ``values()`` query, 482 in which case all possible combinations will be returned. 483 484values_list 485~~~~~~~~~~~ 486 487.. method:: values_list(*fields) 488 489This is similar to ``values()`` except that instead of returning dictionaries, 490it returns tuples when iterated over. Each tuple contains the value from the 491respective field passed into the ``values_list()`` call -- so the first item is 492the first field, etc. For example:: 493 494 >>> Entry.objects.values_list('id', 'headline') 495 [(1, u'First entry'), ...] 496 497If you only pass in a single field, you can also pass in the ``flat`` 498parameter. If ``True``, this will mean the returned results are single values, 499rather than one-tuples. An example should make the difference clearer:: 500 501 >>> Entry.objects.values_list('id').order_by('id') 502 [(1,), (2,), (3,), ...] 503 504 >>> Entry.objects.values_list('id', flat=True).order_by('id') 505 [1, 2, 3, ...] 506 507It is an error to pass in ``flat`` when there is more than one field. 508 509If you don't pass any values to ``values_list()``, it will return all the 510fields in the model, in the order they were declared. 511 512dates 513~~~~~ 514 515.. method:: dates(field, kind, order='ASC') 516 517Returns a ``DateQuerySet`` -- a ``QuerySet`` that evaluates to a list of 518``datetime.datetime`` objects representing all available dates of a particular 519kind within the contents of the ``QuerySet``. 520 521``field`` should be the name of a ``DateField`` or ``DateTimeField`` of your 522model. 523 524``kind`` should be either ``"year"``, ``"month"`` or ``"day"``. Each 525``datetime.datetime`` object in the result list is "truncated" to the given 526``type``. 527 528 * ``"year"`` returns a list of all distinct year values for the field. 529 * ``"month"`` returns a list of all distinct year/month values for the field. 530 * ``"day"`` returns a list of all distinct year/month/day values for the field. 531 532``order``, which defaults to ``'ASC'``, should be either ``'ASC'`` or 533``'DESC'``. This specifies how to order the results. 534 535Examples:: 536 537 >>> Entry.objects.dates('pub_date', 'year') 538 [datetime.datetime(2005, 1, 1)] 539 >>> Entry.objects.dates('pub_date', 'month') 540 [datetime.datetime(2005, 2, 1), datetime.datetime(2005, 3, 1)] 541 >>> Entry.objects.dates('pub_date', 'day') 542 [datetime.datetime(2005, 2, 20), datetime.datetime(2005, 3, 20)] 543 >>> Entry.objects.dates('pub_date', 'day', order='DESC') 544 [datetime.datetime(2005, 3, 20), datetime.datetime(2005, 2, 20)] 545 >>> Entry.objects.filter(headline__contains='Lennon').dates('pub_date', 'day') 546 [datetime.datetime(2005, 3, 20)] 547 548none 549~~~~ 550 551.. method:: none() 552 553Returns an ``EmptyQuerySet`` -- a ``QuerySet`` that always evaluates to 554an empty list. This can be used in cases where you know that you should 555return an empty result set and your caller is expecting a ``QuerySet`` 556object (instead of returning an empty list, for example.) 557 558Examples:: 559 560 >>> Entry.objects.none() 561 [] 562 563all 564~~~ 565 566.. method:: all() 567 568Returns a *copy* of the current ``QuerySet`` (or ``QuerySet`` subclass you 569pass in). This can be useful in some situations where you might want to pass 570in either a model manager or a ``QuerySet`` and do further filtering on the 571result. You can safely call ``all()`` on either object and then you'll 572definitely have a ``QuerySet`` to work with. 573 574.. _select-related: 575 576select_related 577~~~~~~~~~~~~~~ 578 579.. method:: select_related() 580 581Returns a ``QuerySet`` that will automatically "follow" foreign-key 582relationships, selecting that additional related-object data when it executes 583its query. This is a performance booster which results in (sometimes much) 584larger queries but means later use of foreign-key relationships won't require 585database queries. 586 587The following examples illustrate the difference between plain lookups and 588``select_related()`` lookups. Here's standard lookup:: 589 590 # Hits the database. 591 e = Entry.objects.get(id=5) 592 593 # Hits the database again to get the related Blog object. 594 b = e.blog 595 596And here's ``select_related`` lookup:: 597 598 # Hits the database. 599 e = Entry.objects.select_related().get(id=5) 600 601 # Doesn't hit the database, because e.blog has been prepopulated 602 # in the previous query. 603 b = e.blog 604 605``select_related()`` follows foreign keys as far as possible. If you have the 606following models:: 607 608 class City(models.Model): 609 # ... 610 611 class Person(models.Model): 612 # ... 613 hometown = models.ForeignKey(City) 614 615 class Book(models.Model): 616 # ... 617 author = models.ForeignKey(Person) 618 619...then a call to ``Book.objects.select_related().get(id=4)`` will cache the 620related ``Person`` *and* the related ``City``:: 621 622 b = Book.objects.select_related().get(id=4) 623 p = b.author # Doesn't hit the database. 624 c = p.hometown # Doesn't hit the database. 625 626 b = Book.objects.get(id=4) # No select_related() in this example. 627 p = b.author # Hits the database. 628 c = p.hometown # Hits the database. 629 630Note that, by default, ``select_related()`` does not follow foreign keys that 631have ``null=True``. 632 633Usually, using ``select_related()`` can vastly improve performance because your 634app can avoid many database calls. However, in situations with deeply nested 635sets of relationships ``select_related()`` can sometimes end up following "too 636many" relations, and can generate queries so large that they end up being slow. 637 638In these situations, you can use the ``depth`` argument to ``select_related()`` 639to control how many "levels" of relations ``select_related()`` will actually 640follow:: 641 642 b = Book.objects.select_related(depth=1).get(id=4) 643 p = b.author # Doesn't hit the database. 644 c = p.hometown # Requires a database call. 645 646Sometimes you only want to access specific models that are related to your root 647model, not all of the related models. In these cases, you can pass the related 648field names to ``select_related()`` and it will only follow those relations. 649You can even do this for models that are more than one relation away by 650separating the field names with double underscores, just as for filters. For 651example, if you have this model:: 652 653 class Room(models.Model): 654 # ... 655 building = models.ForeignKey(...) 656 657 class Group(models.Model): 658 # ... 659 teacher = models.ForeignKey(...) 660 room = models.ForeignKey(Room) 661 subject = models.ForeignKey(...) 662 663...and you only needed to work with the ``room`` and ``subject`` attributes, 664you could write this:: 665 666 g = Group.objects.select_related('room', 'subject') 667 668This is also valid:: 669 670 g = Group.objects.select_related('room__building', 'subject') 671 672...and would also pull in the ``building`` relation. 673 674You can refer to any ``ForeignKey`` or ``OneToOneField`` relation in 675the list of fields passed to ``select_related``. This includes foreign 676keys that have ``null=True`` (unlike the default ``select_related()`` 677call). It's an error to use both a list of fields and the ``depth`` 678parameter in the same ``select_related()`` call, since they are 679conflicting options. 680 681.. versionchanged:: 1.2 682 683You can also refer to the reverse direction of a ``OneToOneFields`` in 684the list of fields passed to ``select_related`` -- that is, you can traverse 685a ``OneToOneField`` back to the object on which the field is defined. Instead 686of specifying the field name, use the ``related_name`` for the field on the 687related object. 688 689``OneToOneFields`` will not be traversed in the reverse direction if you 690are performing a depth-based ``select_related``. 691 692extra 693~~~~~ 694 695.. method:: extra(select=None, where=None, params=None, tables=None, order_by=None, select_params=None) 696 697Sometimes, the Django query syntax by itself can't easily express a complex 698``WHERE`` clause. For these edge cases, Django provides the ``extra()`` 699``QuerySet`` modifier -- a hook for injecting specific clauses into the SQL 700generated by a ``QuerySet``. 701 702By definition, these extra lookups may not be portable to different database 703engines (because you're explicitly writing SQL code) and violate the DRY 704principle, so you should avoid them if possible. 705 706Specify one or more of ``params``, ``select``, ``where`` or ``tables``. None 707of the arguments is required, but you should use at least one of them. 708 709 * ``select`` 710 The ``select`` argument lets you put extra fields in the ``SELECT`` clause. 711 It should be a dictionary mapping attribute names to SQL clauses to use to 712 calculate that attribute. 713 714 Example:: 715 716 Entry.objects.extra(select={'is_recent': "pub_date > '2006-01-01'"}) 717 718 As a result, each ``Entry`` object will have an extra attribute, 719 ``is_recent``, a boolean representing whether the entry's ``pub_date`` is 720 greater than Jan. 1, 2006. 721 722 Django inserts the given SQL snippet directly into the ``SELECT`` 723 statement, so the resulting SQL of the above example would be something 724 like:: 725 726 SELECT blog_entry.*, (pub_date > '2006-01-01') AS is_recent 727 FROM blog_entry; 728 729 730 The next example is more advanced; it does a subquery to give each 731 resulting ``Blog`` object an ``entry_count`` attribute, an integer count 732 of associated ``Entry`` objects:: 733 734 Blog.objects.extra( 735 select={ 736 'entry_count': 'SELECT COUNT(*) FROM blog_entry WHERE blog_entry.blog_id = blog_blog.id' 737 }, 738 ) 739 740 (In this particular case, we're exploiting the fact that the query will 741 already contain the ``blog_blog`` table in its ``FROM`` clause.) 742 743 The resulting SQL of the above example would be:: 744 745 SELECT blog_blog.*, (SELECT COUNT(*) FROM blog_entry WHERE blog_entry.blog_id = blog_blog.id) AS entry_count 746 FROM blog_blog; 747 748 Note that the parenthesis required by most database engines around 749 subqueries are not required in Django's ``select`` clauses. Also note that 750 some database backends, such as some MySQL versions, don't support 751 subqueries. 752 753 In some rare cases, you might wish to pass parameters to the SQL fragments 754 in ``extra(select=...)``. For this purpose, use the ``select_params`` 755 parameter. Since ``select_params`` is a sequence and the ``select`` 756 attribute is a dictionary, some care is required so that the parameters 757 are matched up correctly with the extra select pieces. In this situation, 758 you should use a ``django.utils.datastructures.SortedDict`` for the 759 ``select`` value, not just a normal Python dictionary. 760 761 This will work, for example:: 762 763 Blog.objects.extra( 764 select=SortedDict([('a', '%s'), ('b', '%s')]), 765 select_params=('one', 'two')) 766 767 The only thing to be careful about when using select parameters in 768 ``extra()`` is to avoid using the substring ``"%%s"`` (that's *two* 769 percent characters before the ``s``) in the select strings. Django's 770 tracking of parameters looks for ``%s`` and an escaped ``%`` character 771 like this isn't detected. That will lead to incorrect results. 772 773 * ``where`` / ``tables`` 774 You can define explicit SQL ``WHERE`` clauses -- perhaps to perform 775 non-explicit joins -- by using ``where``. You can manually add tables to 776 the SQL ``FROM`` clause by using ``tables``. 777 778 ``where`` and ``tables`` both take a list of strings. All ``where`` 779 parameters are "AND"ed to any other search criteria. 780 781 Example:: 782 783 Entry.objects.extra(where=['id IN (3, 4, 5, 20)']) 784 785 ...translates (roughly) into the following SQL:: 786 787 SELECT * FROM blog_entry WHERE id IN (3, 4, 5, 20); 788 789 Be careful when using the ``tables`` parameter if you're specifying 790 tables that are already used in the query. When you add extra tables 791 via the ``tables`` parameter, Django assumes you want that table included 792 an extra time, if it is already included. That creates a problem, 793 since the table name will then be given an alias. If a table appears 794 multiple times in an SQL statement, the second and subsequent occurrences 795 must use aliases so the database can tell them apart. If you're 796 referring to the extra table you added in the extra ``where`` parameter 797 this is going to cause errors. 798 799 Normally you'll only be adding extra tables that don't already appear in 800 the query. However, if the case outlined above does occur, there are a few 801 solutions. First, see if you can get by without including the extra table 802 and use the one already in the query. If that isn't possible, put your 803 ``extra()`` call at the front of the queryset construction so that your 804 table is the first use of that table. Finally, if all else fails, look at 805 the query produced and rewrite your ``where`` addition to use the alias 806 given to your extra table. The alias will be the same each time you 807 construct the queryset in the same way, so you can rely upon the alias 808 name to not change. 809 810 * ``order_by`` 811 If you need to order the resulting queryset using some of the new fields 812 or tables you have included via ``extra()`` use the ``order_by`` parameter 813 to ``extra()`` and pass in a sequence of strings. These strings should 814 either be model fields (as in the normal ``order_by()`` method on 815 querysets), of the form ``table_name.column_name`` or an alias for a column 816 that you specified in the ``select`` parameter to ``extra()``. 817 818 For example:: 819 820 q = Entry.objects.extra(select={'is_recent': "pub_date > '2006-01-01'"}) 821 q = q.extra(order_by = ['-is_recent']) 822 823 This would sort all the items for which ``is_recent`` is true to the front 824 of the result set (``True`` sorts before ``False`` in a descending 825 ordering). 826 827 This shows, by the way, that you can make multiple calls to 828 ``extra()`` and it will behave as you expect (adding new constraints each 829 time). 830 831 * ``params`` 832 The ``where`` parameter described above may use standard Python database 833 string placeholders -- ``'%s'`` to indicate parameters the database engine 834 should automatically quote. The ``params`` argument is a list of any extra 835 parameters to be substituted. 836 837 Example:: 838 839 Entry.objects.extra(where=['headline=%s'], params=['Lennon']) 840 841 Always use ``params`` instead of embedding values directly into ``where`` 842 because ``params`` will ensure values are quoted correctly according to 843 your particular backend. (For example, quotes will be escaped correctly.) 844 845 Bad:: 846 847 Entry.objects.extra(where=["headline='Lennon'"]) 848 849 Good:: 850 851 Entry.objects.extra(where=['headline=%s'], params=['Lennon']) 852 853defer 854~~~~~ 855 856.. method:: defer(*fields) 857 858In some complex data-modeling situations, your models might contain a lot of 859fields, some of which could contain a lot of data (for example, text fields), 860or require expensive processing to convert them to Python objects. If you are 861using the results of a queryset in some situation where you know you don't 862need those particular fields, you can tell Django not to retrieve them from 863the database. 864 865This is done by passing the names of the fields to not load to ``defer()``:: 866 867 Entry.objects.defer("headline", "body") 868 869A queryset that has deferred fields will still return model instances. Each 870deferred field will be retrieved from the database if you access that field 871(one at a time, not all the deferred fields at once). 872 873You can make multiple calls to ``defer()``. Each call adds new fields to the 874deferred set:: 875 876 # Defers both the body and headline fields. 877 Entry.objects.defer("body").filter(rating=5).defer("headline") 878 879The order in which fields are added to the deferred set does not matter. 880Calling ``defer()`` with a field name that has already been deferred is 881harmless (the field will still be deferred). 882 883You can defer loading of fields in related models (if the related models are 884loading via ``select_related()``) by using the standard double-underscore 885notation to separate related fields:: 886 887 Blog.objects.select_related().defer("entry__headline", "entry__body") 888 889If you want to clear the set of deferred fields, pass ``None`` as a parameter 890to ``defer()``:: 891 892 # Load all fields immediately. 893 my_queryset.defer(None) 894 895Some fields in a model won't be deferred, even if you ask for them. You can 896never defer the loading of the primary key. If you are using 897``select_related()`` to retrieve other models at the same time you shouldn't 898defer the loading of the field that connects from the primary model to the 899related one (at the moment, that doesn't raise an error, but it will 900eventually). 901 902.. note:: 903 904 The ``defer()`` method (and its cousin, ``only()``, below) are only for 905 advanced use-cases. They provide an optimization for when you have 906 analyzed your queries closely and understand *exactly* what information 907 you need and have measured that the difference between returning the 908 fields you need and the full set of fields for the model will be 909 significant. When you are initially developing your applications, don't 910 bother using ``defer()``; leave it until your query construction has 911 settled down and you understand where the hot-points are. 912 913only 914~~~~ 915 916.. method:: only(*fields) 917 918The ``only()`` method is more or less the opposite of ``defer()``. You 919call it with the fields that should *not* be deferred when retrieving a model. 920If you have a model where almost all the fields need to be deferred, using 921``only()`` to specify the complementary set of fields could result in simpler 922code. 923 924If you have a model with fields ``name``, ``age`` and ``biography``, the 925following two querysets are the same, in terms of deferred fields:: 926 927 Person.objects.defer("age", "biography") 928 Person.objects.only("name") 929 930Whenever you call ``only()`` it *replaces* the set of fields to load 931immediately. The method's name is mnemonic: **only** those fields are loaded 932immediately; the remainder are deferred. Thus, successive calls to ``only()`` 933result in only the final fields being considered:: 934 935 # This will defer all fields except the headline. 936 Entry.objects.only("body", "rating").only("headline") 937 938Since ``defer()`` acts incrementally (adding fields to the deferred list), you 939can combine calls to ``only()`` and ``defer()`` and things will behave 940logically:: 941 942 # Final result is that everything except "headline" is deferred. 943 Entry.objects.only("headline", "body").defer("body") 944 945 # Final result loads headline and body immediately (only() replaces any 946 # existing set of fields). 947 Entry.objects.defer("body").only("headline", "body") 948 949using 950~~~~~ 951 952.. method:: using(alias) 953 954.. versionadded:: 1.2 955 956This method is for controlling which database the ``QuerySet`` will be 957evaluated against if you are using more than one database. The only argument 958this method takes is the alias of a database, as defined in 959:setting:`DATABASES`. 960 961For example:: 962 963 # queries the database with the 'default' alias. 964 >>> Entry.objects.all() 965 966 # queries the database with the 'backup' alias 967 >>> Entry.objects.using('backup') 968 969 970Methods that do not return QuerySets 971------------------------------------ 972 973The following ``QuerySet`` methods evaluate the ``QuerySet`` and return 974something *other than* a ``QuerySet``. 975 976These methods do not use a cache (see :ref:`caching-and-querysets`). Rather, 977they query the database each time they're called. 978 979get 980~~~ 981 982.. method:: get(**kwargs) 983 984Returns the object matching the given lookup parameters, which should be in 985the format described in `Field lookups`_. 986 987``get()`` raises ``MultipleObjectsReturned`` if more than one object was 988found. The ``MultipleObjectsReturned`` exception is an attribute of the model 989class. 990 991``get()`` raises a ``DoesNotExist`` exception if an object wasn't found for 992the given parameters. This exception is also an attribute of the model class. 993Example:: 994 995 Entry.objects.get(id='foo') # raises Entry.DoesNotExist 996 997The ``DoesNotExist`` exception inherits from 998``django.core.exceptions.ObjectDoesNotExist``, so you can target multiple 999``DoesNotExist`` exceptions. Example:: 1000 1001 from django.core.exceptions import ObjectDoesNotExist 1002 try: 1003 e = Entry.objects.get(id=3) 1004 b = Blog.objects.get(id=1) 1005 except ObjectDoesNotExist: 1006 print "Either the entry or blog doesn't exist." 1007 1008create 1009~~~~~~ 1010 1011.. method:: create(**kwargs) 1012 1013A convenience method for creating an object and saving it all in one step. Thus:: 1014 1015 p = Person.objects.create(first_name="Bruce", last_name="Springsteen") 1016 1017and:: 1018 1019 p = Person(first_name="Bruce", last_name="Springsteen") 1020 p.save(force_insert=True) 1021 1022are equivalent. 1023 1024The :ref:`force_insert <ref-models-force-insert>` parameter is documented 1025elsewhere, but all it means is that a new object will always be created. 1026Normally you won't need to worry about this. However, if your model contains a 1027manual primary key value that you set and if that value already exists in the 1028database, a call to ``create()`` will fail with an 1029:exc:`~django.db.IntegrityError` since primary keys must be unique. So remember 1030to be prepared to handle the exception if you are using manual primary keys. 1031 1032get_or_create 1033~~~~~~~~~~~~~ 1034 1035.. method:: get_or_create(**kwargs) 1036 1037A convenience method for looking up an object with the given kwargs, creating 1038one if necessary. 1039 1040Returns a tuple of ``(object, created)``, where ``object`` is the retrieved or 1041created object and ``created`` is a boolean specifying whether a new object was 1042created. 1043 1044This is meant as a shortcut to boilerplatish code and is mostly useful for 1045data-import scripts. For example:: 1046 1047 try: 1048 obj = Person.objects.get(first_name='John', last_name='Lennon') 1049 except Person.DoesNotExist: 1050 obj = Person(first_name='John', last_name='Lennon', birthday=date(1940, 10, 9)) 1051 obj.save() 1052 1053This pattern gets quite unwieldy as the number of fields in a model goes up. 1054The above example can be rewritten using ``get_or_create()`` like so:: 1055 1056 obj, created = Person.objects.get_or_create(first_name='John', last_name='Lennon', 1057 defaults={'birthday': date(1940, 10, 9)}) 1058 1059Any keyword arguments passed to ``get_or_create()`` -- *except* an optional one 1060called ``defaults`` -- will be used in a ``get()`` call. If an object is found, 1061``get_or_create()`` returns a tuple of that object and ``False``. If an object 1062is *not* found, ``get_or_create()`` will instantiate and save a new object, 1063returning a tuple of the new object and ``True``. The new object will be 1064created roughly according to this algorithm:: 1065 1066 defaults = kwargs.pop('defaults', {}) 1067 params = dict([(k, v) for k, v in kwargs.items() if '__' not in k]) 1068 params.update(defaults) 1069 obj = self.model(**params) 1070 obj.save() 1071 1072In English, that means start with any non-``'defaults'`` keyword argument that 1073doesn't contain a double underscore (which would indicate a non-exact lookup). 1074Then add the contents of ``defaults``, overriding any keys if necessary, and 1075use the result as the keyword arguments to the model class. As hinted at 1076above, this is a simplification of the algorithm that is used, but it contains 1077all the pertinent details. The internal implementation has some more 1078error-checking than this and handles some extra edge-conditions; if you're 1079interested, read the code. 1080 1081If you have a field named ``defaults`` and want to use it as an exact lookup in 1082``get_or_create()``, just use ``'defaults__exact'``, like so:: 1083 1084 Foo.objects.get_or_create(defaults__exact='bar', defaults={'defaults': 'baz'}) 1085 1086 1087The ``get_or_create()`` method has similar error behavior to ``create()`` 1088when you are using manually specified primary keys. If an object needs to be 1089created and the key already exists in the database, an ``IntegrityError`` will 1090be raised. 1091 1092Finally, a word on using ``get_or_create()`` in Django views. As mentioned 1093earlier, ``get_or_create()`` is mostly useful in scripts that need to parse 1094data and create new records if existing ones aren't available. But if you need 1095to use ``get_or_create()`` in a view, please make sure to use it only in 1096``POST`` requests unless you have a good reason not to. ``GET`` requests 1097shouldn't have any effect on data; use ``POST`` whenever a request to a page 1098has a side effect on your data. For more, see `Safe methods`_ in the HTTP spec. 1099 1100.. _Safe methods: http://www.w3.org/Protocols/rfc2616/rfc2616-sec9.html#sec9.1.1 1101 1102count 1103~~~~~ 1104 1105.. method:: count() 1106 1107Returns an integer representing the number of objects in the database matching 1108the ``QuerySet``. ``count()`` never raises exceptions. 1109 1110Example:: 1111 1112 # Returns the total number of entries in the database. 1113 Entry.objects.count() 1114 1115 # Returns the number of entries whose headline contains 'Lennon' 1116 Entry.objects.filter(headline__contains='Lennon').count() 1117 1118``count()`` performs a ``SELECT COUNT(*)`` behind the scenes, so you should 1119always use ``count()`` rather than loading all of the record into Python 1120objects and calling ``len()`` on the result (unless you need to load the 1121objects into memory anyway, in which case ``len()`` will be faster). 1122 1123Depending on which database you're using (e.g. PostgreSQL vs. MySQL), 1124``count()`` may return a long integer instead of a normal Python integer. This 1125is an underlying implementation quirk that shouldn't pose any real-world 1126problems. 1127 1128in_bulk 1129~~~~~~~ 1130 1131.. method:: in_bulk(id_list) 1132 1133Takes a list of primary-key values and returns a dictionary mapping each 1134primary-key value to an instance of the object with the given ID. 1135 1136Example:: 1137 1138 >>> Blog.objects.in_bulk([1]) 1139 {1: <Blog: Beatles Blog>} 1140 >>> Blog.objects.in_bulk([1, 2]) 1141 {1: <Blog: Beatles Blog>, 2: <Blog: Cheddar Talk>} 1142 >>> Blog.objects.in_bulk([]) 1143 {} 1144 1145If you pass ``in_bulk()`` an empty list, you'll get an empty dictionary. 1146 1147iterator 1148~~~~~~~~ 1149 1150.. method:: iterator() 1151 1152Evaluates the ``QuerySet`` (by performing the query) and returns an 1153`iterator`_ over the results. A ``QuerySet`` typically caches its 1154results internally so that repeated evaluations do not result in 1155additional queries; ``iterator()`` will instead read results directly, 1156without doing any caching at the ``QuerySet`` level. For a 1157``QuerySet`` which returns a large number of objects, this often 1158results in better performance and a significant reduction in memory 1159 1160Note that using ``iterator()`` on a ``QuerySet`` which has already 1161been evaluated will force it to evaluate again, repeating the query. 1162 1163.. _iterator: http://www.python.org/dev/peps/pep-0234/ 1164 1165latest 1166~~~~~~ 1167 1168.. method:: latest(field_name=None) 1169 1170Returns the latest object in the table, by date, using the ``field_name`` 1171provided as the date field. 1172 1173This example returns the latest ``Entry`` in the table, according to the 1174``pub_date`` field:: 1175 1176 Entry.objects.latest('pub_date') 1177 1178If your model's ``Meta`` specifies ``get_latest_by``, you can leave off the 1179``field_name`` argument to ``latest()``. Django will use the field specified in 1180``get_latest_by`` by default. 1181 1182Like ``get()``, ``latest()`` raises ``DoesNotExist`` if an object doesn't 1183exist with the given parameters. 1184 1185Note ``latest()`` exists purely for convenience and readability. 1186 1187aggregate 1188~~~~~~~~~ 1189 1190.. method:: aggregate(*args, **kwargs) 1191 1192Returns a dictionary of aggregate values (averages, sums, etc) calculated 1193over the ``QuerySet``. Each argument to ``aggregate()`` specifies 1194a value that will be included in the dictionary that is returned. 1195 1196The aggregation functions that are provided by Django are described 1197in `Aggregation Functions`_ below. 1198 1199Aggregates specified using keyword arguments will use the keyword as 1200the name for the annotation. Anonymous arguments will have an name 1201generated for them based upon the name of the aggregate function and 1202the model field that is being aggregated. 1203 1204For example, if you were manipulating blog entries, you may want to know 1205the number of authors that have contributed blog entries:: 1206 1207 >>> q = Blog.objects.aggregate(Count('entry')) 1208 {'entry__count': 16} 1209 1210By using a keyword argument to specify the aggregate function, you can 1211control the name of the aggregation value that is returned:: 1212 1213 >>> q = Blog.objects.aggregate(number_of_entries=Count('entry')) 1214 {'number_of_entries': 16} 1215 1216For an in-depth discussion of aggregation, see :doc:`the topic guide on 1217Aggregation </topics/db/aggregation>`. 1218 1219exists 1220~~~~~~ 1221 1222.. method:: exists() 1223 1224.. versionadded:: 1.2 1225 1226Returns ``True`` if the :class:`.QuerySet` contains any results, and ``False`` 1227if not. This tries to perform the query in the simplest and fastest way 1228possible, but it *does* execute nearly the same query. This means that calling 1229:meth:`.QuerySet.exists` is faster than ``bool(some_query_set)``, but not by 1230a large degree. If ``some_query_set`` has not yet been evaluated, but you know 1231that it will be at some point, then using ``some_query_set.exists()`` will do 1232more overall work (an additional query) than simply using 1233``bool(some_query_set)``. 1234 1235update 1236~~~~~~ 1237 1238.. method:: update(**kwargs) 1239 1240Performs an SQL update query for the specified fields, and returns 1241the number of rows affected. The ``update()`` method is applied instantly and 1242the only restriction on the :class:`.QuerySet` that is updated is that it can 1243only update columns in the model's main table. Filtering based on related 1244fields is still possible. You cannot call ``update()`` on a 1245:class:`.QuerySet` that has had a slice taken or can otherwise no longer be 1246filtered. 1247 1248For example, if you wanted to update all the entries in a particular blog 1249to use the same headline:: 1250 1251 >>> b = Blog.objects.get(pk=1) 1252 1253 # Update all the headlines belonging to this Blog. 1254 >>> Entry.objects.select_related().filter(blog=b).update(headline='Everything is the same') 1255 1256The ``update()`` method does a bulk update and does not call any ``save()`` 1257methods on your models, nor does it emit the ``pre_save`` or ``post_save`` 1258signals (which are a consequence of calling ``save()``). 1259 1260delete 1261~~~~~~ 1262 1263.. method:: delete() 1264 1265Performs an SQL delete query on all rows in the :class:`.QuerySet`. The 1266``delete()`` is applied instantly. You cannot call ``delete()`` on a 1267:class:`.QuerySet` that has had a slice taken or can otherwise no longer be 1268filtered. 1269 1270For example, to delete all the entries in a particular blog:: 1271 1272 >>> b = Blog.objects.get(pk=1) 1273 1274 # Delete all the entries belonging to this Blog. 1275 >…
Large files files are truncated, but you can click here to view the full file