======================

QuerySet API reference

======================

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

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

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

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

understand those documents before reading this one.

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

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

</topics/db/queries>`.

.. _when-querysets-are-evaluated:

When QuerySets are evaluated

============================

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

passed around without actually hitting the database. No database activity

actually occurs until you do something to evaluate the queryset.

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

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

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

      the headline of all entries in the database::

          for e in Entry.objects.all():

              print e.headline

    * **Slicing.** As explained in :ref:`limiting-querysets`, a ``QuerySet`` can

      be sliced, using Python's array-slicing syntax. Usually slicing a

      ``QuerySet`` returns another (unevaluated) ``QuerySet``, but Django will

      execute the database query if you use the "step" parameter of slice

      syntax.

    * **Pickling/Caching.** See the following section for details of what

      is involved when `pickling QuerySets`_. The important thing for the

      purposes of this section is that the results are read from the database.

    * **repr().** A ``QuerySet`` is evaluated when you call ``repr()`` on it.

      This is for convenience in the Python interactive interpreter, so you can

      immediately see your results when using the API interactively.

    * **len().** A ``QuerySet`` is evaluated when you call ``len()`` on it.

      This, as you might expect, returns the length of the result list.

      Note: *Don't* use ``len()`` on ``QuerySet``\s if all you want to do is

      determine the number of records in the set. It's much more efficient to

      handle a count at the database level, using SQL's ``SELECT COUNT(*)``,

      and Django provides a ``count()`` method for precisely this reason. See

      ``count()`` below.

    * **list().** Force evaluation of a ``QuerySet`` by calling ``list()`` on

      it. For example::

          entry_list = list(Entry.objects.all())

      Be warned, though, that this could have a large memory overhead, because

      Django will load each element of the list into memory. In contrast,

      iterating over a ``QuerySet`` will take advantage of your database to

      load data and instantiate objects only as you need them.

    * **bool().** Testing a ``QuerySet`` in a boolean context, such as using

      ``bool()``, ``or``, ``and`` or an ``if`` statement, will cause the query

      to be executed. If there is at least one result, the ``QuerySet`` is

      ``True``, otherwise ``False``. For example::

          if Entry.objects.filter(headline="Test"):

             print "There is at least one Entry with the headline Test"

      Note: *Don't* use this if all you want to do is determine if at least one

      result exists, and don't need the actual objects. It's more efficient to

      use ``exists()`` (see below).

.. _pickling QuerySets:

Pickling QuerySets

------------------

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

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

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

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

time, defeating the purpose of caching). This means that when you unpickle a

``QuerySet``, it contains the results at the moment it was pickled, rather

than the results that are currently in the database.

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

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

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

any results loaded) using some code like this::

    >>> import pickle

    >>> query = pickle.loads(s)     # Assuming 's' is the pickled string.

    >>> qs = MyModel.objects.all()

    >>> qs.query = query            # Restore the original 'query'.

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

the query construction and is not part of the public API. However, it is safe

(and fully supported) to pickle and unpickle the attribute's contents as

described here.

.. admonition:: You can't share pickles between versions

	Pickles of QuerySets are only valid for the version of Django that

	was used to generate them. If you generate a pickle using Django

	version N, there is no guarantee that pickle will be readable with

	Django version N+1. Pickles should not be used as part of a long-term

	archival strategy.

.. _pickle: http://docs.python.org/library/pickle.html

.. _queryset-api:

QuerySet API

============

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

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

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

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

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

Methods that return new QuerySets

---------------------------------

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

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

executed.

filter

~~~~~~

.. method:: filter(**kwargs)

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

parameters.

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

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

underlying SQL statement.

exclude

~~~~~~~

.. method:: exclude(**kwargs)

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

lookup parameters.

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

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

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

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

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

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

In SQL terms, that evaluates to::

    SELECT ...

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

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

OR whose headline is "Hello"::

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

In SQL terms, that evaluates to::

    SELECT ...

    WHERE NOT pub_date > '2005-1-3'

    AND NOT headline = 'Hello'

Note the second example is more restrictive.

annotate

~~~~~~~~

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

.. versionadded:: 1.1

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

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

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

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

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

The aggregation functions that are provided by Django are described

in `Aggregation Functions`_ below.

Annotations specified using keyword arguments will use the keyword as

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

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

the model field that is being aggregated.

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

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

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

    # The name of the first blog

    >>> q[0].name

    'Blogasaurus'

    # The number of entries on the first blog

    >>> q[0].entry__count

    42

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

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

control the name of the annotation::

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

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

    >>> q[0].number_of_entries

    42

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

Aggregation </topics/db/aggregation>`.

order_by

~~~~~~~~

.. method:: order_by(*fields)

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

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

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

Example::

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

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

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

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

like so::

    Entry.objects.order_by('?')

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

database backend you're using.

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

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

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

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

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

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

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

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

    Entry.objects.order_by('blog')

...is identical to::

    Entry.objects.order_by('blog__id')

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

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

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

related model ordering can change the expected results.

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

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

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

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

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

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

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

.. versionadded:: 1.0

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

documentation`_ for the old behaviour.

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

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

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

backend normally orders them.

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

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

.. versionadded:: 1.1

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

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

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

reverse

~~~~~~~

.. method:: reverse()

.. versionadded:: 1.0

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

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

ordering back to the normal direction.

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

    my_queryset.reverse()[:5]

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

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

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

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

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

efficiently in SQL.

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

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

a model which defines a default ordering, or when using

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

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

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

undefined afterward).

distinct

~~~~~~~~

.. method:: distinct()

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

eliminates duplicate rows from the query results.

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

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

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

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

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

.. note::

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

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

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

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

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

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

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

    returned.

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

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

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

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

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

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

    ``values()`` call.

values

~~~~~~

.. method:: values(*fields)

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

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

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

the attribute names of model objects.

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

objects::

    # This list contains a Blog object.

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

    [<Blog: Beatles Blog>]

    # This list contains a dictionary.

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

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

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

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

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

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

key and value for every field in the database table.

Example::

    >>> Blog.objects.values()

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

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

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

A couple of subtleties that are worth mentioning:

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

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

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

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

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

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

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

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

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

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

      will match the field name you passed in).

      For example::

        >>> Entry.objects.values()

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

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

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

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

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

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

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

      details.

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

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

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

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

      fields added by the select will be included automatically.

.. versionadded:: 1.0

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

example, only ``blog``.

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

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

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

the fields you need to use.

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

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

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

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

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

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

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

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

individualism.

values_list

~~~~~~~~~~~

.. method:: values_list(*fields)

.. versionadded:: 1.0

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

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

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

the first field, etc. For example::

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

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

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

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

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

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

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

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

    [1, 2, 3, ...]

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

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

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

dates

~~~~~

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

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

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

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

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

model.

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

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

``type``.

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

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

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

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

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

Examples::

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

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

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

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

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

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

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

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

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

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

none

~~~~

.. method:: none()

.. versionadded:: 1.0