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

Making queries

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

.. currentmodule:: django.db.models

Once you've created your :doc:`data models </topics/db/models>`, Django

automatically gives you a database-abstraction API that lets you create,

retrieve, update and delete objects. This document explains how to use this

API. Refer to the :doc:`data model reference </ref/models/index>` for full

details of all the various model lookup options.

Throughout this guide (and in the reference), we'll refer to the following

models, which comprise a Weblog application:

.. _queryset-model-example:

.. code-block:: python

    class Blog(models.Model):

        name = models.CharField(max_length=100)

        tagline = models.TextField()

        def __unicode__(self):

            return self.name

    class Author(models.Model):

        name = models.CharField(max_length=50)

        email = models.EmailField()

        def __unicode__(self):

            return self.name

    class Entry(models.Model):

        blog = models.ForeignKey(Blog)

        headline = models.CharField(max_length=255)

        body_text = models.TextField()

        pub_date = models.DateTimeField()

        authors = models.ManyToManyField(Author)

        n_comments = models.IntegerField()

        n_pingbacks = models.IntegerField()

        rating = models.IntegerField()

        def __unicode__(self):

            return self.headline

Creating objects

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

To represent database-table data in Python objects, Django uses an intuitive

system: A model class represents a database table, and an instance of that

class represents a particular record in the database table.

To create an object, instantiate it using keyword arguments to the model class,

then call ``save()`` to save it to the database.

You import the model class from wherever it lives on the Python path, as you

may expect. (We point this out here because previous Django versions required

funky model importing.)

Assuming models live in a file ``mysite/blog/models.py``, here's an example::

    >>> from blog.models import Blog

    >>> b = Blog(name='Beatles Blog', tagline='All the latest Beatles news.')

    >>> b.save()

This performs an ``INSERT`` SQL statement behind the scenes. Django doesn't hit

the database until you explicitly call ``save()``.

The ``save()`` method has no return value.

.. seealso::

    ``save()`` takes a number of advanced options not described here.

    See the documentation for ``save()`` for complete details.

    To create an object and save it all in one step see the ```create()```

    method.

Saving changes to objects

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

To save changes to an object that's already in the database, use ``save()``.

Given a ``Blog`` instance ``b5`` that has already been saved to the database,

this example changes its name and updates its record in the database::

    >> b5.name = 'New name'

    >> b5.save()

This performs an ``UPDATE`` SQL statement behind the scenes. Django doesn't hit

the database until you explicitly call ``save()``.

Saving ``ForeignKey`` and ``ManyToManyField`` fields

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

Updating a ``ForeignKey`` field works exactly the same way as saving a normal

field; simply assign an object of the right type to the field in question.

This example updates the ``blog`` attribute of an ``Entry`` instance ``entry``::

    >>> from blog.models import Entry

    >>> entry = Entry.objects.get(pk=1)

    >>> cheese_blog = Blog.objects.get(name="Cheddar Talk")

    >>> entry.blog = cheese_blog

    >>> entry.save()

Updating a ``ManyToManyField`` works a little differently; use the ``add()``

method on the field to add a record to the relation. This example adds the

``Author`` instance ``joe`` to the ``entry`` object::

    >>> from blog.models import Author

    >>> joe = Author.objects.create(name="Joe")

    >>> entry.authors.add(joe)

Django will complain if you try to assign or add an object of the wrong type.

Retrieving objects

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

To retrieve objects from your database, you construct a ``QuerySet`` via a

``Manager`` on your model class.

A ``QuerySet`` represents a collection of objects from your database. It can

have zero, one or many *filters* -- criteria that narrow down the collection

based on given parameters. In SQL terms, a ``QuerySet`` equates to a ``SELECT``

statement, and a filter is a limiting clause such as ``WHERE`` or ``LIMIT``.

You get a ``QuerySet`` by using your model's ``Manager``. Each model has at

least one ``Manager``, and it's called ``objects`` by default. Access it

directly via the model class, like so::

    >>> Blog.objects

    <django.db.models.manager.Manager object at ...>

    >>> b = Blog(name='Foo', tagline='Bar')

    >>> b.objects

    Traceback:

        ...

    AttributeError: "Manager isn't accessible via Blog instances."

.. note::

    ``Managers`` are accessible only via model classes, rather than from model

    instances, to enforce a separation between "table-level" operations and

    "record-level" operations.

The ``Manager`` is the main source of ``QuerySets`` for a model. It acts as a

"root" ``QuerySet`` that describes all objects in the model's database table.

For example, ``Blog.objects`` is the initial ``QuerySet`` that contains all

