diff options
Diffstat (limited to 'parts/django/docs/ref/models/querysets.txt')
| -rw-r--r-- | parts/django/docs/ref/models/querysets.txt | 1888 |
1 files changed, 1888 insertions, 0 deletions
diff --git a/parts/django/docs/ref/models/querysets.txt b/parts/django/docs/ref/models/querysets.txt new file mode 100644 index 0000000..9f0de1f --- /dev/null +++ b/parts/django/docs/ref/models/querysets.txt @@ -0,0 +1,1888 @@ +====================== +QuerySet API reference +====================== + +.. currentmodule:: django.db.models.QuerySet + +This document describes the details of the ``QuerySet`` API. It builds on the +material presented in the :doc:`model </topics/db/models>` and :doc:`database +query </topics/db/queries>` guides, so you'll probably want to read and +understand those documents before reading this one. + +Throughout this reference we'll use the :ref:`example Weblog models +<queryset-model-example>` presented in the :doc:`database query guide +</topics/db/queries>`. + +.. _when-querysets-are-evaluated: + +When QuerySets are evaluated +============================ + +Internally, a ``QuerySet`` can be constructed, filtered, sliced, and generally +passed around without actually hitting the database. No database activity +actually occurs until you do something to evaluate the queryset. + +You can evaluate a ``QuerySet`` in the following ways: + + * **Iteration.** A ``QuerySet`` is iterable, and it executes its database + query the first time you iterate over it. For example, this will print + the headline of all entries in the database:: + + for e in Entry.objects.all(): + print e.headline + + * **Slicing.** As explained in :ref:`limiting-querysets`, a ``QuerySet`` can + be sliced, using Python's array-slicing syntax. Usually slicing a + ``QuerySet`` returns another (unevaluated) ``QuerySet``, but Django will + execute the database query if you use the "step" parameter of slice + syntax. + + * **Pickling/Caching.** See the following section for details of what + is involved when `pickling QuerySets`_. The important thing for the + purposes of this section is that the results are read from the database. + + * **repr().** A ``QuerySet`` is evaluated when you call ``repr()`` on it. + This is for convenience in the Python interactive interpreter, so you can + immediately see your results when using the API interactively. + + * **len().** A ``QuerySet`` is evaluated when you call ``len()`` on it. + This, as you might expect, returns the length of the result list. + + Note: *Don't* use ``len()`` on ``QuerySet``\s if all you want to do is + determine the number of records in the set. It's much more efficient to + handle a count at the database level, using SQL's ``SELECT COUNT(*)``, + and Django provides a ``count()`` method for precisely this reason. See + ``count()`` below. + + * **list().** Force evaluation of a ``QuerySet`` by calling ``list()`` on + it. For example:: + + entry_list = list(Entry.objects.all()) + + Be warned, though, that this could have a large memory overhead, because + Django will load each element of the list into memory. In contrast, + iterating over a ``QuerySet`` will take advantage of your database to + load data and instantiate objects only as you need them. + + * **bool().** Testing a ``QuerySet`` in a boolean context, such as using + ``bool()``, ``or``, ``and`` or an ``if`` statement, will cause the query + to be executed. If there is at least one result, the ``QuerySet`` is + ``True``, otherwise ``False``. For example:: + + if Entry.objects.filter(headline="Test"): + print "There is at least one Entry with the headline Test" + + Note: *Don't* use this if all you want to do is determine if at least one + result exists, and don't need the actual objects. It's more efficient to + use ``exists()`` (see below). + +.. _pickling QuerySets: + +Pickling QuerySets +------------------ + +If you pickle_ a ``QuerySet``, this will force all the results to be loaded +into memory prior to pickling. Pickling is usually used as a precursor to +caching and when the cached queryset is reloaded, you want the results to +already be present and ready for use (reading from the database can take some +time, defeating the purpose of caching). This means that when you unpickle a +``QuerySet``, it contains the results at the moment it was pickled, rather +than the results that are currently in the database. + +If you only want to pickle the necessary information to recreate the +``QuerySet`` from the database at a later time, pickle the ``query`` attribute +of the ``QuerySet``. You can then recreate the original ``QuerySet`` (without +any results loaded) using some code like this:: + + >>> import pickle + >>> query = pickle.loads(s) # Assuming 's' is the pickled string. + >>> qs = MyModel.objects.all() + >>> qs.query = query # Restore the original 'query'. + +The ``query`` attribute is an opaque object. It represents the internals of +the query construction and is not part of the public API. However, it is safe +(and fully supported) to pickle and unpickle the attribute's contents as +described here. + +.. admonition:: You can't share pickles between versions + + Pickles of QuerySets are only valid for the version of Django that + was used to generate them. If you generate a pickle using Django + version N, there is no guarantee that pickle will be readable with + Django version N+1. Pickles should not be used as part of a long-term + archival strategy. + +.. _pickle: http://docs.python.org/library/pickle.html + +.. _queryset-api: + +QuerySet API +============ + +Though you usually won't create one manually -- you'll go through a +:class:`Manager` -- here's the formal declaration of a ``QuerySet``: + +.. class:: QuerySet([model=None]) + +Usually when you'll interact with a ``QuerySet`` you'll use it by :ref:`chaining +filters <chaining-filters>`. To make this work, most ``QuerySet`` methods return new querysets. + +Methods that return new QuerySets +--------------------------------- + +Django provides a range of ``QuerySet`` refinement methods that modify either +the types of results returned by the ``QuerySet`` or the way its SQL query is +executed. + +filter +~~~~~~ + +.. method:: filter(**kwargs) + +Returns a new ``QuerySet`` containing objects that match the given lookup +parameters. + +The lookup parameters (``**kwargs``) should be in the format described in +`Field lookups`_ below. Multiple parameters are joined via ``AND`` in the +underlying SQL statement. + +exclude +~~~~~~~ + +.. method:: exclude(**kwargs) + +Returns a new ``QuerySet`` containing objects that do *not* match the given +lookup parameters. + +The lookup parameters (``**kwargs``) should be in the format described in +`Field lookups`_ below. Multiple parameters are joined via ``AND`` in the +underlying SQL statement, and the whole thing is enclosed in a ``NOT()``. + +This example excludes all entries whose ``pub_date`` is later than 2005-1-3 +AND whose ``headline`` is "Hello":: + + Entry.objects.exclude(pub_date__gt=datetime.date(2005, 1, 3), headline='Hello') + +In SQL terms, that evaluates to:: + + SELECT ... + WHERE NOT (pub_date > '2005-1-3' AND headline = 'Hello') + +This example excludes all entries whose ``pub_date`` is later than 2005-1-3 +OR whose headline is "Hello":: + + Entry.objects.exclude(pub_date__gt=datetime.date(2005, 1, 3)).exclude(headline='Hello') + +In SQL terms, that evaluates to:: + + SELECT ... + WHERE NOT pub_date > '2005-1-3' + AND NOT headline = 'Hello' + +Note the second example is more restrictive. + +annotate +~~~~~~~~ + +.. method:: annotate(*args, **kwargs) + +.. versionadded:: 1.1 + +Annotates each object in the ``QuerySet`` with the provided list of +aggregate values (averages, sums, etc) that have been computed over +the objects that are related to the objects in the ``QuerySet``. +Each argument to ``annotate()`` is an annotation that will be added +to each object in the ``QuerySet`` that is returned. + +The aggregation functions that are provided by Django are described +in `Aggregation Functions`_ below. + +Annotations specified using keyword arguments will use the keyword as +the alias for the annotation. Anonymous arguments will have an alias +generated for them based upon the name of the aggregate function and +the model field that is being aggregated. + +For example, if you were manipulating a list of blogs, you may want +to determine how many entries have been made in each blog:: + + >>> q = Blog.objects.annotate(Count('entry')) + # The name of the first blog + >>> q[0].name + 'Blogasaurus' + # The number of entries on the first blog + >>> q[0].entry__count + 42 + +The ``Blog`` model doesn't define an ``entry__count`` attribute by itself, +but by using a keyword argument to specify the aggregate function, you can +control the name of the annotation:: + + >>> q = Blog.objects.annotate(number_of_entries=Count('entry')) + # The number of entries on the first blog, using the name provided + >>> q[0].number_of_entries + 42 + +For an in-depth discussion of aggregation, see :doc:`the topic guide on +Aggregation </topics/db/aggregation>`. + +order_by +~~~~~~~~ + +.. method:: order_by(*fields) + +By default, results returned by a ``QuerySet`` are ordered by the ordering +tuple given by the ``ordering`` option in the model's ``Meta``. You can +override this on a per-``QuerySet`` basis by using the ``order_by`` method. + +Example:: + + Entry.objects.filter(pub_date__year=2005).order_by('-pub_date', 'headline') + +The result above will be ordered by ``pub_date`` descending, then by +``headline`` ascending. The negative sign in front of ``"-pub_date"`` indicates +*descending* order. Ascending order is implied. To order randomly, use ``"?"``, +like so:: + + Entry.objects.order_by('?') + +Note: ``order_by('?')`` queries may be expensive and slow, depending on the +database backend you're using. + +To order by a field in a different model, use the same syntax as when you are +querying across model relations. That is, the name of the field, followed by a +double underscore (``__``), followed by the name of the field in the new model, +and so on for as many models as you want to join. For example:: + + Entry.objects.order_by('blog__name', 'headline') + +If you try to order by a field that is a relation to another model, Django will +use the default ordering on the related model (or order by the related model's +primary key if there is no ``Meta.ordering`` specified. For example:: + + Entry.objects.order_by('blog') + +...is identical to:: + + Entry.objects.order_by('blog__id') + +...since the ``Blog`` model has no default ordering specified. + +Be cautious when ordering by fields in related models if you are also using +``distinct()``. See the note in :meth:`distinct` for an explanation of how +related model ordering can change the expected results. + +It is permissible to specify a multi-valued field to order the results by (for +example, a ``ManyToMany`` field). Normally this won't be a sensible thing to +do and it's really an advanced usage feature. However, if you know that your +queryset's filtering or available data implies that there will only be one +ordering piece of data for each of the main items you are selecting, the +ordering may well be exactly what you want to do. Use ordering on multi-valued +fields with care and make sure the results are what you expect. + +.. versionadded:: 1.0 + +The syntax for ordering across related models has changed. See the `Django 0.96 +documentation`_ for the old behaviour. + +.. _Django 0.96 documentation: http://www.djangoproject.com/documentation/0.96/model-api/#floatfield + +There's no way to specify whether ordering should be case sensitive. With +respect to case-sensitivity, Django will order results however your database +backend normally orders them. + +If you don't want any ordering to be applied to a query, not even the default +ordering, call ``order_by()`` with no parameters. + +.. versionadded:: 1.1 + +You can tell if a query is ordered or not by checking the +:attr:`QuerySet.ordered` attribute, which will be ``True`` if the +``QuerySet`` has been ordered in any way. + +reverse +~~~~~~~ + +.. method:: reverse() + +.. versionadded:: 1.0 + +Use the ``reverse()`` method to reverse the order in which a queryset's +elements are returned. Calling ``reverse()`` a second time restores the +ordering back to the normal direction. + +To retrieve the ''last'' five items in a queryset, you could do this:: + + my_queryset.reverse()[:5] + +Note that this is not quite the same as slicing from the end of a sequence in +Python. The above example will return the last item first, then the +penultimate item and so on. If we had a Python sequence and looked at +``seq[-5:]``, we would see the fifth-last item first. Django doesn't support +that mode of access (slicing from the end), because it's not possible to do it +efficiently in SQL. + +Also, note that ``reverse()`` should generally only be called on a +``QuerySet`` which has a defined ordering (e.g., when querying against +a model which defines a default ordering, or when using +``order_by()``). If no such ordering is defined for a given +``QuerySet``, calling ``reverse()`` on it has no real effect (the +ordering was undefined prior to calling ``reverse()``, and will remain +undefined afterward). + +distinct +~~~~~~~~ + +.. method:: distinct() + +Returns a new ``QuerySet`` that uses ``SELECT DISTINCT`` in its SQL query. This +eliminates duplicate rows from the query results. + +By default, a ``QuerySet`` will not eliminate duplicate rows. In practice, this +is rarely a problem, because simple queries such as ``Blog.objects.all()`` +don't introduce the possibility of duplicate result rows. However, if your +query spans multiple tables, it's possible to get duplicate results when a +``QuerySet`` is evaluated. That's when you'd use ``distinct()``. + +.. note:: + Any fields used in an :meth:`order_by` call are included in the SQL + ``SELECT`` columns. This can sometimes lead to unexpected results when + used in conjunction with ``distinct()``. If you order by fields from a + related model, those fields will be added to the selected columns and they + may make otherwise duplicate rows appear to be distinct. Since the extra + columns don't appear in the returned results (they are only there to + support ordering), it sometimes looks like non-distinct results are being + returned. + + Similarly, if you use a ``values()`` query to restrict the columns + selected, the columns used in any ``order_by()`` (or default model + ordering) will still be involved and may affect uniqueness of the results. + + The moral here is that if you are using ``distinct()`` be careful about + ordering by related models. Similarly, when using ``distinct()`` and + ``values()`` together, be careful when ordering by fields not in the + ``values()`` call. + +values +~~~~~~ + +.. method:: values(*fields) + +Returns a ``ValuesQuerySet`` -- a ``QuerySet`` that returns dictionaries when +used as an iterable, rather than model-instance objects. + +Each of those dictionaries represents an object, with the keys corresponding to +the attribute names of model objects. + +This example compares the dictionaries of ``values()`` with the normal model +objects:: + + # This list contains a Blog object. + >>> Blog.objects.filter(name__startswith='Beatles') + [<Blog: Beatles Blog>] + + # This list contains a dictionary. + >>> Blog.objects.filter(name__startswith='Beatles').values() + [{'id': 1, 'name': 'Beatles Blog', 'tagline': 'All the latest Beatles news.'}] + +``values()`` takes optional positional arguments, ``*fields``, which specify +field names to which the ``SELECT`` should be limited. If you specify the +fields, each dictionary will contain only the field keys/values for the fields +you specify. If you don't specify the fields, each dictionary will contain a +key and value for every field in the database table. + +Example:: + + >>> Blog.objects.values() + [{'id': 1, 'name': 'Beatles Blog', 'tagline': 'All the latest Beatles news.'}], + >>> Blog.objects.values('id', 'name') + [{'id': 1, 'name': 'Beatles Blog'}] + +A couple of subtleties that are worth mentioning: + + * The ``values()`` method does not return anything for + :class:`~django.db.models.ManyToManyField` attributes and will raise an + error if you try to pass in this type of field to it. + * If you have a field called ``foo`` that is a + :class:`~django.db.models.ForeignKey`, the default ``values()`` call + will return a dictionary key called ``foo_id``, since this is the name + of the hidden model attribute that stores the actual value (the ``foo`` + attribute refers to the related model). When you are calling + ``values()`` and passing in field names, you can pass in either ``foo`` + or ``foo_id`` and you will get back the same thing (the dictionary key + will match the field name you passed in). + + For example:: + + >>> Entry.objects.values() + [{'blog_id': 1, 'headline': u'First Entry', ...}, ...] + + >>> Entry.objects.values('blog') + [{'blog': 1}, ...] + + >>> Entry.objects.values('blog_id') + [{'blog_id': 1}, ...] + + * When using ``values()`` together with ``distinct()``, be aware that + ordering can affect the results. See the note in :meth:`distinct` for + details. + + * If you use a ``values()`` clause after an ``extra()`` clause, + any fields defined by a ``select`` argument in the ``extra()`` + must be explicitly included in the ``values()`` clause. However, + if the ``extra()`` clause is used after the ``values()``, the + fields added by the select will be included automatically. + +.. versionadded:: 1.0 + +Previously, it was not possible to pass ``blog_id`` to ``values()`` in the above +example, only ``blog``. + +A ``ValuesQuerySet`` is useful when you know you're only going to need values +from a small number of the available fields and you won't need the +functionality of a model instance object. It's more efficient to select only +the fields you need to use. + +Finally, note a ``ValuesQuerySet`` is a subclass of ``QuerySet``, so it has all +methods of ``QuerySet``. You can call ``filter()`` on it, or ``order_by()``, or +whatever. Yes, that means these two calls are identical:: + + Blog.objects.values().order_by('id') + Blog.objects.order_by('id').values() + +The people who made Django prefer to put all the SQL-affecting methods first, +followed (optionally) by any output-affecting methods (such as ``values()``), +but it doesn't really matter. This is your chance to really flaunt your +individualism. + +values_list +~~~~~~~~~~~ + +.. method:: values_list(*fields) + +.. versionadded:: 1.0 + +This is similar to ``values()`` except that instead of returning dictionaries, +it returns tuples when iterated over. Each tuple contains the value from the +respective field passed into the ``values_list()`` call -- so the first item is +the first field, etc. For example:: + + >>> Entry.objects.values_list('id', 'headline') + [(1, u'First entry'), ...] + +If you only pass in a single field, you can also pass in the ``flat`` +parameter. If ``True``, this will mean the returned results are single values, +rather than one-tuples. An example should make the difference clearer:: + + >>> Entry.objects.values_list('id').order_by('id') + [(1,), (2,), (3,), ...] + + >>> Entry.objects.values_list('id', flat=True).order_by('id') + [1, 2, 3, ...] + +It is an error to pass in ``flat`` when there is more than one field. + +If you don't pass any values to ``values_list()``, it will return all the +fields in the model, in the order they were declared. + +dates +~~~~~ + +.. method:: dates(field, kind, order='ASC') + +Returns a ``DateQuerySet`` -- a ``QuerySet`` that evaluates to a list of +``datetime.datetime`` objects representing all available dates of a particular +kind within the contents of the ``QuerySet``. + +``field`` should be the name of a ``DateField`` or ``DateTimeField`` of your +model. + +``kind`` should be either ``"year"``, ``"month"`` or ``"day"``. Each +``datetime.datetime`` object in the result list is "truncated" to the given +``type``. + + * ``"year"`` returns a list of all distinct year values for the field. + * ``"month"`` returns a list of all distinct year/month values for the field. + * ``"day"`` returns a list of all distinct year/month/day values for the field. + +``order``, which defaults to ``'ASC'``, should be either ``'ASC'`` or +``'DESC'``. This specifies how to order the results. + +Examples:: + + >>> Entry.objects.dates('pub_date', 'year') + [datetime.datetime(2005, 1, 1)] + >>> Entry.objects.dates('pub_date', 'month') + [datetime.datetime(2005, 2, 1), datetime.datetime(2005, 3, 1)] + >>> Entry.objects.dates('pub_date', 'day') + [datetime.datetime(2005, 2, 20), datetime.datetime(2005, 3, 20)] + >>> Entry.objects.dates('pub_date', 'day', order='DESC') + [datetime.datetime(2005, 3, 20), datetime.datetime(2005, 2, 20)] + >>> Entry.objects.filter(headline__contains='Lennon').dates('pub_date', 'day') + [datetime.datetime(2005, 3, 20)] + +none +~~~~ + +.. method:: none() + +.. versionadded:: 1.0 + +Returns an ``EmptyQuerySet`` -- a ``QuerySet`` 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() + +.. versionadded:: 1.0 + +Returns a *copy* of the current ``QuerySet`` (or ``QuerySet`` subclass you +pass in). This can be useful in some situations where you might want to pass +in either a model manager or a ``QuerySet`` and do further filtering on the +result. You can safely call ``all()`` on either object and then you'll +definitely have a ``QuerySet`` to work with. + +.. _select-related: + +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): + # ... + + 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, in situations with deeply nested +sets of relationships ``select_related()`` can sometimes end up following "too +many" relations, and can generate queries so large that they end up being slow. + +In these situations, you can use the ``depth`` argument to ``select_related()`` +to control how many "levels" of relations ``select_related()`` will actually +follow:: + + 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. + +Sometimes you only want to access specific models that are related to your root +model, not all of the related models. In these cases, you can pass the related +field names to ``select_related()`` and it will only follow those relations. +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 ``ForeignKey`` or ``OneToOneField`` relation in +the list of fields passed to ``select_related``. Ths includes foreign +keys that have ``null=True`` (unlike the default ``select_related()`` +call). It's an error to use both a list of fields and the ``depth`` +parameter in the same ``select_related()`` call, since they are +conflicting options. + +.. versionadded:: 1.0 + +Both the ``depth`` argument and the ability to specify field names in the call +to ``select_related()`` are new in Django version 1.0. + +.. versionchanged:: 1.2 + +You can also refer to the reverse direction of a ``OneToOneFields`` in +the list of fields passed to ``select_related`` -- that is, you can traverse +a ``OneToOneField`` back to the object on which the field is defined. Instead +of specifying the field name, use the ``related_name`` for the field on the +related object. + +``OneToOneFields`` will not be traversed in the reverse direction if you +are performing a depth-based ``select_related``. + +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 parenthesis 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. + + .. versionadded:: 1.0 + + 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 ``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=['id IN (3, 4, 5, 20)']) + + ...translates (roughly) into the following SQL:: + + SELECT * FROM blog_entry WHERE id IN (3, 4, 5, 20); + + 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 ``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']) + +defer +~~~~~ + +.. method:: defer(*fields) + +.. versionadded:: 1.1 + +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 know you don't +need those particular fields, 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 ``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) + +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 +``select_related()`` to retrieve other models at the same time you shouldn't +defer the loading of the field that connects from the primary model to the +related one (at the moment, that doesn't raise an error, but it will +eventually). + +.. note:: + + The ``defer()`` method (and its cousin, ``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. When you are initially developing your applications, don't + bother using ``defer()``; leave it until your query construction has + settled down and you understand where the hot-points are. + +only +~~~~ + +.. method:: only(*fields) + +.. versionadded:: 1.1 + +The ``only()`` method is more or less the opposite of ``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 could result in simpler +code. + +If 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") + +using +~~~~~ + +.. method:: using(alias) + +.. versionadded:: 1.2 + +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') + + +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 ``MultipleObjectsReturned`` if more than one object was +found. The ``MultipleObjectsReturned`` exception is an attribute of the model +class. + +``get()`` raises a ``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 ``DoesNotExist`` exception inherits from +``django.core.exceptions.ObjectDoesNotExist``, so you can target multiple +``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:`IntegrityError` since +primary keys must be unique. So remember to 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 and is mostly useful for +data-import scripts. 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 ``get()`` call. If an object is found, +``get_or_create()`` returns a tuple of that object and ``False``. 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 behaviour to ``create()`` +when you are using manually specified primary keys. If an object needs to be +created and the key already exists in the database, an ``IntegrityError`` will +be raised. + +Finally, a word on using ``get_or_create()`` in Django views. As mentioned +earlier, ``get_or_create()`` is mostly useful in scripts that need to parse +data and create new records if existing ones aren't available. But if you need +to use ``get_or_create()`` in a view, 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; 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 + +count +~~~~~ + +.. method:: count() + +Returns an integer representing the number of objects in the database matching +the ``QuerySet``. ``count()`` 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() + +``count()`` 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`_ over the results. A ``QuerySet`` typically caches its +results internally so that repeated evaluations do not result in +additional queries; ``iterator()`` will instead read results directly, +without doing any caching at the ``QuerySet`` level. For a +``QuerySet`` which returns a large number of objects, this often +results 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. + +.. _iterator: http://www.python.org/dev/peps/pep-0234/ + +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 ``Meta`` specifies ``get_latest_by``, you can leave off the +``field_name`` argument to ``latest()``. Django will use the field specified in +``get_latest_by`` by default. + +Like ``get()``, ``latest()`` raises ``DoesNotExist`` if an object doesn't +exist with the given parameters. + +Note ``latest()`` exists purely for convenience and readability. + +aggregate +~~~~~~~~~ + +.. method:: aggregate(*args, **kwargs) + +.. versionadded:: 1.1 + +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 an name +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 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() + +.. versionadded:: 1.2 + +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. This means that calling +:meth:`QuerySet.exists()` is faster than ``bool(some_query_set)``, but not by +a large degree. If ``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 (an additional query) than simply using +``bool(some_query_set)``. + +update +~~~~~~ + +.. method:: update(**kwargs) + +Performs an SQL update query for the specified fields, and returns +the number of rows affected. 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. Filtering based on related +fields is still possible. You cannot call ``update()`` on a +:class:`QuerySet` that has had a slice taken or can otherwise no longer be +filtered. + +For example, if you wanted to update all the entries in a particular blog +to use the same headline:: + + >>> b = Blog.objects.get(pk=1) + + # Update all the headlines belonging to this Blog. + >>> Entry.objects.select_related().filter(blog=b).update(headline='Everything is the same') + +The ``update()`` method does a bulk update and does not call any ``save()`` +methods on your models, nor does it emit the ``pre_save`` or ``post_save`` +signals (which are a consequence of calling ``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() + +Django 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() + +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). + +.. _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 ``filter()``, +``exclude()`` and ``get()``. + +For an introduction, see :ref:`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 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; + +.. versionchanged:: 1.0 + The semantics of ``id__exact=None`` have changed in Django 1.0. Previously, + it was (intentionally) converted to ``WHERE id = NULL`` at the SQL level, + which would never match anything. It has now been changed to behave the + same as ``id__isnull=True``. + +.. 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 ``'Today Lennon honored'`` but not +``'today lennon honored'``. + +SQLite doesn't support case-sensitive ``LIKE`` statements; ``contains`` acts +like ``icontains`` for SQLite. + +.. 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%') + +The above code fragment could also be written as follows:: + + inner_q = Blog.objects.filter(name__contains='Cheddar').values('pk').query + entries = Entry.objects.filter(blog__in=inner_q) + + +.. versionchanged:: 1.1 + In Django 1.0, only the latter piece of code is valid. + +This second form is a bit less readable and unnatural to write, since it +accesses the internal ``query`` attribute and requires a ``ValuesQuerySet``. +If your code doesn't require compatibility with Django 1.0, use the first +form, passing in a queryset directly. + +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) + +.. warning:: + + This ``query`` attribute should be considered an opaque internal attribute. + It's fine to use it like above, but its API may change between Django + versions. + +.. 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'; + +SQLite doesn't support case-sensitive ``LIKE`` statements; ``endswith`` acts +like ``iendswith`` for SQLite. + +.. 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. + +.. 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 EXTRACT('year' FROM pub_date) = '2005'; + +(The exact SQL syntax varies for each database engine.) + +.. fieldlookup:: month + +month +~~~~~ + +For date/datetime fields, 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/datetime fields, 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 +~~~~~~~~ + +.. versionadded:: 1.1 + +For date/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. + +.. 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 ``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. +<http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html>`_ + + +.. fieldlookup:: regex + +regex +~~~~~ + +.. versionadded:: 1.0 + +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 +~~~~~~ + +.. versionadded:: 1.0 + +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 +--------------------- + +.. versionadded:: 1.1 + +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. + + * 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: integer + +Has one optional argument: + +.. attribute:: distinct + + If distinct=True, the count will only include unique instances. This has + the SQL equivalent of ``COUNT(DISTINCT field)``. 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. |