| ====================== |
| QuerySet API reference |
| ====================== |
| |
| .. currentmodule:: django.db.models.query |
| |
| 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) |
| |
| Note: Don't use this if all you want to do is determine if at least one |
| result exists. It's more efficient to use :meth:`~QuerySet.exists`. |
| |
| * **Slicing.** As explained in :ref:`limiting-querysets`, a ``QuerySet`` can |
| be sliced, using Python's array-slicing syntax. Slicing an unevaluated |
| ``QuerySet`` usually returns another unevaluated ``QuerySet``, but Django |
| will execute the database query if you use the "step" parameter of slice |
| syntax, and will return a list. Slicing a ``QuerySet`` that has been |
| evaluated (partially or fully) also returns a list. |
| |
| * **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 :meth:`~QuerySet.exists` (see below). |
| |
| .. _pickling QuerySets: |
| |
| Pickling QuerySets |
| ------------------ |
| |
| If you :mod:`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. |
| |
| .. _queryset-api: |
| |
| QuerySet API |
| ============ |
| |
| Though you usually won't create one manually — you'll go through a |
| :class:`~django.db.models.Manager` — here's the formal declaration of a |
| ``QuerySet``: |
| |
| .. class:: QuerySet([model=None, query=None, using=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. These methods are covered in |
| detail later in this section. |
| |
| The ``QuerySet`` class has two public attributes you can use for |
| introspection: |
| |
| .. attribute:: ordered |
| |
| ``True`` if the ``QuerySet`` is ordered — i.e. has an |
| :meth:`order_by()` clause or a default ordering on the model. |
| ``False`` otherwise. |
| |
| .. attribute:: db |
| |
| The database that will be used if this query is executed now. |
| |
| .. note:: |
| |
| The ``query`` parameter to :class:`QuerySet` exists so that specialized |
| query subclasses such as |
| :class:`~django.contrib.gis.db.models.GeoQuerySet` can reconstruct |
| internal query state. The value of the parameter is an opaque |
| representation of that query state and is not part of a public API. |
| To put it simply: if you need to ask, you don't need to use it. |
| |
| .. currentmodule:: django.db.models.query.QuerySet |
| |
| 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) |
| |
| 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 :attr:`Meta.ordering |
| <django.db.models.Options.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 |
| :meth:`distinct()`. See the note in :meth:`distinct` for an explanation of how |
| related model ordering can change the expected results. |
| |
| .. note:: |
| It is permissible to specify a multi-valued field to order the results by |
| (for example, a :class:`~django.db.models.ManyToManyField` field, or the |
| reverse relation of a :class:`~django.db.models.ForeignKey` field). |
| |
| Consider this case:: |
| |
| class Event(Model): |
| parent = models.ForeignKey('self', related_name='children') |
| date = models.DateField() |
| |
| Event.objects.order_by('children__date') |
| |
| Here, there could potentially be multiple ordering data for each ``Event``; |
| each ``Event`` with multiple ``children`` will be returned multiple times |
| into the new ``QuerySet`` that ``order_by()`` creates. In other words, |
| using ``order_by()`` on the ``QuerySet`` could return more items than you |
| were working on to begin with - which is probably neither expected nor |
| useful. |
| |
| Thus, take care when using multi-valued field to order the results. **If** |
| you can be sure that there will only be one ordering piece of data for each |
| of the items you're ordering, this approach should not present problems. If |
| not, make sure the results are what you expect. |
| |
| 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 :meth:`order_by()` with no parameters. |
| |
| 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() |
| |
| 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 :meth:`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([*fields]) |
| |
| 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 :meth:`values()` query to restrict the columns |
| selected, the columns used in any :meth:`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 |
| :meth:`values()` together, be careful when ordering by fields not in the |
| :meth:`values()` call. |
| |
| .. versionadded:: 1.4 |
| |
| As of Django 1.4, you can pass positional arguments (``*fields``) in order to |
| specify the names of fields to which the ``DISTINCT`` should apply. This |
| translates to a ``SELECT DISTINCT ON`` SQL query. |
| |
| Here's the difference. For a normal ``distinct()`` call, the database compares |
| *each* field in each row when determining which rows are distinct. For a |
| ``distinct()`` call with specified field names, the database will only compare |
| the specified field names. |
| |
| .. note:: |
| This ability to specify field names is only available in PostgreSQL. |
| |
| .. note:: |
| When you specify field names, you *must* provide an ``order_by()`` in the |
| ``QuerySet``, and the fields in ``order_by()`` must start with the fields in |
| ``distinct()``, in the same order. |
| |
| For example, ``SELECT DISTINCT ON (a)`` gives you the first row for each |
| value in column ``a``. If you don't specify an order, you'll get some |
| arbitrary row. |
| |
| Examples:: |
| |
| >>> Author.objects.distinct() |
| [...] |
| |
| >>> Entry.objects.order_by('pub_date').distinct('pub_date') |
| [...] |
| |
| >>> Entry.objects.order_by('blog').distinct('blog') |
| [...] |
| |
| >>> Entry.objects.order_by('author', 'pub_date').distinct('author', 'pub_date') |
| [...] |
| |
| >>> Entry.objects.order_by('blog__name', 'mod_date').distinct('blog__name', 'mod_date') |
| [...] |
| |
| >>> Entry.objects.order_by('author', 'pub_date').distinct('author') |
| [...] |
| |
| values |
| ~~~~~~ |
| |
| .. method:: values(*fields) |
| |
| Returns a ``ValuesQuerySet`` — a ``QuerySet`` subclass 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.'}] |
| |
| The ``values()`` method 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 few subtleties that are worth mentioning: |
| |
| * 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 :meth:`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 :meth:`extra()` call, |
| any fields defined by a ``select`` argument in the :meth:`extra()` must |
| be explicitly included in the ``values()`` call. Any :meth:`extra()` call |
| made after a ``values()`` call will have its extra selected fields |
| ignored. |
| |
| 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. |
| |
| You can also refer to fields on related models with reverse relations through |
| ``OneToOneField``, ``ForeignKey`` and ``ManyToManyField`` attributes:: |
| |
| Blog.objects.values('name', 'entry__headline') |
| [{'name': 'My blog', 'entry__headline': 'An entry'}, |
| {'name': 'My blog', 'entry__headline': 'Another entry'}, ...] |
| |
| .. warning:: |
| |
| Because :class:`~django.db.models.ManyToManyField` attributes and reverse |
| relations can have multiple related rows, including these can have a |
| multiplier effect on the size of your result set. This will be especially |
| pronounced if you include multiple such fields in your ``values()`` query, |
| in which case all possible combinations will be returned. |
| |
| values_list |
| ~~~~~~~~~~~ |
| |
| .. method:: values_list(*fields) |
| |
| 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)] |
| |
| .. warning:: |
| |
| When :doc:`time zone support </topics/i18n/timezones>` is enabled, Django |
| uses UTC in the database connection, which means the aggregation is |
| performed in UTC. This is a known limitation of the current implementation. |
| |
| none |
| ~~~~ |
| |
| .. method:: none() |
| |
| Returns an ``EmptyQuerySet`` — a ``QuerySet`` subclass that always evaluates to |
| an empty list. This can be used in cases where you know that you should return |
| an empty result set and your caller is expecting a ``QuerySet`` object (instead |
| of returning an empty list, for example.) |
| |
| Examples:: |
| |
| >>> Entry.objects.none() |
| [] |
| |
| all |
| ~~~ |
| |
| .. method:: all() |
| |
| Returns a *copy* of the current ``QuerySet`` (or ``QuerySet`` subclass). This |
| can be useful in situations where you might want to pass in either a model |
| manager or a ``QuerySet`` and do further filtering on the result. After calling |
| ``all()`` on either object, you'll definitely have a ``QuerySet`` to work with. |
| |
| select_related |
| ~~~~~~~~~~~~~~ |
| |
| .. method:: select_related() |
| |
| Returns a ``QuerySet`` that will automatically "follow" foreign-key |
| relationships, selecting that additional related-object data when it executes |
| its query. This is a performance booster which results in (sometimes much) |
| larger queries but means later use of foreign-key relationships won't require |
| database queries. |
| |
| The following examples illustrate the difference between plain lookups and |
| ``select_related()`` lookups. Here's standard lookup:: |
| |
| # Hits the database. |
| e = Entry.objects.get(id=5) |
| |
| # Hits the database again to get the related Blog object. |
| b = e.blog |
| |
| And here's ``select_related`` lookup:: |
| |
| # Hits the database. |
| e = Entry.objects.select_related().get(id=5) |
| |
| # Doesn't hit the database, because e.blog has been prepopulated |
| # in the previous query. |
| b = e.blog |
| |
| ``select_related()`` follows foreign keys as far as possible. If you have the |
| following models:: |
| |
| class City(models.Model): |
| # ... |
| pass |
| |
| class Person(models.Model): |
| # ... |
| hometown = models.ForeignKey(City) |
| |
| class Book(models.Model): |
| # ... |
| author = models.ForeignKey(Person) |
| |
| ...then a call to ``Book.objects.select_related().get(id=4)`` will cache the |
| related ``Person`` *and* the related ``City``:: |
| |
| b = Book.objects.select_related().get(id=4) |
| p = b.author # Doesn't hit the database. |
| c = p.hometown # Doesn't hit the database. |
| |
| b = Book.objects.get(id=4) # No select_related() in this example. |
| p = b.author # Hits the database. |
| c = p.hometown # Hits the database. |
| |
| Note that, by default, ``select_related()`` does not follow foreign keys that |
| have ``null=True``. |
| |
| Usually, using ``select_related()`` can vastly improve performance because your |
| app can avoid many database calls. However, there are times you are only |
| interested in specific related models, or have deeply nested sets of |
| relationships, and in these cases ``select_related()`` can be optimized by |
| explicitly passing the related field names you are interested in. Only |
| the specified relations will be followed. |
| |
| You can even do this for models that are more than one relation away by |
| separating the field names with double underscores, just as for filters. For |
| example, if you have this model:: |
| |
| class Room(models.Model): |
| # ... |
| building = models.ForeignKey(...) |
| |
| class Group(models.Model): |
| # ... |
| teacher = models.ForeignKey(...) |
| room = models.ForeignKey(Room) |
| subject = models.ForeignKey(...) |
| |
| ...and you only needed to work with the ``room`` and ``subject`` attributes, |
| you could write this:: |
| |
| g = Group.objects.select_related('room', 'subject') |
| |
| This is also valid:: |
| |
| g = Group.objects.select_related('room__building', 'subject') |
| |
| ...and would also pull in the ``building`` relation. |
| |
| You can refer to any :class:`~django.db.models.ForeignKey` or |
| :class:`~django.db.models.OneToOneField` relation in the list of fields |
| passed to ``select_related()``. This includes foreign keys that have |
| ``null=True`` (which are omitted in a no-parameter ``select_related()`` call). |
| It's an error to use both a list of fields and the ``depth`` parameter in the |
| same ``select_related()`` call; they are conflicting options. |
| |
| You can also refer to the reverse direction of a |
| :class:`~django.db.models.OneToOneField` in the list of fields passed to |
| ``select_related`` — that is, you can traverse a |
| :class:`~django.db.models.OneToOneField` back to the object on which the field |
| is defined. Instead of specifying the field name, use the :attr:`related_name |
| <django.db.models.ForeignKey.related_name>` for the field on the related object. |
| |
| .. deprecated:: 1.5 |
| The ``depth`` parameter to ``select_related()`` has been deprecated. You |
| should replace it with the use of the ``(*fields)`` listing specific |
| related fields instead as documented above. |
| |
| A depth limit of relationships to follow can also be specified:: |
| |
| b = Book.objects.select_related(depth=1).get(id=4) |
| p = b.author # Doesn't hit the database. |
| c = p.hometown # Requires a database call. |
| |
| A :class:`~django.db.models.OneToOneField` is not traversed in the reverse |
| direction if you are performing a depth-based ``select_related()`` call. |
| |
| prefetch_related |
| ~~~~~~~~~~~~~~~~ |
| |
| .. method:: prefetch_related(*lookups) |
| |
| .. versionadded:: 1.4 |
| |
| Returns a ``QuerySet`` that will automatically retrieve, in a single batch, |
| related objects for each of the specified lookups. |
| |
| This has a similar purpose to ``select_related``, in that both are designed to |
| stop the deluge of database queries that is caused by accessing related objects, |
| but the strategy is quite different. |
| |
| ``select_related`` works by creating a SQL join and including the fields of the |
| related object in the ``SELECT`` statement. For this reason, ``select_related`` |
| gets the related objects in the same database query. However, to avoid the much |
| larger result set that would result from joining across a 'many' relationship, |
| ``select_related`` is limited to single-valued relationships - foreign key and |
| one-to-one. |
| |
| ``prefetch_related``, on the other hand, does a separate lookup for each |
| relationship, and does the 'joining' in Python. This allows it to prefetch |
| many-to-many and many-to-one objects, which cannot be done using |
| ``select_related``, in addition to the foreign key and one-to-one relationships |
| that are supported by ``select_related``. It also supports prefetching of |
| :class:`~django.contrib.contenttypes.generic.GenericRelation` and |
| :class:`~django.contrib.contenttypes.generic.GenericForeignKey`. |
| |
| For example, suppose you have these models:: |
| |
| class Topping(models.Model): |
| name = models.CharField(max_length=30) |
| |
| class Pizza(models.Model): |
| name = models.CharField(max_length=50) |
| toppings = models.ManyToManyField(Topping) |
| |
| def __unicode__(self): |
| return u"%s (%s)" % (self.name, u", ".join([topping.name |
| for topping in self.toppings.all()])) |
| |
| and run:: |
| |
| >>> Pizza.objects.all() |
| [u"Hawaiian (ham, pineapple)", u"Seafood (prawns, smoked salmon)"... |
| |
| The problem with this is that every time ``Pizza.__unicode__()`` asks for |
| ``self.toppings.all()`` it has to query the database, so |
| ``Pizza.objects.all()`` will run a query on the Toppings table for **every** |
| item in the Pizza ``QuerySet``. |
| |
| We can reduce to just two queries using ``prefetch_related``: |
| |
| >>> Pizza.objects.all().prefetch_related('toppings') |
| |
| This implies a ``self.toppings.all()`` for each ``Pizza``; now each time |
| ``self.toppings.all()`` is called, instead of having to go to the database for |
| the items, it will find them in a prefetched ``QuerySet`` cache that was |
| populated in a single query. |
| |
| That is, all the relevant toppings will have been fetched in a single query, |
| and used to make ``QuerySets`` that have a pre-filled cache of the relevant |
| results; these ``QuerySets`` are then used in the ``self.toppings.all()`` calls. |
| |
| The additional queries in ``prefetch_related()`` are executed after the |
| ``QuerySet`` has begun to be evaluated and the primary query has been executed. |
| |
| Note that the result cache of the primary ``QuerySet`` and all specified related |
| objects will then be fully loaded into memory. This changes the typical |
| behavior of ``QuerySets``, which normally try to avoid loading all objects into |
| memory before they are needed, even after a query has been executed in the |
| database. |
| |
| .. note:: |
| |
| Remember that, as always with ``QuerySets``, any subsequent chained methods |
| which imply a different database query will ignore previously cached |
| results, and retrieve data using a fresh database query. So, if you write |
| the following: |
| |
| >>> pizzas = Pizza.objects.prefetch_related('toppings') |
| >>> [list(pizza.toppings.filter(spicy=True)) for pizza in pizzas] |
| |
| ...then the fact that ``pizza.toppings.all()`` has been prefetched will not |
| help you. The ``prefetch_related('toppings')`` implied |
| ``pizza.toppings.all()``, but ``pizza.toppings.filter()`` is a new and |
| different query. The prefetched cache can't help here; in fact it hurts |
| performance, since you have done a database query that you haven't used. So |
| use this feature with caution! |
| |
| You can also use the normal join syntax to do related fields of related |
| fields. Suppose we have an additional model to the example above:: |
| |
| class Restaurant(models.Model): |
| pizzas = models.ManyToMany(Pizza, related_name='restaurants') |
| best_pizza = models.ForeignKey(Pizza, related_name='championed_by') |
| |
| The following are all legal: |
| |
| >>> Restaurant.objects.prefetch_related('pizzas__toppings') |
| |
| This will prefetch all pizzas belonging to restaurants, and all toppings |
| belonging to those pizzas. This will result in a total of 3 database queries - |
| one for the restaurants, one for the pizzas, and one for the toppings. |
| |
| >>> Restaurant.objects.prefetch_related('best_pizza__toppings') |
| |
| This will fetch the best pizza and all the toppings for the best pizza for each |
| restaurant. This will be done in 3 database queries - one for the restaurants, |
| one for the 'best pizzas', and one for one for the toppings. |
| |
| Of course, the ``best_pizza`` relationship could also be fetched using |
| ``select_related`` to reduce the query count to 2: |
| |
| >>> Restaurant.objects.select_related('best_pizza').prefetch_related('best_pizza__toppings') |
| |
| Since the prefetch is executed after the main query (which includes the joins |
| needed by ``select_related``), it is able to detect that the ``best_pizza`` |
| objects have already been fetched, and it will skip fetching them again. |
| |
| Chaining ``prefetch_related`` calls will accumulate the lookups that are |
| prefetched. To clear any ``prefetch_related`` behavior, pass `None` as a |
| parameter:: |
| |
| >>> non_prefetched = qs.prefetch_related(None) |
| |
| One difference to note when using ``prefetch_related`` is that objects created |
| by a query can be shared between the different objects that they are related to |
| i.e. a single Python model instance can appear at more than one point in the |
| tree of objects that are returned. This will normally happen with foreign key |
| relationships. Typically this behavior will not be a problem, and will in fact |
| save both memory and CPU time. |
| |
| While ``prefetch_related`` supports prefetching ``GenericForeignKey`` |
| relationships, the number of queries will depend on the data. Since a |
| ``GenericForeignKey`` can reference data in multiple tables, one query per table |
| referenced is needed, rather than one query for all the items. There could be |
| additional queries on the ``ContentType`` table if the relevant rows have not |
| already been fetched. |
| |
| ``prefetch_related`` in most cases will be implemented using a SQL query that |
| uses the 'IN' operator. This means that for a large ``QuerySet`` a large 'IN' clause |
| could be generated, which, depending on the database, might have performance |
| problems of its own when it comes to parsing or executing the SQL query. Always |
| profile for your use case! |
| |
| Note that if you use ``iterator()`` to run the query, ``prefetch_related()`` |
| calls will be ignored since these two optimizations do not make sense together. |
| |
| extra |
| ~~~~~ |
| |
| .. method:: extra(select=None, where=None, params=None, tables=None, order_by=None, select_params=None) |
| |
| Sometimes, the Django query syntax by itself can't easily express a complex |
| ``WHERE`` clause. For these edge cases, Django provides the ``extra()`` |
| ``QuerySet`` modifier — a hook for injecting specific clauses into the SQL |
| generated by a ``QuerySet``. |
| |
| By definition, these extra lookups may not be portable to different database |
| engines (because you're explicitly writing SQL code) and violate the DRY |
| principle, so you should avoid them if possible. |
| |
| Specify one or more of ``params``, ``select``, ``where`` or ``tables``. None |
| of the arguments is required, but you should use at least one of them. |
| |
| * ``select`` |
| |
| The ``select`` argument lets you put extra fields in the ``SELECT`` |
| clause. It should be a dictionary mapping attribute names to SQL |
| clauses to use to calculate that attribute. |
| |
| Example:: |
| |
| Entry.objects.extra(select={'is_recent': "pub_date > '2006-01-01'"}) |
| |
| As a result, each ``Entry`` object will have an extra attribute, |
| ``is_recent``, a boolean representing whether the entry's ``pub_date`` |
| is greater than Jan. 1, 2006. |
| |
| Django inserts the given SQL snippet directly into the ``SELECT`` |
| statement, so the resulting SQL of the above example would be something |
| like:: |
| |
| SELECT blog_entry.*, (pub_date > '2006-01-01') AS is_recent |
| FROM blog_entry; |
| |
| |
| The next example is more advanced; it does a subquery to give each |
| resulting ``Blog`` object an ``entry_count`` attribute, an integer count |
| of associated ``Entry`` objects:: |
| |
| Blog.objects.extra( |
| select={ |
| 'entry_count': 'SELECT COUNT(*) FROM blog_entry WHERE blog_entry.blog_id = blog_blog.id' |
| }, |
| ) |
| |
| In this particular case, we're exploiting the fact that the query will |
| already contain the ``blog_blog`` table in its ``FROM`` clause. |
| |
| The resulting SQL of the above example would be:: |
| |
| SELECT blog_blog.*, (SELECT COUNT(*) FROM blog_entry WHERE blog_entry.blog_id = blog_blog.id) AS entry_count |
| FROM blog_blog; |
| |
| Note that the parentheses required by most database engines around |
| subqueries are not required in Django's ``select`` clauses. Also note |
| that some database backends, such as some MySQL versions, don't support |
| subqueries. |
| |
| In some rare cases, you might wish to pass parameters to the SQL |
| fragments in ``extra(select=...)``. For this purpose, use the |
| ``select_params`` parameter. Since ``select_params`` is a sequence and |
| the ``select`` attribute is a dictionary, some care is required so that |
| the parameters are matched up correctly with the extra select pieces. |
| In this situation, you should use a |
| :class:`django.utils.datastructures.SortedDict` for the ``select`` |
| value, not just a normal Python dictionary. |
| |
| This will work, for example:: |
| |
| Blog.objects.extra( |
| select=SortedDict([('a', '%s'), ('b', '%s')]), |
| select_params=('one', 'two')) |
| |
| The only thing to be careful about when using select parameters in |
| ``extra()`` is to avoid using the substring ``"%%s"`` (that's *two* |
| percent characters before the ``s``) in the select strings. Django's |
| tracking of parameters looks for ``%s`` and an escaped ``%`` character |
| like this isn't detected. That will lead to incorrect results. |
| |
| * ``where`` / ``tables`` |
| |
| You can define explicit SQL ``WHERE`` clauses — perhaps to perform |
| non-explicit joins — by using ``where``. You can manually add tables to |
| the SQL ``FROM`` clause by using ``tables``. |
| |
| ``where`` and ``tables`` both take a list of strings. All ``where`` |
| parameters are "AND"ed to any other search criteria. |
| |
| Example:: |
| |
| Entry.objects.extra(where=["foo='a' OR bar = 'a'", "baz = 'a'"]) |
| |
| ...translates (roughly) into the following SQL:: |
| |
| SELECT * FROM blog_entry WHERE (foo='a' OR bar='a') AND (baz='a') |
| |
| Be careful when using the ``tables`` parameter if you're specifying |
| tables that are already used in the query. When you add extra tables |
| via the ``tables`` parameter, Django assumes you want that table |
| included an extra time, if it is already included. That creates a |
| problem, since the table name will then be given an alias. If a table |
| appears multiple times in an SQL statement, the second and subsequent |
| occurrences must use aliases so the database can tell them apart. If |
| you're referring to the extra table you added in the extra ``where`` |
| parameter this is going to cause errors. |
| |
| Normally you'll only be adding extra tables that don't already appear |
| in the query. However, if the case outlined above does occur, there are |
| a few solutions. First, see if you can get by without including the |
| extra table and use the one already in the query. If that isn't |
| possible, put your ``extra()`` call at the front of the queryset |
| construction so that your table is the first use of that table. |
| Finally, if all else fails, look at the query produced and rewrite your |
| ``where`` addition to use the alias given to your extra table. The |
| alias will be the same each time you construct the queryset in the same |
| way, so you can rely upon the alias name to not change. |
| |
| * ``order_by`` |
| |
| If you need to order the resulting queryset using some of the new |
| fields or tables you have included via ``extra()`` use the ``order_by`` |
| parameter to ``extra()`` and pass in a sequence of strings. These |
| strings should either be model fields (as in the normal |
| :meth:`order_by()` method on querysets), of the form |
| ``table_name.column_name`` or an alias for a column that you specified |
| in the ``select`` parameter to ``extra()``. |
| |
| For example:: |
| |
| q = Entry.objects.extra(select={'is_recent': "pub_date > '2006-01-01'"}) |
| q = q.extra(order_by = ['-is_recent']) |
| |
| This would sort all the items for which ``is_recent`` is true to the |
| front of the result set (``True`` sorts before ``False`` in a |
| descending ordering). |
| |
| This shows, by the way, that you can make multiple calls to ``extra()`` |
| and it will behave as you expect (adding new constraints each time). |
| |
| * ``params`` |
| |
| The ``where`` parameter described above may use standard Python |
| database string placeholders — ``'%s'`` to indicate parameters the |
| database engine should automatically quote. The ``params`` argument is |
| a list of any extra parameters to be substituted. |
| |
| Example:: |
| |
| Entry.objects.extra(where=['headline=%s'], params=['Lennon']) |
| |
| Always use ``params`` instead of embedding values directly into |
| ``where`` because ``params`` will ensure values are quoted correctly |
| according to your particular backend. For example, quotes will be |
| escaped correctly. |
| |
| Bad:: |
| |
| Entry.objects.extra(where=["headline='Lennon'"]) |
| |
| Good:: |
| |
| Entry.objects.extra(where=['headline=%s'], params=['Lennon']) |
| |
| .. warning:: |
| |
| If you are performing queries on MySQL, note that MySQL's silent type coercion |
| may cause unexpected results when mixing types. If you query on a string |
| type column, but with an integer value, MySQL will coerce the types of all values |
| in the table to an integer before performing the comparison. For example, if your |
| table contains the values ``'abc'``, ``'def'`` and you query for ``WHERE mycolumn=0``, |
| both rows will match. To prevent this, perform the correct typecasting |
| before using the value in a query. |
| |
| defer |
| ~~~~~ |
| |
| .. method:: defer(*fields) |
| |
| In some complex data-modeling situations, your models might contain a lot of |
| fields, some of which could contain a lot of data (for example, text fields), |
| or require expensive processing to convert them to Python objects. If you are |
| using the results of a queryset in some situation where you don't know |
| if you need those particular fields when you initially fetch the data, you can |
| tell Django not to retrieve them from the database. |
| |
| This is done by passing the names of the fields to not load to ``defer()``:: |
| |
| Entry.objects.defer("headline", "body") |
| |
| A queryset that has deferred fields will still return model instances. Each |
| deferred field will be retrieved from the database if you access that field |
| (one at a time, not all the deferred fields at once). |
| |
| You can make multiple calls to ``defer()``. Each call adds new fields to the |
| deferred set:: |
| |
| # Defers both the body and headline fields. |
| Entry.objects.defer("body").filter(rating=5).defer("headline") |
| |
| The order in which fields are added to the deferred set does not matter. |
| Calling ``defer()`` with a field name that has already been deferred is |
| harmless (the field will still be deferred). |
| |
| You can defer loading of fields in related models (if the related models are |
| loading via :meth:`select_related()`) by using the standard double-underscore |
| notation to separate related fields:: |
| |
| Blog.objects.select_related().defer("entry__headline", "entry__body") |
| |
| If you want to clear the set of deferred fields, pass ``None`` as a parameter |
| to ``defer()``:: |
| |
| # Load all fields immediately. |
| my_queryset.defer(None) |
| |
| .. versionchanged:: 1.5 |
| |
| Some fields in a model won't be deferred, even if you ask for them. You can |
| never defer the loading of the primary key. If you are using |
| :meth:`select_related()` to retrieve related models, you shouldn't defer the |
| loading of the field that connects from the primary model to the related |
| one, doing so will result in an error. |
| |
| .. note:: |
| |
| The ``defer()`` method (and its cousin, :meth:`only()`, below) are only for |
| advanced use-cases. They provide an optimization for when you have analyzed |
| your queries closely and understand *exactly* what information you need and |
| have measured that the difference between returning the fields you need and |
| the full set of fields for the model will be significant. |
| |
| Even if you think you are in the advanced use-case situation, **only use |
| defer() when you cannot, at queryset load time, determine if you will need |
| the extra fields or not**. If you are frequently loading and using a |
| particular subset of your data, the best choice you can make is to |
| normalize your models and put the non-loaded data into a separate model |
| (and database table). If the columns *must* stay in the one table for some |
| reason, create a model with ``Meta.managed = False`` (see the |
| :attr:`managed attribute <django.db.models.Options.managed>` documentation) |
| containing just the fields you normally need to load and use that where you |
| might otherwise call ``defer()``. This makes your code more explicit to the |
| reader, is slightly faster and consumes a little less memory in the Python |
| process. |
| |
| .. versionchanged:: 1.5 |
| |
| .. note:: |
| |
| When calling :meth:`~django.db.models.Model.save()` for instances with |
| deferred fields, only the loaded fields will be saved. See |
| :meth:`~django.db.models.Model.save()` for more details. |
| |
| |
| only |
| ~~~~ |
| |
| .. method:: only(*fields) |
| |
| The ``only()`` method is more or less the opposite of :meth:`defer()`. You call |
| it with the fields that should *not* be deferred when retrieving a model. If |
| you have a model where almost all the fields need to be deferred, using |
| ``only()`` to specify the complementary set of fields can result in simpler |
| code. |
| |
| Suppose you have a model with fields ``name``, ``age`` and ``biography``. The |
| following two querysets are the same, in terms of deferred fields:: |
| |
| Person.objects.defer("age", "biography") |
| Person.objects.only("name") |
| |
| Whenever you call ``only()`` it *replaces* the set of fields to load |
| immediately. The method's name is mnemonic: **only** those fields are loaded |
| immediately; the remainder are deferred. Thus, successive calls to ``only()`` |
| result in only the final fields being considered:: |
| |
| # This will defer all fields except the headline. |
| Entry.objects.only("body", "rating").only("headline") |
| |
| Since ``defer()`` acts incrementally (adding fields to the deferred list), you |
| can combine calls to ``only()`` and ``defer()`` and things will behave |
| logically:: |
| |
| # Final result is that everything except "headline" is deferred. |
| Entry.objects.only("headline", "body").defer("body") |
| |
| # Final result loads headline and body immediately (only() replaces any |
| # existing set of fields). |
| Entry.objects.defer("body").only("headline", "body") |
| |
| .. versionchanged:: 1.5 |
| |
| All of the cautions in the note for the :meth:`defer` documentation apply to |
| ``only()`` as well. Use it cautiously and only after exhausting your other |
| options. Also note that using :meth:`only` and omitting a field requested |
| using :meth:`select_related` is an error as well. |
| |
| .. versionchanged:: 1.5 |
| |
| .. note:: |
| |
| When calling :meth:`~django.db.models.Model.save()` for instances with |
| deferred fields, only the loaded fields will be saved. See |
| :meth:`~django.db.models.Model.save()` for more details. |
| |
| using |
| ~~~~~ |
| |
| .. method:: using(alias) |
| |
| This method is for controlling which database the ``QuerySet`` will be |
| evaluated against if you are using more than one database. The only argument |
| this method takes is the alias of a database, as defined in |
| :setting:`DATABASES`. |
| |
| For example:: |
| |
| # queries the database with the 'default' alias. |
| >>> Entry.objects.all() |
| |
| # queries the database with the 'backup' alias |
| >>> Entry.objects.using('backup') |
| |
| select_for_update |
| ~~~~~~~~~~~~~~~~~ |
| |
| .. method:: select_for_update(nowait=False) |
| |
| .. versionadded:: 1.4 |
| |
| Returns a queryset that will lock rows until the end of the transaction, |
| generating a ``SELECT ... FOR UPDATE`` SQL statement on supported databases. |
| |
| For example:: |
| |
| entries = Entry.objects.select_for_update().filter(author=request.user) |
| |
| All matched entries will be locked until the end of the transaction block, |
| meaning that other transactions will be prevented from changing or acquiring |
| locks on them. |
| |
| Usually, if another transaction has already acquired a lock on one of the |
| selected rows, the query will block until the lock is released. If this is |
| not the behavior you want, call ``select_for_update(nowait=True)``. This will |
| make the call non-blocking. If a conflicting lock is already acquired by |
| another transaction, :exc:`~django.db.DatabaseError` will be raised when the |
| queryset is evaluated. |
| |
| Note that using ``select_for_update()`` will cause the current transaction to be |
| considered dirty, if under transaction management. This is to ensure that |
| Django issues a ``COMMIT`` or ``ROLLBACK``, releasing any locks held by the |
| ``SELECT FOR UPDATE``. |
| |
| Currently, the ``postgresql_psycopg2``, ``oracle``, and ``mysql`` database |
| backends support ``select_for_update()``. However, MySQL has no support for the |
| ``nowait`` argument. Obviously, users of external third-party backends should |
| check with their backend's documentation for specifics in those cases. |
| |
| Passing ``nowait=True`` to ``select_for_update`` using database backends that |
| do not support ``nowait``, such as MySQL, will cause a |
| :exc:`~django.db.DatabaseError` to be raised. This is in order to prevent code |
| unexpectedly blocking. |
| |
| Using ``select_for_update`` on backends which do not support |
| ``SELECT ... FOR UPDATE`` (such as SQLite) will have no effect. |
| |
| Methods that do not return QuerySets |
| ------------------------------------ |
| |
| The following ``QuerySet`` methods evaluate the ``QuerySet`` and return |
| something *other than* a ``QuerySet``. |
| |
| These methods do not use a cache (see :ref:`caching-and-querysets`). Rather, |
| they query the database each time they're called. |
| |
| get |
| ~~~ |
| |
| .. method:: get(**kwargs) |
| |
| Returns the object matching the given lookup parameters, which should be in |
| the format described in `Field lookups`_. |
| |
| ``get()`` raises :exc:`~django.core.exceptions.MultipleObjectsReturned` if more |
| than one object was found. The |
| :exc:`~django.core.exceptions.MultipleObjectsReturned` exception is an |
| attribute of the model class. |
| |
| ``get()`` raises a :exc:`~django.core.exceptions.DoesNotExist` exception if an |
| object wasn't found for the given parameters. This exception is also an |
| attribute of the model class. Example:: |
| |
| Entry.objects.get(id='foo') # raises Entry.DoesNotExist |
| |
| The :exc:`~django.core.exceptions.DoesNotExist` exception inherits from |
| :exc:`django.core.exceptions.ObjectDoesNotExist`, so you can target multiple |
| :exc:`~django.core.exceptions.DoesNotExist` exceptions. Example:: |
| |
| from django.core.exceptions import ObjectDoesNotExist |
| try: |
| e = Entry.objects.get(id=3) |
| b = Blog.objects.get(id=1) |
| except ObjectDoesNotExist: |
| print("Either the entry or blog doesn't exist.") |
| |
| create |
| ~~~~~~ |
| |
| .. method:: create(**kwargs) |
| |
| A convenience method for creating an object and saving it all in one step. Thus:: |
| |
| p = Person.objects.create(first_name="Bruce", last_name="Springsteen") |
| |
| and:: |
| |
| p = Person(first_name="Bruce", last_name="Springsteen") |
| p.save(force_insert=True) |
| |
| are equivalent. |
| |
| The :ref:`force_insert <ref-models-force-insert>` parameter is documented |
| elsewhere, but all it means is that a new object will always be created. |
| Normally you won't need to worry about this. However, if your model contains a |
| manual primary key value that you set and if that value already exists in the |
| database, a call to ``create()`` will fail with an |
| :exc:`~django.db.IntegrityError` since primary keys must be unique. Be |
| prepared to handle the exception if you are using manual primary keys. |
| |
| get_or_create |
| ~~~~~~~~~~~~~ |
| |
| .. method:: get_or_create(**kwargs) |
| |
| A convenience method for looking up an object with the given ``kwargs``, |
| creating one if necessary. |
| |
| Returns a tuple of ``(object, created)``, where ``object`` is the retrieved or |
| created object and ``created`` is a boolean specifying whether a new object was |
| created. |
| |
| This is meant as a shortcut to boilerplatish code. For example:: |
| |
| try: |
| obj = Person.objects.get(first_name='John', last_name='Lennon') |
| except Person.DoesNotExist: |
| obj = Person(first_name='John', last_name='Lennon', birthday=date(1940, 10, 9)) |
| obj.save() |
| |
| This pattern gets quite unwieldy as the number of fields in a model goes up. |
| The above example can be rewritten using ``get_or_create()`` like so:: |
| |
| obj, created = Person.objects.get_or_create(first_name='John', last_name='Lennon', |
| defaults={'birthday': date(1940, 10, 9)}) |
| |
| Any keyword arguments passed to ``get_or_create()`` — *except* an optional one |
| called ``defaults`` — will be used in a :meth:`get()` call. If an object is |
| found, ``get_or_create()`` returns a tuple of that object and ``False``. If |
| multiple objects are found, ``get_or_create`` raises |
| :exc:`~django.core.exceptions.MultipleObjectsReturned`. If an object is *not* |
| found, ``get_or_create()`` will instantiate and save a new object, returning a |
| tuple of the new object and ``True``. The new object will be created roughly |
| according to this algorithm:: |
| |
| defaults = kwargs.pop('defaults', {}) |
| params = dict([(k, v) for k, v in kwargs.items() if '__' not in k]) |
| params.update(defaults) |
| obj = self.model(**params) |
| obj.save() |
| |
| In English, that means start with any non-``'defaults'`` keyword argument that |
| doesn't contain a double underscore (which would indicate a non-exact lookup). |
| Then add the contents of ``defaults``, overriding any keys if necessary, and |
| use the result as the keyword arguments to the model class. As hinted at |
| above, this is a simplification of the algorithm that is used, but it contains |
| all the pertinent details. The internal implementation has some more |
| error-checking than this and handles some extra edge-conditions; if you're |
| interested, read the code. |
| |
| If you have a field named ``defaults`` and want to use it as an exact lookup in |
| ``get_or_create()``, just use ``'defaults__exact'``, like so:: |
| |
| Foo.objects.get_or_create(defaults__exact='bar', defaults={'defaults': 'baz'}) |
| |
| The ``get_or_create()`` method has similar error behavior to :meth:`create()` |
| when you're using manually specified primary keys. If an object needs to be |
| created and the key already exists in the database, an |
| :exc:`~django.db.IntegrityError` will be raised. |
| |
| This method is atomic assuming correct usage, correct database configuration, |
| and correct behavior of the underlying database. However, if uniqueness is not |
| enforced at the database level for the ``kwargs`` used in a ``get_or_create`` |
| call (see :attr:`~django.db.models.Field.unique` or |
| :attr:`~django.db.models.Options.unique_together`), this method is prone to a |
| race-condition which can result in multiple rows with the same parameters being |
| inserted simultaneously. |
| |
| If you are using MySQL, be sure to use the ``READ COMMITTED`` isolation level |
| rather than ``REPEATABLE READ`` (the default), otherwise you may see cases |
| where ``get_or_create`` will raise an :exc:`~django.db.IntegrityError` but the |
| object won't appear in a subsequent :meth:`~django.db.models.query.QuerySet.get` |
| call. |
| |
| Finally, a word on using ``get_or_create()`` in Django views. Please make sure |
| to use it only in ``POST`` requests unless you have a good reason not to. |
| ``GET`` requests shouldn't have any effect on data. Instead, use ``POST`` |
| whenever a request to a page has a side effect on your data. For more, see |
| `Safe methods`_ in the HTTP spec. |
| |
| .. _Safe methods: http://www.w3.org/Protocols/rfc2616/rfc2616-sec9.html#sec9.1.1 |
| |
| bulk_create |
| ~~~~~~~~~~~ |
| |
| .. method:: bulk_create(objs, batch_size=None) |
| |
| .. versionadded:: 1.4 |
| |
| This method inserts the provided list of objects into the database in an |
| efficient manner (generally only 1 query, no matter how many objects there |
| are):: |
| |
| >>> Entry.objects.bulk_create([ |
| ... Entry(headline="Django 1.0 Released"), |
| ... Entry(headline="Django 1.1 Announced"), |
| ... Entry(headline="Breaking: Django is awesome") |
| ... ]) |
| |
| This has a number of caveats though: |
| |
| * The model's ``save()`` method will not be called, and the ``pre_save`` and |
| ``post_save`` signals will not be sent. |
| * It does not work with child models in a multi-table inheritance scenario. |
| * If the model's primary key is an :class:`~django.db.models.AutoField` it |
| does not retrieve and set the primary key attribute, as ``save()`` does. |
| |
| The ``batch_size`` parameter controls how many objects are created in single |
| query. The default is to create all objects in one batch, except for SQLite |
| where the default is such that at maximum 999 variables per query is used. |
| |
| .. versionadded:: 1.5 |
| The ``batch_size`` parameter was added in version 1.5. |
| |
| count |
| ~~~~~ |
| |
| .. method:: count() |
| |
| Returns an integer representing the number of objects in the database matching |
| the ``QuerySet``. The ``count()`` method never raises exceptions. |
| |
| Example:: |
| |
| # Returns the total number of entries in the database. |
| Entry.objects.count() |
| |
| # Returns the number of entries whose headline contains 'Lennon' |
| Entry.objects.filter(headline__contains='Lennon').count() |
| |
| A ``count()`` call performs a ``SELECT COUNT(*)`` behind the scenes, so you |
| should always use ``count()`` rather than loading all of the record into Python |
| objects and calling ``len()`` on the result (unless you need to load the |
| objects into memory anyway, in which case ``len()`` will be faster). |
| |
| Depending on which database you're using (e.g. PostgreSQL vs. MySQL), |
| ``count()`` may return a long integer instead of a normal Python integer. This |
| is an underlying implementation quirk that shouldn't pose any real-world |
| problems. |
| |
| in_bulk |
| ~~~~~~~ |
| |
| .. method:: in_bulk(id_list) |
| |
| Takes a list of primary-key values and returns a dictionary mapping each |
| primary-key value to an instance of the object with the given ID. |
| |
| Example:: |
| |
| >>> Blog.objects.in_bulk([1]) |
| {1: <Blog: Beatles Blog>} |
| >>> Blog.objects.in_bulk([1, 2]) |
| {1: <Blog: Beatles Blog>, 2: <Blog: Cheddar Talk>} |
| >>> Blog.objects.in_bulk([]) |
| {} |
| |
| If you pass ``in_bulk()`` an empty list, you'll get an empty dictionary. |
| |
| iterator |
| ~~~~~~~~ |
| |
| .. method:: iterator() |
| |
| Evaluates the ``QuerySet`` (by performing the query) and returns an iterator |
| (see :pep:`234`) over the results. A ``QuerySet`` typically caches its results |
| internally so that repeated evaluations do not result in additional queries. In |
| contrast, ``iterator()`` will read results directly, without doing any caching |
| at the ``QuerySet`` level (internally, the default iterator calls ``iterator()`` |
| and caches the return value). For a ``QuerySet`` which returns a large number of |
| objects that you only need to access once, this can result in better |
| performance and a significant reduction in memory. |
| |
| Note that using ``iterator()`` on a ``QuerySet`` which has already been |
| evaluated will force it to evaluate again, repeating the query. |
| |
| Also, use of ``iterator()`` causes previous ``prefetch_related()`` calls to be |
| ignored since these two optimizations do not make sense together. |
| |
| .. warning:: |
| |
| Some Python database drivers like ``psycopg2`` perform caching if using |
| client side cursors (instantiated with ``connection.cursor()`` and what |
| Django's ORM uses). Using ``iterator()`` does not affect caching at the |
| database driver level. To disable this caching, look at `server side |
| cursors`_. |
| |
| .. _server side cursors: http://initd.org/psycopg/docs/usage.html#server-side-cursors |
| |
| latest |
| ~~~~~~ |
| |
| .. method:: latest(field_name=None) |
| |
| Returns the latest object in the table, by date, using the ``field_name`` |
| provided as the date field. |
| |
| This example returns the latest ``Entry`` in the table, according to the |
| ``pub_date`` field:: |
| |
| Entry.objects.latest('pub_date') |
| |
| If your model's :ref:`Meta <meta-options>` specifies |
| :attr:`~django.db.models.Options.get_latest_by`, you can leave off the |
| ``field_name`` argument to ``latest()``. Django will use the field specified |
| in :attr:`~django.db.models.Options.get_latest_by` by default. |
| |
| Like :meth:`get()`, ``latest()`` raises |
| :exc:`~django.core.exceptions.DoesNotExist` if there is no object with the given |
| parameters. |
| |
| Note ``latest()`` exists purely for convenience and readability. |
| |
| aggregate |
| ~~~~~~~~~ |
| |
| .. method:: aggregate(*args, **kwargs) |
| |
| Returns a dictionary of aggregate values (averages, sums, etc) calculated over |
| the ``QuerySet``. Each argument to ``aggregate()`` specifies a value that will |
| be included in the dictionary that is returned. |
| |
| The aggregation functions that are provided by Django are described in |
| `Aggregation Functions`_ below. |
| |
| Aggregates specified using keyword arguments will use the keyword as the name |
| for the annotation. Anonymous arguments will have a name generated for them |
| based upon the name of the aggregate function and the model field that is being |
| aggregated. |
| |
| For example, when you are working with blog entries, you may want to know the |
| number of authors that have contributed blog entries:: |
| |
| >>> q = Blog.objects.aggregate(Count('entry')) |
| {'entry__count': 16} |
| |
| By using a keyword argument to specify the aggregate function, you can |
| control the name of the aggregation value that is returned:: |
| |
| >>> q = Blog.objects.aggregate(number_of_entries=Count('entry')) |
| {'number_of_entries': 16} |
| |
| For an in-depth discussion of aggregation, see :doc:`the topic guide on |
| Aggregation </topics/db/aggregation>`. |
| |
| exists |
| ~~~~~~ |
| |
| .. method:: exists() |
| |
| Returns ``True`` if the :class:`.QuerySet` contains any results, and ``False`` |
| if not. This tries to perform the query in the simplest and fastest way |
| possible, but it *does* execute nearly the same query as a normal |
| :class:`.QuerySet` query. |
| |
| :meth:`~.QuerySet.exists` is useful for searches relating to both |
| object membership in a :class:`.QuerySet` and to the existence of any objects in |
| a :class:`.QuerySet`, particularly in the context of a large :class:`.QuerySet`. |
| |
| The most efficient method of finding whether a model with a unique field |
| (e.g. ``primary_key``) is a member of a :class:`.QuerySet` is:: |
| |
| entry = Entry.objects.get(pk=123) |
| if some_query_set.filter(pk=entry.pk).exists(): |
| print("Entry contained in queryset") |
| |
| Which will be faster than the following which requires evaluating and iterating |
| through the entire queryset:: |
| |
| if entry in some_query_set: |
| print("Entry contained in QuerySet") |
| |
| And to find whether a queryset contains any items:: |
| |
| if some_query_set.exists(): |
| print("There is at least one object in some_query_set") |
| |
| Which will be faster than:: |
| |
| if some_query_set: |
| print("There is at least one object in some_query_set") |
| |
| ... but not by a large degree (hence needing a large queryset for efficiency |
| gains). |
| |
| Additionally, if a ``some_query_set`` has not yet been evaluated, but you know |
| that it will be at some point, then using ``some_query_set.exists()`` will do |
| more overall work (one query for the existence check plus an extra one to later |
| retrieve the results) than simply using ``bool(some_query_set)``, which |
| retrieves the results and then checks if any were returned. |
| |
| update |
| ~~~~~~ |
| |
| .. method:: update(**kwargs) |
| |
| Performs an SQL update query for the specified fields, and returns |
| the number of rows matched (which may not be equal to the number of rows |
| updated if some rows already have the new value). |
| |
| For example, to turn comments off for all blog entries published in 2010, |
| you could do this:: |
| |
| >>> Entry.objects.filter(pub_date__year=2010).update(comments_on=False) |
| |
| (This assumes your ``Entry`` model has fields ``pub_date`` and ``comments_on``.) |
| |
| You can update multiple fields — there's no limit on how many. For example, |
| here we update the ``comments_on`` and ``headline`` fields:: |
| |
| >>> Entry.objects.filter(pub_date__year=2010).update(comments_on=False, headline='This is old') |
| |
| The ``update()`` method is applied instantly, and the only restriction on the |
| :class:`.QuerySet` that is updated is that it can only update columns in the |
| model's main table, not on related models. You can't do this, for example:: |
| |
| >>> Entry.objects.update(blog__name='foo') # Won't work! |
| |
| Filtering based on related fields is still possible, though:: |
| |
| >>> Entry.objects.filter(blog__id=1).update(comments_on=True) |
| |
| You cannot call ``update()`` on a :class:`.QuerySet` that has had a slice taken |
| or can otherwise no longer be filtered. |
| |
| The ``update()`` method returns the number of affected rows:: |
| |
| >>> Entry.objects.filter(id=64).update(comments_on=True) |
| 1 |
| |
| >>> Entry.objects.filter(slug='nonexistent-slug').update(comments_on=True) |
| 0 |
| |
| >>> Entry.objects.filter(pub_date__year=2010).update(comments_on=False) |
| 132 |
| |
| If you're just updating a record and don't need to do anything with the model |
| object, the most efficient approach is to call ``update()``, rather than |
| loading the model object into memory. For example, instead of doing this:: |
| |
| e = Entry.objects.get(id=10) |
| e.comments_on = False |
| e.save() |
| |
| ...do this:: |
| |
| Entry.objects.filter(id=10).update(comments_on=False) |
| |
| Using ``update()`` also prevents a race condition wherein something might |
| change in your database in the short period of time between loading the object |
| and calling ``save()``. |
| |
| Finally, realize that ``update()`` does an update at the SQL level and, thus, |
| does not call any ``save()`` methods on your models, nor does it emit the |
| :attr:`~django.db.models.signals.pre_save` or |
| :attr:`~django.db.models.signals.post_save` signals (which are a consequence of |
| calling :meth:`Model.save() <django.db.models.Model.save>`). If you want to |
| update a bunch of records for a model that has a custom |
| :meth:`~django.db.models.Model.save()` method, loop over them and call |
| :meth:`~django.db.models.Model.save()`, like this:: |
| |
| for e in Entry.objects.filter(pub_date__year=2010): |
| e.comments_on = False |
| e.save() |
| |
| delete |
| ~~~~~~ |
| |
| .. method:: delete() |
| |
| Performs an SQL delete query on all rows in the :class:`.QuerySet`. The |
| ``delete()`` is applied instantly. You cannot call ``delete()`` on a |
| :class:`.QuerySet` that has had a slice taken or can otherwise no longer be |
| filtered. |
| |
| For example, to delete all the entries in a particular blog:: |
| |
| >>> b = Blog.objects.get(pk=1) |
| |
| # Delete all the entries belonging to this Blog. |
| >>> Entry.objects.filter(blog=b).delete() |
| |
| By default, Django's :class:`~django.db.models.ForeignKey` emulates the SQL |
| constraint ``ON DELETE CASCADE`` — in other words, any objects with foreign |
| keys pointing at the objects to be deleted will be deleted along with them. |
| For example:: |
| |
| blogs = Blog.objects.all() |
| # This will delete all Blogs and all of their Entry objects. |
| blogs.delete() |
| |
| This cascade behavior is customizable via the |
| :attr:`~django.db.models.ForeignKey.on_delete` argument to the |
| :class:`~django.db.models.ForeignKey`. |
| |
| The ``delete()`` method does a bulk delete and does not call any ``delete()`` |
| methods on your models. It does, however, emit the |
| :data:`~django.db.models.signals.pre_delete` and |
| :data:`~django.db.models.signals.post_delete` signals for all deleted objects |
| (including cascaded deletions). |
| |
| .. versionadded:: 1.5 |
| Allow fast-path deletion of objects |
| |
| Django needs to fetch objects into memory to send signals and handle cascades. |
| However, if there are no cascades and no signals, then Django may take a |
| fast-path and delete objects without fetching into memory. For large |
| deletes this can result in significantly reduced memory usage. The amount of |
| executed queries can be reduced, too. |
| |
| ForeignKeys which are set to :attr:`~django.db.models.ForeignKey.on_delete` |
| DO_NOTHING do not prevent taking the fast-path in deletion. |
| |
| Note that the queries generated in object deletion is an implementation |
| detail subject to change. |
| |
| .. _field-lookups: |
| |
| 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 :meth:`filter()`, |
| :meth:`exclude()` and :meth:`get()`. |
| |
| For an introduction, see :ref:`models and database queries documentation |
| <field-lookups-intro>`. |
| |
| .. fieldlookup:: exact |
| |
| exact |
| ~~~~~ |
| |
| Exact match. If the value provided for comparison is ``None``, it will be |
| interpreted as an SQL ``NULL`` (see :lookup:`isnull` for more details). |
| |
| Examples:: |
| |
| Entry.objects.get(id__exact=14) |
| Entry.objects.get(id__exact=None) |
| |
| SQL equivalents:: |
| |
| SELECT ... WHERE id = 14; |
| SELECT ... WHERE id IS NULL; |
| |
| .. admonition:: MySQL comparisons |
| |
| In MySQL, a database table's "collation" setting determines whether |
| ``exact`` comparisons are case-sensitive. This is a database setting, *not* |
| a Django setting. It's possible to configure your MySQL tables to use |
| case-sensitive comparisons, but some trade-offs are involved. For more |
| information about this, see the :ref:`collation section <mysql-collation>` |
| in the :doc:`databases </ref/databases>` documentation. |
| |
| .. fieldlookup:: iexact |
| |
| iexact |
| ~~~~~~ |
| |
| Case-insensitive exact match. |
| |
| Example:: |
| |
| Blog.objects.get(name__iexact='beatles blog') |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE name ILIKE 'beatles blog'; |
| |
| Note this will match ``'Beatles Blog'``, ``'beatles blog'``, ``'BeAtLes |
| BLoG'``, etc. |
| |
| .. admonition:: SQLite users |
| |
| When using the SQLite backend and Unicode (non-ASCII) strings, bear in |
| mind the :ref:`database note <sqlite-string-matching>` about string |
| comparisons. SQLite does not do case-insensitive matching for Unicode |
| strings. |
| |
| .. fieldlookup:: contains |
| |
| contains |
| ~~~~~~~~ |
| |
| Case-sensitive containment test. |
| |
| Example:: |
| |
| Entry.objects.get(headline__contains='Lennon') |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE headline LIKE '%Lennon%'; |
| |
| Note this will match the headline ``'Lennon honored today'`` but not ``'lennon |
| honored today'``. |
| |
| .. admonition:: SQLite users |
| |
| SQLite doesn't support case-sensitive ``LIKE`` statements; ``contains`` |
| acts like ``icontains`` for SQLite. See the :ref:`database note |
| <sqlite-string-matching>` for more information. |
| |
| |
| .. fieldlookup:: icontains |
| |
| icontains |
| ~~~~~~~~~ |
| |
| Case-insensitive containment test. |
| |
| Example:: |
| |
| Entry.objects.get(headline__icontains='Lennon') |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE headline ILIKE '%Lennon%'; |
| |
| .. admonition:: SQLite users |
| |
| When using the SQLite backend and Unicode (non-ASCII) strings, bear in |
| mind the :ref:`database note <sqlite-string-matching>` about string |
| comparisons. |
| |
| .. fieldlookup:: in |
| |
| in |
| ~~ |
| |
| In a given list. |
| |
| Example:: |
| |
| Entry.objects.filter(id__in=[1, 3, 4]) |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE id IN (1, 3, 4); |
| |
| You can also use a queryset to dynamically evaluate the list of values |
| instead of providing a list of literal values:: |
| |
| inner_qs = Blog.objects.filter(name__contains='Cheddar') |
| entries = Entry.objects.filter(blog__in=inner_qs) |
| |
| This queryset will be evaluated as subselect statement:: |
| |
| SELECT ... WHERE blog.id IN (SELECT id FROM ... WHERE NAME LIKE '%Cheddar%') |
| |
| If you pass in a ``ValuesQuerySet`` or ``ValuesListQuerySet`` (the result of |
| calling ``values()`` or ``values_list()`` on a queryset) as the value to an |
| ``__in`` lookup, you need to ensure you are only extracting one field in the |
| result. For example, this will work (filtering on the blog names):: |
| |
| inner_qs = Blog.objects.filter(name__contains='Ch').values('name') |
| entries = Entry.objects.filter(blog__name__in=inner_qs) |
| |
| This example will raise an exception, since the inner query is trying to |
| extract two field values, where only one is expected:: |
| |
| # Bad code! Will raise a TypeError. |
| inner_qs = Blog.objects.filter(name__contains='Ch').values('name', 'id') |
| entries = Entry.objects.filter(blog__name__in=inner_qs) |
| |
| .. admonition:: Performance considerations |
| |
| Be cautious about using nested queries and understand your database |
| server's performance characteristics (if in doubt, benchmark!). Some |
| database backends, most notably MySQL, don't optimize nested queries very |
| well. It is more efficient, in those cases, to extract a list of values |
| and then pass that into the second query. That is, execute two queries |
| instead of one:: |
| |
| values = Blog.objects.filter( |
| name__contains='Cheddar').values_list('pk', flat=True) |
| entries = Entry.objects.filter(blog__in=list(values)) |
| |
| Note the ``list()`` call around the Blog ``QuerySet`` to force execution of |
| the first query. Without it, a nested query would be executed, because |
| :ref:`querysets-are-lazy`. |
| |
| .. fieldlookup:: gt |
| |
| gt |
| ~~ |
| |
| Greater than. |
| |
| Example:: |
| |
| Entry.objects.filter(id__gt=4) |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE id > 4; |
| |
| .. fieldlookup:: gte |
| |
| gte |
| ~~~ |
| |
| Greater than or equal to. |
| |
| .. fieldlookup:: lt |
| |
| lt |
| ~~ |
| |
| Less than. |
| |
| .. fieldlookup:: lte |
| |
| lte |
| ~~~ |
| |
| Less than or equal to. |
| |
| .. fieldlookup:: startswith |
| |
| startswith |
| ~~~~~~~~~~ |
| |
| Case-sensitive starts-with. |
| |
| Example:: |
| |
| Entry.objects.filter(headline__startswith='Will') |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE headline LIKE 'Will%'; |
| |
| SQLite doesn't support case-sensitive ``LIKE`` statements; ``startswith`` acts |
| like ``istartswith`` for SQLite. |
| |
| .. fieldlookup:: istartswith |
| |
| istartswith |
| ~~~~~~~~~~~ |
| |
| Case-insensitive starts-with. |
| |
| Example:: |
| |
| Entry.objects.filter(headline__istartswith='will') |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE headline ILIKE 'Will%'; |
| |
| .. admonition:: SQLite users |
| |
| When using the SQLite backend and Unicode (non-ASCII) strings, bear in |
| mind the :ref:`database note <sqlite-string-matching>` about string |
| comparisons. |
| |
| .. fieldlookup:: endswith |
| |
| endswith |
| ~~~~~~~~ |
| |
| Case-sensitive ends-with. |
| |
| Example:: |
| |
| Entry.objects.filter(headline__endswith='cats') |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE headline LIKE '%cats'; |
| |
| .. admonition:: SQLite users |
| |
| SQLite doesn't support case-sensitive ``LIKE`` statements; ``endswith`` |
| acts like ``iendswith`` for SQLite. Refer to the :ref:`database note |
| <sqlite-string-matching>` documentation for more. |
| |
| .. fieldlookup:: iendswith |
| |
| iendswith |
| ~~~~~~~~~ |
| |
| Case-insensitive ends-with. |
| |
| Example:: |
| |
| Entry.objects.filter(headline__iendswith='will') |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE headline ILIKE '%will' |
| |
| .. admonition:: SQLite users |
| |
| When using the SQLite backend and Unicode (non-ASCII) strings, bear in |
| mind the :ref:`database note <sqlite-string-matching>` about string |
| comparisons. |
| |
| .. fieldlookup:: range |
| |
| range |
| ~~~~~ |
| |
| Range test (inclusive). |
| |
| Example:: |
| |
| start_date = datetime.date(2005, 1, 1) |
| end_date = datetime.date(2005, 3, 31) |
| Entry.objects.filter(pub_date__range=(start_date, end_date)) |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE pub_date BETWEEN '2005-01-01' and '2005-03-31'; |
| |
| You can use ``range`` anywhere you can use ``BETWEEN`` in SQL — for dates, |
| numbers and even characters. |
| |
| .. warning:: |
| |
| Filtering a ``DateTimeField`` with dates won't include items on the last |
| day, because the bounds are interpreted as "0am on the given date". If |
| ``pub_date`` was a ``DateTimeField``, the above expression would be turned |
| into this SQL:: |
| |
| SELECT ... WHERE pub_date BETWEEN '2005-01-01 00:00:00' and '2005-03-31 00:00:00'; |
| |
| Generally speaking, you can't mix dates and datetimes. |
| |
| .. fieldlookup:: year |
| |
| year |
| ~~~~ |
| |
| For date/datetime fields, exact year match. Takes a four-digit year. |
| |
| Example:: |
| |
| Entry.objects.filter(pub_date__year=2005) |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE pub_date BETWEEN '2005-01-01' AND '2005-12-31'; |
| |
| (The exact SQL syntax varies for each database engine.) |
| |
| .. fieldlookup:: month |
| |
| month |
| ~~~~~ |
| |
| For date and datetime fields, an exact month match. Takes an integer 1 |
| (January) through 12 (December). |
| |
| Example:: |
| |
| Entry.objects.filter(pub_date__month=12) |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE EXTRACT('month' FROM pub_date) = '12'; |
| |
| (The exact SQL syntax varies for each database engine.) |
| |
| .. fieldlookup:: day |
| |
| day |
| ~~~ |
| |
| For date and datetime fields, an exact day match. |
| |
| Example:: |
| |
| Entry.objects.filter(pub_date__day=3) |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE EXTRACT('day' FROM pub_date) = '3'; |
| |
| (The exact SQL syntax varies for each database engine.) |
| |
| Note this will match any record with a pub_date on the third day of the month, |
| such as January 3, July 3, etc. |
| |
| .. fieldlookup:: week_day |
| |
| week_day |
| ~~~~~~~~ |
| |
| For date and datetime fields, a 'day of the week' match. |
| |
| Takes an integer value representing the day of week from 1 (Sunday) to 7 |
| (Saturday). |
| |
| Example:: |
| |
| Entry.objects.filter(pub_date__week_day=2) |
| |
| (No equivalent SQL code fragment is included for this lookup because |
| implementation of the relevant query varies among different database engines.) |
| |
| Note this will match any record with a ``pub_date`` that falls on a Monday (day |
| 2 of the week), regardless of the month or year in which it occurs. Week days |
| are indexed with day 1 being Sunday and day 7 being Saturday. |
| |
| .. warning:: |
| |
| When :doc:`time zone support </topics/i18n/timezones>` is enabled, Django |
| uses UTC in the database connection, which means the ``year``, ``month``, |
| ``day`` and ``week_day`` lookups are performed in UTC. This is a known |
| limitation of the current implementation. |
| |
| .. fieldlookup:: isnull |
| |
| isnull |
| ~~~~~~ |
| |
| Takes either ``True`` or ``False``, which correspond to SQL queries of |
| ``IS NULL`` and ``IS NOT NULL``, respectively. |
| |
| Example:: |
| |
| Entry.objects.filter(pub_date__isnull=True) |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE pub_date IS NULL; |
| |
| .. fieldlookup:: search |
| |
| search |
| ~~~~~~ |
| |
| A boolean full-text search, taking advantage of full-text indexing. This is |
| like :lookup:`contains` but is significantly faster due to full-text indexing. |
| |
| Example:: |
| |
| Entry.objects.filter(headline__search="+Django -jazz Python") |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE MATCH(tablename, headline) AGAINST (+Django -jazz Python IN BOOLEAN MODE); |
| |
| Note this is only available in MySQL and requires direct manipulation of the |
| database to add the full-text index. By default Django uses BOOLEAN MODE for |
| full text searches. See the `MySQL documentation`_ for additional details. |
| |
| .. _MySQL documentation: http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html |
| |
| |
| .. fieldlookup:: regex |
| |
| regex |
| ~~~~~ |
| |
| Case-sensitive regular expression match. |
| |
| The regular expression syntax is that of the database backend in use. |
| In the case of SQLite, which has no built in regular expression support, |
| this feature is provided by a (Python) user-defined REGEXP function, and |
| the regular expression syntax is therefore that of Python's ``re`` module. |
| |
| Example:: |
| |
| Entry.objects.get(title__regex=r'^(An?|The) +') |
| |
| SQL equivalents:: |
| |
| SELECT ... WHERE title REGEXP BINARY '^(An?|The) +'; -- MySQL |
| |
| SELECT ... WHERE REGEXP_LIKE(title, '^(an?|the) +', 'c'); -- Oracle |
| |
| SELECT ... WHERE title ~ '^(An?|The) +'; -- PostgreSQL |
| |
| SELECT ... WHERE title REGEXP '^(An?|The) +'; -- SQLite |
| |
| Using raw strings (e.g., ``r'foo'`` instead of ``'foo'``) for passing in the |
| regular expression syntax is recommended. |
| |
| .. fieldlookup:: iregex |
| |
| iregex |
| ~~~~~~ |
| |
| Case-insensitive regular expression match. |
| |
| Example:: |
| |
| Entry.objects.get(title__iregex=r'^(an?|the) +') |
| |
| SQL equivalents:: |
| |
| SELECT ... WHERE title REGEXP '^(an?|the) +'; -- MySQL |
| |
| SELECT ... WHERE REGEXP_LIKE(title, '^(an?|the) +', 'i'); -- Oracle |
| |
| SELECT ... WHERE title ~* '^(an?|the) +'; -- PostgreSQL |
| |
| SELECT ... WHERE title REGEXP '(?i)^(an?|the) +'; -- SQLite |
| |
| .. _aggregation-functions: |
| |
| Aggregation functions |
| --------------------- |
| |
| .. currentmodule:: django.db.models |
| |
| Django provides the following aggregation functions in the |
| ``django.db.models`` module. For details on how to use these |
| aggregate functions, see |
| :doc:`the topic guide on aggregation </topics/db/aggregation>`. |
| |
| Avg |
| ~~~ |
| |
| .. class:: Avg(field) |
| |
| Returns the mean value of the given field, which must be numeric. |
| |
| * Default alias: ``<field>__avg`` |
| * Return type: ``float`` |
| |
| Count |
| ~~~~~ |
| |
| .. class:: Count(field, distinct=False) |
| |
| Returns the number of objects that are related through the provided field. |
| |
| * Default alias: ``<field>__count`` |
| * Return type: ``int`` |
| |
| Has one optional argument: |
| |
| .. attribute:: distinct |
| |
| If ``distinct=True``, the count will only include unique instances. |
| This is the SQL equivalent of ``COUNT(DISTINCT <field>)``. The default |
| value is ``False``. |
| |
| Max |
| ~~~ |
| |
| .. class:: Max(field) |
| |
| Returns the maximum value of the given field. |
| |
| * Default alias: ``<field>__max`` |
| * Return type: same as input field |
| |
| Min |
| ~~~ |
| |
| .. class:: Min(field) |
| |
| Returns the minimum value of the given field. |
| |
| * Default alias: ``<field>__min`` |
| * Return type: same as input field |
| |
| StdDev |
| ~~~~~~ |
| |
| .. class:: StdDev(field, sample=False) |
| |
| Returns the standard deviation of the data in the provided field. |
| |
| * Default alias: ``<field>__stddev`` |
| * Return type: ``float`` |
| |
| Has one optional argument: |
| |
| .. attribute:: sample |
| |
| By default, ``StdDev`` returns the population standard deviation. However, |
| if ``sample=True``, the return value will be the sample standard deviation. |
| |
| .. admonition:: SQLite |
| |
| SQLite doesn't provide ``StdDev`` out of the box. An implementation |
| is available as an extension module for SQLite. Consult the `SQlite |
| documentation`_ for instructions on obtaining and installing this |
| extension. |
| |
| Sum |
| ~~~ |
| |
| .. class:: Sum(field) |
| |
| Computes the sum of all values of the given field. |
| |
| * Default alias: ``<field>__sum`` |
| * Return type: same as input field |
| |
| Variance |
| ~~~~~~~~ |
| |
| .. class:: Variance(field, sample=False) |
| |
| Returns the variance of the data in the provided field. |
| |
| * Default alias: ``<field>__variance`` |
| * Return type: ``float`` |
| |
| Has one optional argument: |
| |
| .. attribute:: sample |
| |
| By default, ``Variance`` returns the population variance. However, |
| if ``sample=True``, the return value will be the sample variance. |
| |
| .. admonition:: SQLite |
| |
| SQLite doesn't provide ``Variance`` out of the box. An implementation |
| is available as an extension module for SQLite. Consult the `SQlite |
| documentation`_ for instructions on obtaining and installing this |
| extension. |
| |
| .. _SQLite documentation: http://www.sqlite.org/contrib |
