1 ======================

     2 QuerySet API reference

     3 ======================

     4 

     5 .. currentmodule:: django.db.models.QuerySet

     6 

     7 This document describes the details of the ``QuerySet`` API. It builds on the

     8 material presented in the :doc:`model </topics/db/models>` and :doc:`database

     9 query </topics/db/queries>` guides, so you'll probably want to read and

    10 understand those documents before reading this one.

    11 

    12 Throughout this reference we'll use the :ref:`example Weblog models

    13 <queryset-model-example>` presented in the :doc:`database query guide

    14 </topics/db/queries>`.

    15 

    16 .. _when-querysets-are-evaluated:

    17 

    18 When QuerySets are evaluated

    19 ============================

    20 

    21 Internally, a ``QuerySet`` can be constructed, filtered, sliced, and generally

    22 passed around without actually hitting the database. No database activity

    23 actually occurs until you do something to evaluate the queryset.

    24 

    25 You can evaluate a ``QuerySet`` in the following ways:

    26 

    27     * **Iteration.** A ``QuerySet`` is iterable, and it executes its database

    28       query the first time you iterate over it. For example, this will print

    29       the headline of all entries in the database::

    30 

    31           for e in Entry.objects.all():

    32               print e.headline

    33 

    34     * **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 

    81 Pickling QuerySets

    82 ------------------

    83 

    84 If you pickle_ a ``QuerySet``, this will force all the results to be loaded

    85 into memory prior to pickling. Pickling is usually used as a precursor to

    86 caching and when the cached queryset is reloaded, you want the results to

    87 already be present and ready for use (reading from the database can take some

    88 time, 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

    90 than the results that are currently in the database.

    91 

    92 If you only want to pickle the necessary information to recreate the

    93 ``QuerySet`` from the database at a later time, pickle the ``query`` attribute

    94 of the ``QuerySet``. You can then recreate the original ``QuerySet`` (without

    95 any 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 

   102 The ``query`` attribute is an opaque object. It represents the internals of

   103 the 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

   105 described 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 

   119 QuerySet API

   120 ============

   121 

   122 Though you usually won't create one manually -- you'll go through a

   123 :class:`Manager` -- here's the formal declaration of a ``QuerySet``:

   124 

   125 .. class:: QuerySet([model=None])

   126 

   127 Usually when you'll interact with a ``QuerySet`` you'll use it by :ref:`chaining

   128 filters <chaining-filters>`. To make this work, most ``QuerySet`` methods return new querysets.

   129 

   130 Methods that return new QuerySets

   131 ---------------------------------

   132 

   133 Django provides a range of ``QuerySet`` refinement methods that modify either

   134 the types of results returned by the ``QuerySet`` or the way its SQL query is

   135 executed.

   136 

   137 filter

   138 ~~~~~~

   139 

   140 .. method:: filter(**kwargs)

   141 

   142 Returns a new ``QuerySet`` containing objects that match the given lookup

   143 parameters.

   144 

   145 The lookup parameters (``**kwargs``) should be in the format described in

   146 `Field lookups`_ below. Multiple parameters are joined via ``AND`` in the

   147 underlying SQL statement.

   148 

   149 exclude

   150 ~~~~~~~

   151 

   152 .. method:: exclude(**kwargs)

   153 

   154 Returns a new ``QuerySet`` containing objects that do *not* match the given

   155 lookup parameters.

   156 

   157 The lookup parameters (``**kwargs``) should be in the format described in

   158 `Field lookups`_ below. Multiple parameters are joined via ``AND`` in the

   159 underlying SQL statement, and the whole thing is enclosed in a ``NOT()``.

   160 

   161 This example excludes all entries whose ``pub_date`` is later than 2005-1-3

   162 AND whose ``headline`` is "Hello"::

   163 

   164     Entry.objects.exclude(pub_date__gt=datetime.date(2005, 1, 3), headline='Hello')

   165 

   166 In SQL terms, that evaluates to::

   167 

   168     SELECT ...

   169     WHERE NOT (pub_date > '2005-1-3' AND headline = 'Hello')

   170 

   171 This example excludes all entries whose ``pub_date`` is later than 2005-1-3

   172 OR whose headline is "Hello"::

   173 

   174     Entry.objects.exclude(pub_date__gt=datetime.date(2005, 1, 3)).exclude(headline='Hello')

   175 

   176 In SQL terms, that evaluates to::

   177 

   178     SELECT ...

   179     WHERE NOT pub_date > '2005-1-3'

   180     AND NOT headline = 'Hello'

   181 

   182 Note the second example is more restrictive.

   183 

   184 annotate

   185 ~~~~~~~~

   186 

   187 .. method:: annotate(*args, **kwargs)

   188 

   189 .. versionadded:: 1.1

   190 

   191 Annotates each object in the ``QuerySet`` with the provided list of

   192 aggregate values (averages, sums, etc) that have been computed over

   193 the objects that are related to the objects in the ``QuerySet``.

   194 Each argument to ``annotate()`` is an annotation that will be added

   195 to each object in the ``QuerySet`` that is returned.

   196 

   197 The aggregation functions that are provided by Django are described

   198 in `Aggregation Functions`_ below.

   199 

   200 Annotations specified using keyword arguments will use the keyword as

   201 the alias for the annotation. Anonymous arguments will have an alias

   202 generated for them based upon the name of the aggregate function and

   203 the model field that is being aggregated.

   204 

   205 For example, if you were manipulating a list of blogs, you may want

   206 to determine how many entries have been made in each blog::

   207 

   208     >>> q = Blog.objects.annotate(Count('entry'))

   209     # The name of the first blog

   210     >>> q[0].name

   211     'Blogasaurus'

   212     # The number of entries on the first blog

   213     >>> q[0].entry__count

   214     42

   215 

   216 The ``Blog`` model doesn't define an ``entry__count`` attribute by itself,

   217 but by using a keyword argument to specify the aggregate function, you can

   218 control the name of the annotation::

   219 

   220     >>> q = Blog.objects.annotate(number_of_entries=Count('entry'))

   221     # The number of entries on the first blog, using the name provided

   222     >>> q[0].number_of_entries

   223     42

   224 

   225 For an in-depth discussion of aggregation, see :doc:`the topic guide on

   226 Aggregation </topics/db/aggregation>`.

   227 

   228 order_by

   229 ~~~~~~~~

   230 

   231 .. method:: order_by(*fields)

   232 

   233 By default, results returned by a ``QuerySet`` are ordered by the ordering

   234 tuple given by the ``ordering`` option in the model's ``Meta``. You can

   235 override this on a per-``QuerySet`` basis by using the ``order_by`` method.

   236 

   237 Example::

   238 

   239     Entry.objects.filter(pub_date__year=2005).order_by('-pub_date', 'headline')

   240 

   241 The result above will be ordered by ``pub_date`` descending, then by

   242 ``headline`` ascending. The negative sign in front of ``"-pub_date"`` indicates

   243 *descending* order. Ascending order is implied. To order randomly, use ``"?"``,

   244 like so::

   245 

   246     Entry.objects.order_by('?')

   247 

   248 Note: ``order_by('?')`` queries may be expensive and slow, depending on the

   249 database backend you're using.

   250 

   251 To order by a field in a different model, use the same syntax as when you are

   252 querying across model relations. That is, the name of the field, followed by a

   253 double underscore (``__``), followed by the name of the field in the new model,

   254 and so on for as many models as you want to join. For example::

   255 

   256     Entry.objects.order_by('blog__name', 'headline')

   257 

   258 If you try to order by a field that is a relation to another model, Django will

   259 use the default ordering on the related model (or order by the related model's

   260 primary key if there is no ``Meta.ordering`` specified. For example::

   261 

   262     Entry.objects.order_by('blog')

   263 

   264 ...is identical to::

   265 

   266     Entry.objects.order_by('blog__id')

   267 

   268 ...since the ``Blog`` model has no default ordering specified.

   269 

   270 Be cautious when ordering by fields in related models if you are also using

   271 ``distinct()``. See the note in :meth:`distinct` for an explanation of how

   272 related model ordering can change the expected results.

   273 

   274 It is permissible to specify a multi-valued field to order the results by (for

   275 example, a ``ManyToMany`` field). Normally this won't be a sensible thing to

   276 do and it's really an advanced usage feature. However, if you know that your

   277 queryset's filtering or available data implies that there will only be one

   278 ordering piece of data for each of the main items you are selecting, the

   279 ordering may well be exactly what you want to do. Use ordering on multi-valued

   280 fields with care and make sure the results are what you expect.

   281 

   282 .. versionadded:: 1.0

   283 

   284 The syntax for ordering across related models has changed. See the `Django 0.96

   285 documentation`_ for the old behaviour.

   286 

   287 .. _Django 0.96 documentation: http://www.djangoproject.com/documentation/0.96/model-api/#floatfield

   288 

   289 There's no way to specify whether ordering should be case sensitive. With

   290 respect to case-sensitivity, Django will order results however your database

   291 backend normally orders them.

   292 

   293 If you don't want any ordering to be applied to a query, not even the default

   294 ordering, call ``order_by()`` with no parameters.

   295 

   296 .. versionadded:: 1.1

   297 

   298 You can tell if a query is ordered or not by checking the

   299 :attr:`QuerySet.ordered` attribute, which will be ``True`` if the

   300 ``QuerySet`` has been ordered in any way.

   301 

   302 reverse

   303 ~~~~~~~

   304 

   305 .. method:: reverse()

   306 

   307 .. versionadded:: 1.0

   308 

   309 Use the ``reverse()`` method to reverse the order in which a queryset's

   310 elements are returned. Calling ``reverse()`` a second time restores the

   311 ordering back to the normal direction.

   312 

   313 To retrieve the ''last'' five items in a queryset, you could do this::

   314 

   315     my_queryset.reverse()[:5]

   316 

   317 Note that this is not quite the same as slicing from the end of a sequence in

   318 Python. The above example will return the last item first, then the

   319 penultimate item and so on. If we had a Python sequence and looked at

   320 ``seq[-5:]``, we would see the fifth-last item first. Django doesn't support

   321 that mode of access (slicing from the end), because it's not possible to do it

   322 efficiently in SQL.

   323 

   324 Also, note that ``reverse()`` should generally only be called on a

   325 ``QuerySet`` which has a defined ordering (e.g., when querying against

   326 a model which defines a default ordering, or when using

   327 ``order_by()``). If no such ordering is defined for a given

   328 ``QuerySet``, calling ``reverse()`` on it has no real effect (the

   329 ordering was undefined prior to calling ``reverse()``, and will remain

   330 undefined afterward).

   331 

   332 distinct

   333 ~~~~~~~~

   334 

   335 .. method:: distinct()

   336 

   337 Returns a new ``QuerySet`` that uses ``SELECT DISTINCT`` in its SQL query. This

   338 eliminates duplicate rows from the query results.

   339 

   340 By default, a ``QuerySet`` will not eliminate duplicate rows. In practice, this

   341 is rarely a problem, because simple queries such as ``Blog.objects.all()``

   342 don't introduce the possibility of duplicate result rows. However, if your

   343 query spans multiple tables, it's possible to get duplicate results when a

   344 ``QuerySet`` is evaluated. That's when you'd use ``distinct()``.

   345 

   346 .. note::

   347     Any fields used in an :meth:`order_by` call are included in the SQL

   348     ``SELECT`` columns. This can sometimes lead to unexpected results when

   349     used in conjunction with ``distinct()``. If you order by fields from a

   350     related model, those fields will be added to the selected columns and they

   351     may make otherwise duplicate rows appear to be distinct. Since the extra

   352     columns don't appear in the returned results (they are only there to

   353     support ordering), it sometimes looks like non-distinct results are being

   354     returned.

   355 

   356     Similarly, if you use a ``values()`` query to restrict the columns

   357     selected, the columns used in any ``order_by()`` (or default model

   358     ordering) will still be involved and may affect uniqueness of the results.

   359 

   360     The moral here is that if you are using ``distinct()`` be careful about

   361     ordering by related models. Similarly, when using ``distinct()`` and

   362     ``values()`` together, be careful when ordering by fields not in the

   363     ``values()`` call.

   364 

   365 values

   366 ~~~~~~

   367 

   368 .. method:: values(*fields)

   369 

   370 Returns a ``ValuesQuerySet`` -- a ``QuerySet`` that returns dictionaries when

   371 used as an iterable, rather than model-instance objects.

   372 

   373 Each of those dictionaries represents an object, with the keys corresponding to

   374 the attribute names of model objects.

   375 

   376 This example compares the dictionaries of ``values()`` with the normal model

   377 objects::

   378 

   379     # This list contains a Blog object.

   380     >>> Blog.objects.filter(name__startswith='Beatles')

   381     [<Blog: Beatles Blog>]

   382 

   383     # This list contains a dictionary.

   384     >>> Blog.objects.filter(name__startswith='Beatles').values()

   385     [{'id': 1, 'name': 'Beatles Blog', 'tagline': 'All the latest Beatles news.'}]

   386 

   387 ``values()`` takes optional positional arguments, ``*fields``, which specify

   388 field names to which the ``SELECT`` should be limited. If you specify the

   389 fields, each dictionary will contain only the field keys/values for the fields

   390 you specify. If you don't specify the fields, each dictionary will contain a

   391 key and value for every field in the database table.

   392 

   393 Example::

   394 

   395     >>> Blog.objects.values()

   396     [{'id': 1, 'name': 'Beatles Blog', 'tagline': 'All the latest Beatles news.'}],

   397     >>> Blog.objects.values('id', 'name')

   398     [{'id': 1, 'name': 'Beatles Blog'}]

   399 

   400 A couple of subtleties that are worth mentioning:

   401 

   402     * The ``values()`` method does not return anything for

   403       :class:`~django.db.models.ManyToManyField` attributes and will raise an

   404       error if you try to pass in this type of field to it.

   405     * If you have a field called ``foo`` that is a

   406       :class:`~django.db.models.ForeignKey`, the default ``values()`` call

   407       will return a dictionary key called ``foo_id``, since this is the name

   408       of the hidden model attribute that stores the actual value (the ``foo``

   409       attribute refers to the related model). When you are calling

   410       ``values()`` and passing in field names, you can pass in either ``foo``

   411       or ``foo_id`` and you will get back the same thing (the dictionary key

   412       will match the field name you passed in).

   413 

   414       For example::

   415 

   416         >>> Entry.objects.values()

   417         [{'blog_id': 1, 'headline': u'First Entry', ...}, ...]

   418 

   419         >>> Entry.objects.values('blog')

   420         [{'blog': 1}, ...]

   421 

   422         >>> Entry.objects.values('blog_id')

   423         [{'blog_id': 1}, ...]

   424 

   425     * When using ``values()`` together with ``distinct()``, be aware that

   426       ordering can affect the results. See the note in :meth:`distinct` for

   427       details.

   428 

   429     * If you use a ``values()`` clause after an ``extra()`` clause,

   430       any fields defined by a ``select`` argument in the ``extra()``

   431       must be explicitly included in the ``values()`` clause. However,

   432       if the ``extra()`` clause is used after the ``values()``, the

   433       fields added by the select will be included automatically.

   434 

   435 .. versionadded:: 1.0

   436 

   437 Previously, it was not possible to pass ``blog_id`` to ``values()`` in the above

   438 example, only ``blog``.

   439 

   440 A ``ValuesQuerySet`` is useful when you know you're only going to need values

   441 from a small number of the available fields and you won't need the

   442 functionality of a model instance object. It's more efficient to select only

   443 the fields you need to use.

   444 

   445 Finally, note a ``ValuesQuerySet`` is a subclass of ``QuerySet``, so it has all

   446 methods of ``QuerySet``. You can call ``filter()`` on it, or ``order_by()``, or

   447 whatever. Yes, that means these two calls are identical::

   448 

   449     Blog.objects.values().order_by('id')

   450     Blog.objects.order_by('id').values()

   451 

   452 The people who made Django prefer to put all the SQL-affecting methods first,

   453 followed (optionally) by any output-affecting methods (such as ``values()``),

   454 but it doesn't really matter. This is your chance to really flaunt your

   455 individualism.

   456 

   457 values_list

   458 ~~~~~~~~~~~

   459 

   460 .. method:: values_list(*fields)

   461 

   462 .. versionadded:: 1.0

   463 

   464 This is similar to ``values()`` except that instead of returning dictionaries,

   465 it returns tuples when iterated over. Each tuple contains the value from the

   466 respective field passed into the ``values_list()`` call -- so the first item is

   467 the first field, etc. For example::

   468 

   469     >>> Entry.objects.values_list('id', 'headline')

   470     [(1, u'First entry'), ...]

   471 

   472 If you only pass in a single field, you can also pass in the ``flat``

   473 parameter. If ``True``, this will mean the returned results are single values,

   474 rather than one-tuples. An example should make the difference clearer::

   475 

   476     >>> Entry.objects.values_list('id').order_by('id')

   477     [(1,), (2,), (3,), ...]

   478 

   479     >>> Entry.objects.values_list('id', flat=True).order_by('id')

   480     [1, 2, 3, ...]

   481 

   482 It is an error to pass in ``flat`` when there is more than one field.

   483 

   484 If you don't pass any values to ``values_list()``, it will return all the

   485 fields in the model, in the order they were declared.

   486 

   487 dates

   488 ~~~~~

   489 

   490 .. method:: dates(field, kind, order='ASC')

   491 

   492 Returns a ``DateQuerySet`` -- a ``QuerySet`` that evaluates to a list of

   493 ``datetime.datetime`` objects representing all available dates of a particular

   494 kind within the contents of the ``QuerySet``.

   495 

   496 ``field`` should be the name of a ``DateField`` or ``DateTimeField`` of your

   497 model.

   498 

   499 ``kind`` should be either ``"year"``, ``"month"`` or ``"day"``. Each

   500 ``datetime.datetime`` object in the result list is "truncated" to the given

   501 ``type``.

   502 

   503     * ``"year"`` returns a list of all distinct year values for the field.

   504     * ``"month"`` returns a list of all distinct year/month values for the field.

   505     * ``"day"`` returns a list of all distinct year/month/day values for the field.

   506 

   507 ``order``, which defaults to ``'ASC'``, should be either ``'ASC'`` or

   508 ``'DESC'``. This specifies how to order the results.

   509 

   510 Examples::

   511 

   512     >>> Entry.objects.dates('pub_date', 'year')

   513     [datetime.datetime(2005, 1, 1)]

   514     >>> Entry.objects.dates('pub_date', 'month')

   515     [datetime.datetime(2005, 2, 1), datetime.datetime(2005, 3, 1)]

   516     >>> Entry.objects.dates('pub_date', 'day')

   517     [datetime.datetime(2005, 2, 20), datetime.datetime(2005, 3, 20)]

   518     >>> Entry.objects.dates('pub_date', 'day', order='DESC')

   519     [datetime.datetime(2005, 3, 20), datetime.datetime(2005, 2, 20)]

   520     >>> Entry.objects.filter(headline__contains='Lennon').dates('pub_date', 'day')

   521     [datetime.datetime(2005, 3, 20)]

   522 

   523 none

   524 ~~~~

   525 

   526 .. method:: none()

   527 

   528 .. versionadded:: 1.0

   529 

   530 Returns an ``EmptyQuerySet`` -- a ``QuerySet`` that always evaluates to

   531 an empty list. This can be used in cases where you know that you should

   532 return an empty result set and your caller is expecting a ``QuerySet``

   533 object (instead of returning an empty list, for example.)

   534 

   535 Examples::

   536 

   537     >>> Entry.objects.none()

   538     []

   539 

   540 all

   541 ~~~

   542 

   543 .. method:: all()

   544 

   545 .. versionadded:: 1.0

   546 

   547 Returns a *copy* of the current ``QuerySet`` (or ``QuerySet`` subclass you

   548 pass in). This can be useful in some situations where you might want to pass

   549 in either a model manager or a ``QuerySet`` and do further filtering on the

   550 result. You can safely call ``all()`` on either object and then you'll

   551 definitely have a ``QuerySet`` to work with.

   552 

   553 .. _select-related:

   554 

   555 select_related

   556 ~~~~~~~~~~~~~~

   557 

   558 .. method:: select_related()

   559 

   560 Returns a ``QuerySet`` that will automatically "follow" foreign-key

   561 relationships, selecting that additional related-object data when it executes

   562 its query. This is a performance booster which results in (sometimes much)

   563 larger queries but means later use of foreign-key relationships won't require

   564 database queries.

   565 

   566 The following examples illustrate the difference between plain lookups and

   567 ``select_related()`` lookups. Here's standard lookup::

   568 

   569     # Hits the database.

   570     e = Entry.objects.get(id=5)

   571 

   572     # Hits the database again to get the related Blog object.

   573     b = e.blog

   574 

   575 And here's ``select_related`` lookup::

   576 

   577     # Hits the database.

   578     e = Entry.objects.select_related().get(id=5)

   579 

   580     # Doesn't hit the database, because e.blog has been prepopulated

   581     # in the previous query.

   582     b = e.blog

   583 

   584 ``select_related()`` follows foreign keys as far as possible. If you have the

   585 following models::

   586 

   587     class City(models.Model):

   588         # ...

   589 

   590     class Person(models.Model):

   591         # ...

   592         hometown = models.ForeignKey(City)

   593 

   594     class Book(models.Model):

   595         # ...

   596         author = models.ForeignKey(Person)

   597 

   598 ...then a call to ``Book.objects.select_related().get(id=4)`` will cache the

   599 related ``Person`` *and* the related ``City``::

   600 

   601     b = Book.objects.select_related().get(id=4)

   602     p = b.author         # Doesn't hit the database.

   603     c = p.hometown       # Doesn't hit the database.

   604 

   605     b = Book.objects.get(id=4) # No select_related() in this example.

   606     p = b.author         # Hits the database.

   607     c = p.hometown       # Hits the database.

   608 

   609 Note that, by default, ``select_related()`` does not follow foreign keys that

   610 have ``null=True``.

   611 

   612 Usually, using ``select_related()`` can vastly improve performance because your

   613 app can avoid many database calls. However, in situations with deeply nested

   614 sets of relationships ``select_related()`` can sometimes end up following "too

   615 many" relations, and can generate queries so large that they end up being slow.

   616 

   617 In these situations, you can use the ``depth`` argument to ``select_related()``

   618 to control how many "levels" of relations ``select_related()`` will actually

   619 follow::

   620 

   621     b = Book.objects.select_related(depth=1).get(id=4)

   622     p = b.author         # Doesn't hit the database.

   623     c = p.hometown       # Requires a database call.

   624 

   625 Sometimes you only want to access specific models that are related to your root

   626 model, not all of the related models. In these cases, you can pass the related

   627 field names to ``select_related()`` and it will only follow those relations.

   628 You can even do this for models that are more than one relation away by

   629 separating the field names with double underscores, just as for filters. For

   630 example, if you have this model::

   631 

   632     class Room(models.Model):

   633         # ...

   634         building = models.ForeignKey(...)

   635 

   636     class Group(models.Model):

   637         # ...

   638         teacher = models.ForeignKey(...)

   639         room = models.ForeignKey(Room)

   640         subject = models.ForeignKey(...)

   641 

   642 ...and you only needed to work with the ``room`` and ``subject`` attributes,

   643 you could write this::

   644 

   645     g = Group.objects.select_related('room', 'subject')

   646 

   647 This is also valid::

   648 

   649     g = Group.objects.select_related('room__building', 'subject')

   650 

   651 ...and would also pull in the ``building`` relation.

   652 

   653 You can refer to any ``ForeignKey`` or ``OneToOneField`` relation in

   654 the list of fields passed to ``select_related``. Ths includes foreign

   655 keys that have ``null=True`` (unlike the default ``select_related()``

   656 call). It's an error to use both a list of fields and the ``depth``

   657 parameter in the same ``select_related()`` call, since they are

   658 conflicting options.

   659 

   660 .. versionadded:: 1.0

   661 

   662 Both the ``depth`` argument and the ability to specify field names in the call

   663 to ``select_related()`` are new in Django version 1.0.

   664 

   665 .. versionchanged:: 1.2

   666 

   667 You can also refer to the reverse direction of a ``OneToOneFields`` in

   668 the list of fields passed to ``select_related`` -- that is, you can traverse

   669 a ``OneToOneField`` back to the object on which the field is defined. Instead

   670 of specifying the field name, use the ``related_name`` for the field on the

   671 related object.

   672 

   673 ``OneToOneFields`` will not be traversed in the reverse direction if you

   674 are performing a depth-based ``select_related``.

   675 

   676 extra

   677 ~~~~~

   678 

   679 .. method:: extra(select=None, where=None, params=None, tables=None, order_by=None, select_params=None)

   680 

   681 Sometimes, the Django query syntax by itself can't easily express a complex

   682 ``WHERE`` clause. For these edge cases, Django provides the ``extra()``

   683 ``QuerySet`` modifier -- a hook for injecting specific clauses into the SQL

   684 generated by a ``QuerySet``.

   685 

   686 By definition, these extra lookups may not be portable to different database

   687 engines (because you're explicitly writing SQL code) and violate the DRY

   688 principle, so you should avoid them if possible.

   689 

   690 Specify one or more of ``params``, ``select``, ``where`` or ``tables``. None

   691 of the arguments is required, but you should use at least one of them.

   692 

   693     * ``select``

   694         The ``select`` argument lets you put extra fields in the ``SELECT`` clause.

   695         It should be a dictionary mapping attribute names to SQL clauses to use to

   696         calculate that attribute.

   697 

   698         Example::

   699 

   700             Entry.objects.extra(select={'is_recent': "pub_date > '2006-01-01'"})

   701 

   702         As a result, each ``Entry`` object will have an extra attribute,

   703         ``is_recent``, a boolean representing whether the entry's ``pub_date`` is

   704         greater than Jan. 1, 2006.

   705 

   706         Django inserts the given SQL snippet directly into the ``SELECT``

   707         statement, so the resulting SQL of the above example would be something

   708         like::

   709 

   710             SELECT blog_entry.*, (pub_date > '2006-01-01') AS is_recent

   711             FROM blog_entry;

   712 

   713 

   714         The next example is more advanced; it does a subquery to give each

   715         resulting ``Blog`` object an ``entry_count`` attribute, an integer count

   716         of associated ``Entry`` objects::

   717 

   718             Blog.objects.extra(

   719                 select={

   720                     'entry_count': 'SELECT COUNT(*) FROM blog_entry WHERE blog_entry.blog_id = blog_blog.id'

   721                 },

   722             )

   723 

   724         (In this particular case, we're exploiting the fact that the query will

   725         already contain the ``blog_blog`` table in its ``FROM`` clause.)

   726 

   727         The resulting SQL of the above example would be::

   728 

   729             SELECT blog_blog.*, (SELECT COUNT(*) FROM blog_entry WHERE blog_entry.blog_id = blog_blog.id) AS entry_count

   730             FROM blog_blog;

   731 

   732         Note that the parenthesis required by most database engines around

   733         subqueries are not required in Django's ``select`` clauses. Also note that

   734         some database backends, such as some MySQL versions, don't support

   735         subqueries.

   736 

   737         .. versionadded:: 1.0

   738 

   739         In some rare cases, you might wish to pass parameters to the SQL fragments

   740         in ``extra(select=...)``. For this purpose, use the ``select_params``

   741         parameter. Since ``select_params`` is a sequence and the ``select``

   742         attribute is a dictionary, some care is required so that the parameters

   743         are matched up correctly with the extra select pieces.  In this situation,

   744         you should use a ``django.utils.datastructures.SortedDict`` for the

   745         ``select`` value, not just a normal Python dictionary.

   746 

   747         This will work, for example::

   748 

   749             Blog.objects.extra(

   750                 select=SortedDict([('a', '%s'), ('b', '%s')]),

   751                 select_params=('one', 'two'))

   752 

   753         The only thing to be careful about when using select parameters in

   754         ``extra()`` is to avoid using the substring ``"%%s"`` (that's *two*

   755         percent characters before the ``s``) in the select strings. Django's

   756         tracking of parameters looks for ``%s`` and an escaped ``%`` character

   757         like this isn't detected. That will lead to incorrect results.

   758 

   759     * ``where`` / ``tables``

   760         You can define explicit SQL ``WHERE`` clauses -- perhaps to perform

   761         non-explicit joins -- by using ``where``. You can manually add tables to

   762         the SQL ``FROM`` clause by using ``tables``.

   763 

   764         ``where`` and ``tables`` both take a list of strings. All ``where``

   765         parameters are "AND"ed to any other search criteria.

   766 

   767         Example::

   768 

   769             Entry.objects.extra(where=['id IN (3, 4, 5, 20)'])

   770 

   771         ...translates (roughly) into the following SQL::

   772 

   773             SELECT * FROM blog_entry WHERE id IN (3, 4, 5, 20);

   774 

   775         Be careful when using the ``tables`` parameter if you're specifying

   776         tables that are already used in the query. When you add extra tables

   777         via the ``tables`` parameter, Django assumes you want that table included

   778         an extra time, if it is already included. That creates a problem,

   779         since the table name will then be given an alias. If a table appears

   780         multiple times in an SQL statement, the second and subsequent occurrences

   781         must use aliases so the database can tell them apart. If you're

   782         referring to the extra table you added in the extra ``where`` parameter

   783         this is going to cause errors.

   784 

   785         Normally you'll only be adding extra tables that don't already appear in

   786         the query. However, if the case outlined above does occur, there are a few

   787         solutions. First, see if you can get by without including the extra table

   788         and use the one already in the query. If that isn't possible, put your

   789         ``extra()`` call at the front of the queryset construction so that your

   790         table is the first use of that table. Finally, if all else fails, look at

   791         the query produced and rewrite your ``where`` addition to use the alias

   792         given to your extra table. The alias will be the same each time you

   793         construct the queryset in the same way, so you can rely upon the alias

   794         name to not change.

   795 

   796     * ``order_by``

   797         If you need to order the resulting queryset using some of the new fields

   798         or tables you have included via ``extra()`` use the ``order_by`` parameter

   799         to ``extra()`` and pass in a sequence of strings. These strings should

   800         either be model fields (as in the normal ``order_by()`` method on

   801         querysets), of the form ``table_name.column_name`` or an alias for a column

   802         that you specified in the ``select`` parameter to ``extra()``.

   803 

   804         For example::

   805 

   806             q = Entry.objects.extra(select={'is_recent': "pub_date > '2006-01-01'"})

   807             q = q.extra(order_by = ['-is_recent'])

   808 

   809         This would sort all the items for which ``is_recent`` is true to the front

   810         of the result set (``True`` sorts before ``False`` in a descending

   811         ordering).

   812 

   813         This shows, by the way, that you can make multiple calls to

   814         ``extra()`` and it will behave as you expect (adding new constraints each

   815         time).

   816 

   817     * ``params``

   818         The ``where`` parameter described above may use standard Python database

   819         string placeholders -- ``'%s'`` to indicate parameters the database engine

   820         should automatically quote. The ``params`` argument is a list of any extra

   821         parameters to be substituted.

   822 

   823         Example::

   824 

   825             Entry.objects.extra(where=['headline=%s'], params=['Lennon'])

   826 

   827         Always use ``params`` instead of embedding values directly into ``where``

   828         because ``params`` will ensure values are quoted correctly according to

   829         your particular backend. (For example, quotes will be escaped correctly.)

   830 

   831         Bad::

   832 

   833             Entry.objects.extra(where=["headline='Lennon'"])

   834 

   835         Good::

   836 

   837             Entry.objects.extra(where=['headline=%s'], params=['Lennon'])

   838 

   839 defer

   840 ~~~~~

   841 

   842 .. method:: defer(*fields)

   843 

   844 .. versionadded:: 1.1

   845 

   846 In some complex data-modeling situations, your models might contain a lot of

   847 fields, some of which could contain a lot of data (for example, text fields),

   848 or require expensive processing to convert them to Python objects. If you are

   849 using the results of a queryset in some situation where you know you don't

   850 need those particular fields, you can tell Django not to retrieve them from

   851 the database.

   852 

   853 This is done by passing the names of the fields to not load to ``defer()``::

   854 

   855     Entry.objects.defer("headline", "body")

   856 

   857 A queryset that has deferred fields will still return model instances. Each

   858 deferred field will be retrieved from the database if you access that field

   859 (one at a time, not all the deferred fields at once).

   860 

   861 You can make multiple calls to ``defer()``. Each call adds new fields to the

   862 deferred set::

   863 

   864     # Defers both the body and headline fields.

   865     Entry.objects.defer("body").filter(rating=5).defer("headline")

   866 

   867 The order in which fields are added to the deferred set does not matter.

   868 Calling ``defer()`` with a field name that has already been deferred is

   869 harmless (the field will still be deferred).

   870 

   871 You can defer loading of fields in related models (if the related models are

   872 loading via ``select_related()``) by using the standard double-underscore

   873 notation to separate related fields::

   874 

   875     Blog.objects.select_related().defer("entry__headline", "entry__body")

   876 

   877 If you want to clear the set of deferred fields, pass ``None`` as a parameter

   878 to ``defer()``::

   879 

   880     # Load all fields immediately.

   881     my_queryset.defer(None)

   882 

   883 Some fields in a model won't be deferred, even if you ask for them. You can

   884 never defer the loading of the primary key. If you are using

   885 ``select_related()`` to retrieve other models at the same time you shouldn't

   886 defer the loading of the field that connects from the primary model to the

   887 related one (at the moment, that doesn't raise an error, but it will

   888 eventually).

   889 

   890 .. note::

   891 

   892     The ``defer()`` method (and its cousin, ``only()``, below) are only for

   893     advanced use-cases. They provide an optimization for when you have

   894     analyzed your queries closely and understand *exactly* what information

   895     you need and have measured that the difference between returning the

   896     fields you need and the full set of fields for the model will be

   897     significant. When you are initially developing your applications, don't

   898     bother using ``defer()``; leave it until your query construction has

   899     settled down and you understand where the hot-points are.

   900 

   901 only

   902 ~~~~

   903 

   904 .. method:: only(*fields)

   905 

   906 .. versionadded:: 1.1

   907 

   908 The ``only()`` method is more or less the opposite of ``defer()``. You

   909 call it with the fields that should *not* be deferred when retrieving a model.

   910 If you have a model where almost all the fields need to be deferred, using

   911 ``only()`` to specify the complementary set of fields could result in simpler

   912 code.

   913 

   914 If you have a model with fields ``name``, ``age`` and ``biography``, the

   915 following two querysets are the same, in terms of deferred fields::

   916 

   917     Person.objects.defer("age", "biography")

   918     Person.objects.only("name")

   919 

   920 Whenever you call ``only()`` it *replaces* the set of fields to load

   921 immediately. The method's name is mnemonic: **only** those fields are loaded

   922 immediately; the remainder are deferred. Thus, successive calls to ``only()``

   923 result in only the final fields being considered::

   924 

   925     # This will defer all fields except the headline.

   926     Entry.objects.only("body", "rating").only("headline")

   927 

   928 Since ``defer()`` acts incrementally (adding fields to the deferred list), you

   929 can combine calls to ``only()`` and ``defer()`` and things will behave

   930 logically::

   931 

   932     # Final result is that everything except "headline" is deferred.

   933     Entry.objects.only("headline", "body").defer("body")

   934 

   935     # Final result loads headline and body immediately (only() replaces any

   936     # existing set of fields).

   937     Entry.objects.defer("body").only("headline", "body")

   938 

   939 using

   940 ~~~~~

   941 

   942 .. method:: using(alias)

   943 

   944 .. versionadded:: 1.2

   945 

   946 This method is for controlling which database the ``QuerySet`` will be

   947 evaluated against if you are using more than one database.  The only argument

   948 this method takes is the alias of a database, as defined in

   949 :setting:`DATABASES`.

   950 

   951 For example::

   952 

   953     # queries the database with the 'default' alias.

   954     >>> Entry.objects.all()

   955 

   956     # queries the database with the 'backup' alias

   957     >>> Entry.objects.using('backup')

   958 

   959 

   960 Methods that do not return QuerySets

   961 ------------------------------------

   962 

   963 The following ``QuerySet`` methods evaluate the ``QuerySet`` and return

   964 something *other than* a ``QuerySet``.

   965 

   966 These methods do not use a cache (see :ref:`caching-and-querysets`). Rather,

   967 they query the database each time they're called.

   968 

   969 get

   970 ~~~

   971 

   972 .. method:: get(**kwargs)

   973 

   974 Returns the object matching the given lookup parameters, which should be in

   975 the format described in `Field lookups`_.

   976 

   977 ``get()`` raises ``MultipleObjectsReturned`` if more than one object was

   978 found. The ``MultipleObjectsReturned`` exception is an attribute of the model

   979 class.

   980 

   981 ``get()`` raises a ``DoesNotExist`` exception if an object wasn't found for

   982 the given parameters. This exception is also an attribute of the model class.

   983 Example::

   984 

   985     Entry.objects.get(id='foo') # raises Entry.DoesNotExist

   986 

   987 The ``DoesNotExist`` exception inherits from

   988 ``django.core.exceptions.ObjectDoesNotExist``, so you can target multiple

   989 ``DoesNotExist`` exceptions. Example::

   990 

   991     from django.core.exceptions import ObjectDoesNotExist

   992     try:

   993         e = Entry.objects.get(id=3)

   994         b = Blog.objects.get(id=1)

   995     except ObjectDoesNotExist:

   996         print "Either the entry or blog doesn't exist."

   997 

   998 create

   999 ~~~~~~

  1000 

  1001 .. method:: create(**kwargs)

  1002 

  1003 A convenience method for creating an object and saving it all in one step.  Thus::

  1004 

  1005     p = Person.objects.create(first_name="Bruce", last_name="Springsteen")

  1006 

  1007 and::

  1008 

  1009     p = Person(first_name="Bruce", last_name="Springsteen")

  1010     p.save(force_insert=True)

  1011 

  1012 are equivalent.

  1013 

  1014 The :ref:`force_insert <ref-models-force-insert>` parameter is documented

  1015 elsewhere, but all it means is that a new object will always be created.

  1016 Normally you won't need to worry about this. However, if your model contains a

  1017 manual primary key value that you set and if that value already exists in the

  1018 database, a call to ``create()`` will fail with an :exc:`IntegrityError` since

  1019 primary keys must be unique. So remember to be prepared to handle the exception

  1020 if you are using manual primary keys.

  1021 

  1022 get_or_create

  1023 ~~~~~~~~~~~~~

  1024 

  1025 .. method:: get_or_create(**kwargs)

  1026 

  1027 A convenience method for looking up an object with the given kwargs, creating

  1028 one if necessary.

  1029 

  1030 Returns a tuple of ``(object, created)``, where ``object`` is the retrieved or

  1031 created object and ``created`` is a boolean specifying whether a new object was

  1032 created.

  1033 

  1034 This is meant as a shortcut to boilerplatish code and is mostly useful for

  1035 data-import scripts. For example::

  1036 

  1037     try:

  1038         obj = Person.objects.get(first_name='John', last_name='Lennon')

  1039     except Person.DoesNotExist:

  1040         obj = Person(first_name='John', last_name='Lennon', birthday=date(1940, 10, 9))

  1041         obj.save()

  1042 

  1043 This pattern gets quite unwieldy as the number of fields in a model goes up.

  1044 The above example can be rewritten using ``get_or_create()`` like so::

  1045 

  1046     obj, created = Person.objects.get_or_create(first_name='John', last_name='Lennon',

  1047                       defaults={'birthday': date(1940, 10, 9)})

  1048 

  1049 Any keyword arguments passed to ``get_or_create()`` -- *except* an optional one

  1050 called ``defaults`` -- will be used in a ``get()`` call. If an object is found,

  1051 ``get_or_create()`` returns a tuple of that object and ``False``. If an object

  1052 is *not* found, ``get_or_create()`` will instantiate and save a new object,

  1053 returning a tuple of the new object and ``True``. The new object will be

  1054 created roughly according to this algorithm::

  1055 

  1056     defaults = kwargs.pop('defaults', {})

  1057     params = dict([(k, v) for k, v in kwargs.items() if '__' not in k])

  1058     params.update(defaults)

  1059     obj = self.model(**params)

  1060     obj.save()

  1061 

  1062 In English, that means start with any non-``'defaults'`` keyword argument that

  1063 doesn't contain a double underscore (which would indicate a non-exact lookup).

  1064 Then add the contents of ``defaults``, overriding any keys if necessary, and

  1065 use the result as the keyword arguments to the model class. As hinted at

  1066 above, this is a simplification of the algorithm that is used, but it contains

  1067 all the pertinent details. The internal implementation has some more

  1068 error-checking than this and handles some extra edge-conditions; if you're

  1069 interested, read the code.

  1070 

  1071 If you have a field named ``defaults`` and want to use it as an exact lookup in

  1072 ``get_or_create()``, just use ``'defaults__exact'``, like so::

  1073 

  1074     Foo.objects.get_or_create(defaults__exact='bar', defaults={'defaults': 'baz'})

  1075 

  1076 

  1077 The ``get_or_create()`` method has similar error behaviour to ``create()``

  1078 when you are using manually specified primary keys. If an object needs to be

  1079 created and the key already exists in the database, an ``IntegrityError`` will

  1080 be raised.

  1081 

  1082 Finally, a word on using ``get_or_create()`` in Django views. As mentioned

  1083 earlier, ``get_or_create()`` is mostly useful in scripts that need to parse

  1084 data and create new records if existing ones aren't available. But if you need

  1085 to use ``get_or_create()`` in a view, please make sure to use it only in

  1086 ``POST`` requests unless you have a good reason not to. ``GET`` requests

  1087 shouldn't have any effect on data; use ``POST`` whenever a request to a page

  1088 has a side effect on your data. For more, see `Safe methods`_ in the HTTP spec.

  1089 

  1090 .. _Safe methods: http://www.w3.org/Protocols/rfc2616/rfc2616-sec9.html#sec9.1.1

  1091 

  1092 count

  1093 ~~~~~

  1094 

  1095 .. method:: count()

  1096 

  1097 Returns an integer representing the number of objects in the database matching

  1098 the ``QuerySet``. ``count()`` never raises exceptions.

  1099 

  1100 Example::

  1101 

  1102     # Returns the total number of entries in the database.

  1103     Entry.objects.count()

  1104 

  1105     # Returns the number of entries whose headline contains 'Lennon'

  1106     Entry.objects.filter(headline__contains='Lennon').count()

  1107 

  1108 ``count()`` performs a ``SELECT COUNT(*)`` behind the scenes, so you should

  1109 always use ``count()`` rather than loading all of the record into Python

  1110 objects and calling ``len()`` on the result (unless you need to load the

  1111 objects into memory anyway, in which case ``len()`` will be faster).

  1112 

  1113 Depending on which database you're using (e.g. PostgreSQL vs. MySQL),

  1114 ``count()`` may return a long integer instead of a normal Python integer. This

  1115 is an underlying implementation quirk that shouldn't pose any real-world

  1116 problems.

  1117 

  1118 in_bulk

  1119 ~~~~~~~

  1120 

  1121 .. method:: in_bulk(id_list)

  1122 

  1123 Takes a list of primary-key values and returns a dictionary mapping each

  1124 primary-key value to an instance of the object with the given ID.

  1125 

  1126 Example::

  1127 

  1128     >>> Blog.objects.in_bulk([1])

  1129     {1: <Blog: Beatles Blog>}

  1130     >>> Blog.objects.in_bulk([1, 2])

  1131     {1: <Blog: Beatles Blog>, 2: <Blog: Cheddar Talk>}

  1132     >>> Blog.objects.in_bulk([])

  1133     {}

  1134 

  1135 If you pass ``in_bulk()`` an empty list, you'll get an empty dictionary.

  1136