``Blog`` objects in the database.

Retrieving all objects

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

The simplest way to retrieve objects from a table is to get all of them.

To do this, use the ``all()`` method on a ``Manager``::

    >>> all_entries = Entry.objects.all()

The ``all()`` method returns a ``QuerySet`` of all the objects in the database.

(If ``Entry.objects`` is a ``QuerySet``, why can't we just do ``Entry.objects``?

That's because ``Entry.objects``, the root ``QuerySet``, is a special case

that cannot be evaluated. The ``all()`` method returns a ``QuerySet`` that

*can* be evaluated.)

Retrieving specific objects with filters

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

The root ``QuerySet`` provided by the ``Manager`` describes all objects in the

database table. Usually, though, you'll need to select only a subset of the

complete set of objects.

To create such a subset, you refine the initial ``QuerySet``, adding filter

conditions. The two most common ways to refine a ``QuerySet`` are:

    ``filter(**kwargs)``

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

        lookup parameters.

    ``exclude(**kwargs)``

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

        given lookup parameters.

The lookup parameters (``**kwargs`` in the above function definitions) should

be in the format described in `Field lookups`_ below.

For example, to get a ``QuerySet`` of blog entries from the year 2006, use

``filter()`` like so::

    Entry.objects.filter(pub_date__year=2006)

We don't have to add an ``all()`` -- ``Entry.objects.all().filter(...)``. That

would still work, but you only need ``all()`` when you want all objects from the

root ``QuerySet``.

.. _chaining-filters:

Chaining filters

~~~~~~~~~~~~~~~~

The result of refining a ``QuerySet`` is itself a ``QuerySet``, so it's

possible to chain refinements together. For example::

    >>> Entry.objects.filter(

    ...     headline__startswith='What'

    ... ).exclude(

    ...     pub_date__gte=datetime.now()

    ... ).filter(

    ...     pub_date__gte=datetime(2005, 1, 1)

    ... )

This takes the initial ``QuerySet`` of all entries in the database, adds a

filter, then an exclusion, then another filter. The final result is a

``QuerySet`` containing all entries with a headline that starts with "What",

that were published between January 1, 2005, and the current day.

.. _filtered-querysets-are-unique:

Filtered QuerySets are unique

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Each time you refine a ``QuerySet``, you get a brand-new ``QuerySet`` that is

in no way bound to the previous ``QuerySet``. Each refinement creates a

separate and distinct ``QuerySet`` that can be stored, used and reused.

Example::

    >> q1 = Entry.objects.filter(headline__startswith="What")

    >> q2 = q1.exclude(pub_date__gte=datetime.now())

    >> q3 = q1.filter(pub_date__gte=datetime.now())

These three ``QuerySets`` are separate. The first is a base ``QuerySet``

containing all entries that contain a headline starting with "What". The second

is a subset of the first, with an additional criteria that excludes records

whose ``pub_date`` is greater than now. The third is a subset of the first,

with an additional criteria that selects only the records whose ``pub_date`` is

greater than now. The initial ``QuerySet`` (``q1``) is unaffected by the

refinement process.

.. _querysets-are-lazy:

QuerySets are lazy

~~~~~~~~~~~~~~~~~~

``QuerySets`` are lazy -- the act of creating a ``QuerySet`` doesn't involve any

database activity. You can stack filters together all day long, and Django won't

actually run the query until the ``QuerySet`` is *evaluated*. Take a look at

this example::

    >>> q = Entry.objects.filter(headline__startswith="What")

    >>> q = q.filter(pub_date__lte=datetime.now())

    >>> q = q.exclude(body_text__icontains="food")

    >>> print q

Though this looks like three database hits, in fact it hits the database only

once, at the last line (``print q``). In general, the results of a ``QuerySet``

aren't fetched from the database until you "ask" for them. When you do, the

``QuerySet`` is *evaluated* by accessing the database. For more details on

exactly when evaluation takes place, see :ref:`when-querysets-are-evaluated`.

.. _retrieving-single-object-with-get:

Retrieving a single object with get

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

``.filter()`` will always give you a ``QuerySet``, even if only a single

object matches the query - in this case, it will be a ``QuerySet`` containing

a single element.

If you know there is only one object that matches your query, you can use

the ``get()`` method on a `Manager` which returns the object directly::

    >>> one_entry = Entry.objects.get(pk=1)

You can use any query expression with ``get()``, just like with ``filter()`` -

again, see `Field lookups`_ below.

Note that there is a difference between using ``.get()``, and using

``.filter()`` with a slice of ``[0]``. If there are no results that match the

