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, 0 insertions, 1888 deletions
diff --git a/parts/django/docs/ref/models/querysets.txt b/parts/django/docs/ref/models/querysets.txt deleted file mode 100644 index 9f0de1f..0000000 --- a/parts/django/docs/ref/models/querysets.txt +++ /dev/null @@ -1,1888 +0,0 @@ -====================== -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. |