query, ``.get()`` will raise a ``DoesNotExist`` exception. This exception is an

attribute of the model class that the query is being performed on - so in the

code above, if there is no ``Entry`` object with a primary key of 1, Django will

raise ``Entry.DoesNotExist``.

Similarly, Django will complain if more than one item matches the ``get()``

query. In this case, it will raise ``MultipleObjectsReturned``, which again is

an attribute of the model class itself.

Other QuerySet methods

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

Most of the time you'll use ``all()``, ``get()``, ``filter()`` and ``exclude()``

when you need to look up objects from the database. However, that's far from all

there is; see the :ref:`QuerySet API Reference <queryset-api>` for a complete

list of all the various ``QuerySet`` methods.

.. _limiting-querysets:

Limiting QuerySets

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

Use a subset of Python's array-slicing syntax to limit your ``QuerySet`` to a

certain number of results. This is the equivalent of SQL's ``LIMIT`` and

``OFFSET`` clauses.

For example, this returns the first 5 objects (``LIMIT 5``)::

    >>> Entry.objects.all()[:5]

This returns the sixth through tenth objects (``OFFSET 5 LIMIT 5``)::

    >>> Entry.objects.all()[5:10]

Negative indexing (i.e. ``Entry.objects.all()[-1]``) is not supported.

Generally, slicing a ``QuerySet`` returns a new ``QuerySet`` -- it doesn't

evaluate the query. An exception is if you use the "step" parameter of Python

slice syntax. For example, this would actually execute the query in order to

return a list of every *second* object of the first 10::

    >>> Entry.objects.all()[:10:2]

To retrieve a *single* object rather than a list

(e.g. ``SELECT foo FROM bar LIMIT 1``), use a simple index instead of a

slice. For example, this returns the first ``Entry`` in the database, after

ordering entries alphabetically by headline::

    >>> Entry.objects.order_by('headline')[0]

This is roughly equivalent to::

    >>> Entry.objects.order_by('headline')[0:1].get()

Note, however, that the first of these will raise ``IndexError`` while the

second will raise ``DoesNotExist`` if no objects match the given criteria. See

:meth:`~django.db.models.QuerySet.get` for more details.

.. _field-lookups-intro:

Field lookups

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

Field lookups are how you specify the meat of an SQL ``WHERE`` clause. They're

specified as keyword arguments to the ``QuerySet`` methods ``filter()``,

``exclude()`` and ``get()``.

Basic lookups keyword arguments take the form ``field__lookuptype=value``.

(That's a double-underscore). For example::

    >>> Entry.objects.filter(pub_date__lte='2006-01-01')

translates (roughly) into the following SQL::

    SELECT * FROM blog_entry WHERE pub_date <= '2006-01-01';

.. admonition:: How this is possible

   Python has the ability to define functions that accept arbitrary name-value

   arguments whose names and values are evaluated at runtime. For more

   information, see `Keyword Arguments`_ in the official Python tutorial.

   .. _`Keyword Arguments`: http://docs.python.org/tutorial/controlflow.html#keyword-arguments

If you pass an invalid keyword argument, a lookup function will raise

``TypeError``.

The database API supports about two dozen lookup types; a complete reference

can be found in the :ref:`field lookup reference <field-lookups>`. To give you a taste of what's available, here's some of the more common lookups

you'll probably use:

    :lookup:`exact`

        An "exact" match. For example::

            >>> Entry.objects.get(headline__exact="Man bites dog")

        Would generate SQL along these lines:

        .. code-block:: sql

            SELECT ... WHERE headline = 'Man bites dog';

        If you don't provide a lookup type -- that is, if your keyword argument

        doesn't contain a double underscore -- the lookup type is assumed to be

        ``exact``.

        For example, the following two statements are equivalent::

            >>> Blog.objects.get(id__exact=14)  # Explicit form

            >>> Blog.objects.get(id=14)         # __exact is implied

        This is for convenience, because ``exact`` lookups are the common case.

    :lookup:`iexact`

        A case-insensitive match. So, the query::

            >>> Blog.objects.get(name__iexact="beatles blog")

        Would match a ``Blog`` titled "Beatles Blog", "beatles blog", or even

        "BeAtlES blOG".

    :lookup:`contains`

        Case-sensitive containment test. For example::

            Entry.objects.get(headline__contains='Lennon')

        Roughly translates to this SQL:

        .. code-block:: sql

            SELECT ... WHERE headline LIKE '%Lennon%';

        Note this will match the headline ``'Today Lennon honored'`` but not

        ``'today lennon honored'``.

        There's also a case-insensitive version, :lookup:`icontains`.

    :lookup:`startswith`, :lookup:`endswith`

        Starts-with and ends-with search, respectively. There are also

        case-insensitive versions called :lookup:`istartswith` and

        :lookup:`iendswith`.

Again, this only scratches the surface. A complete reference can be found in the

:ref:`field lookup reference <field-lookups>`.

Lookups that span relationships

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

Django offers a powerful and intuitive way to "follow" relationships in

lookups, taking care of the SQL ``JOIN``\s for you automatically, behind the

scenes. To span a relationship, just use the field name of related fields

across models, separated by double underscores, until you get to the field you

want.

This example retrieves all ``Entry`` objects with a ``Blog`` whose ``name``

is ``'Beatles Blog'``::

    >>> Entry.objects.filter(blog__name__exact='Beatles Blog')

This spanning can be as deep as you'd like.

It works backwards, too. To refer to a "reverse" relationship, just use the

lowercase name of the model.

This example retrieves all ``Blog`` objects which have at least one ``Entry``

whose ``headline`` contains ``'Lennon'``::

    >>> Blog.objects.filter(entry__headline__contains='Lennon')

If you are filtering across multiple relationships and one of the intermediate

models doesn't have a value that meets the filter condition, Django will treat

it as if there is an empty (all values are ``NULL``), but valid, object there.

All this means is that no error will be raised. For example, in this filter::

    Blog.objects.filter(entry__authors__name='Lennon')

(if there was a related ``Author`` model), if there was no ``author``

associated with an entry, it would be treated as if there was also no ``name``

attached, rather than raising an error because of the missing ``author``.

Usually this is exactly what you want to have happen. The only case where it

might be confusing is if you are using ``isnull``. Thus::

    Blog.objects.filter(entry__authors__name__isnull=True)

will return ``Blog`` objects that have an empty ``name`` on the ``author`` and

also those which have an empty ``author`` on the ``entry``. If you don't want

those latter objects, you could write::

    Blog.objects.filter(entry__authors__isnull=False,

            entry__authors__name__isnull=True)

Spanning multi-valued relationships

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. versionadded:: 1.0

When you are filtering an object based on a ``ManyToManyField`` or a reverse

``ForeignKey``, there are two different sorts of filter you may be

interested in. Consider the ``Blog``/``Entry`` relationship (``Blog`` to

``Entry`` is a one-to-many relation). We might be interested in finding blogs

that have an entry which has both *"Lennon"* in the headline and was published

in 2008. Or we might want to find blogs that have an entry with *"Lennon"* in

the headline as well as an entry that was published in 2008. Since there are

multiple entries associated with a single ``Blog``, both of these queries are

possible and make sense in some situations.

The same type of situation arises with a ``ManyToManyField``. For example, if

an ``Entry`` has a ``ManyToManyField`` called ``tags``, we might want to find

entries linked to tags called *"music"* and *"bands"* or we might want an

entry that contains a tag with a name of *"music"* and a status of *"public"*.

To handle both of these situations, Django has a consistent way of processing

``filter()`` and ``exclude()`` calls. Everything inside a single ``filter()``

call is applied simultaneously to filter out items matching all those

requirements. Successive ``filter()`` calls further restrict the set of

objects, but for multi-valued relations, they apply to any object linked to

the primary model, not necessarily those objects that were selected by an

earlier ``filter()`` call.

That may sound a bit confusing, so hopefully an example will clarify. To

select all blogs that contain entries with both *"Lennon"* in the headline

and that were published in 2008 (the same entry satisfying both conditions),

we would write::

    Blog.objects.filter(entry__headline__contains='Lennon',

            entry__pub_date__year=2008)

To select all blogs that contain an entry with *"Lennon"* in the headline

**as well as** an entry that was published in 2008, we would write::

    Blog.objects.filter(entry__headline__contains='Lennon').filter(

            entry__pub_date__year=2008)

In this second example, the first filter restricted the queryset to all those

blogs linked to that particular type of entry. The second filter restricted

the set of blogs *further* to those that are also linked to the second type of

entry. The entries select by the second filter may or may not be the same as

the entries in the first filter. We are filtering the ``Blog`` items with each

filter statement, not the ``Entry`` items.

All of this behavior also applies to ``exclude()``: all the conditions in a

single ``exclude()`` statement apply to a single instance (if those conditions

are talking about the same multi-valued relation). Conditions in subsequent

``filter()`` or ``exclude()`` calls that refer to the same relation may end up

filtering on different linked objects.

.. _query-expressions: