diff options
Diffstat (limited to 'parts/django/docs/topics/db/queries.txt')
| -rw-r--r-- | parts/django/docs/topics/db/queries.txt | 1110 |
1 files changed, 0 insertions, 1110 deletions
diff --git a/parts/django/docs/topics/db/queries.txt b/parts/django/docs/topics/db/queries.txt deleted file mode 100644 index 923b1e4..0000000 --- a/parts/django/docs/topics/db/queries.txt +++ /dev/null @@ -1,1110 +0,0 @@ -============== -Making queries -============== - -.. currentmodule:: django.db.models - -Once you've created your :doc:`data models </topics/db/models>`, Django -automatically gives you a database-abstraction API that lets you create, -retrieve, update and delete objects. This document explains how to use this -API. Refer to the :doc:`data model reference </ref/models/index>` for full -details of all the various model lookup options. - -Throughout this guide (and in the reference), we'll refer to the following -models, which comprise a Weblog application: - -.. _queryset-model-example: - -.. code-block:: python - - class Blog(models.Model): - name = models.CharField(max_length=100) - tagline = models.TextField() - - def __unicode__(self): - return self.name - - class Author(models.Model): - name = models.CharField(max_length=50) - email = models.EmailField() - - def __unicode__(self): - return self.name - - class Entry(models.Model): - blog = models.ForeignKey(Blog) - headline = models.CharField(max_length=255) - body_text = models.TextField() - pub_date = models.DateTimeField() - authors = models.ManyToManyField(Author) - n_comments = models.IntegerField() - n_pingbacks = models.IntegerField() - rating = models.IntegerField() - - def __unicode__(self): - return self.headline - -Creating objects -================ - -To represent database-table data in Python objects, Django uses an intuitive -system: A model class represents a database table, and an instance of that -class represents a particular record in the database table. - -To create an object, instantiate it using keyword arguments to the model class, -then call ``save()`` to save it to the database. - -You import the model class from wherever it lives on the Python path, as you -may expect. (We point this out here because previous Django versions required -funky model importing.) - -Assuming models live in a file ``mysite/blog/models.py``, here's an example:: - - >>> from blog.models import Blog - >>> b = Blog(name='Beatles Blog', tagline='All the latest Beatles news.') - >>> b.save() - -This performs an ``INSERT`` SQL statement behind the scenes. Django doesn't hit -the database until you explicitly call ``save()``. - -The ``save()`` method has no return value. - -.. seealso:: - - ``save()`` takes a number of advanced options not described here. - See the documentation for ``save()`` for complete details. - - To create an object and save it all in one step see the ```create()``` - method. - -Saving changes to objects -========================= - -To save changes to an object that's already in the database, use ``save()``. - -Given a ``Blog`` instance ``b5`` that has already been saved to the database, -this example changes its name and updates its record in the database:: - - >> b5.name = 'New name' - >> b5.save() - -This performs an ``UPDATE`` SQL statement behind the scenes. Django doesn't hit -the database until you explicitly call ``save()``. - -Saving ``ForeignKey`` and ``ManyToManyField`` fields ----------------------------------------------------- - -Updating a ``ForeignKey`` field works exactly the same way as saving a normal -field; simply assign an object of the right type to the field in question. -This example updates the ``blog`` attribute of an ``Entry`` instance ``entry``:: - - >>> from blog.models import Entry - >>> entry = Entry.objects.get(pk=1) - >>> cheese_blog = Blog.objects.get(name="Cheddar Talk") - >>> entry.blog = cheese_blog - >>> entry.save() - -Updating a ``ManyToManyField`` works a little differently; use the ``add()`` -method on the field to add a record to the relation. This example adds the -``Author`` instance ``joe`` to the ``entry`` object:: - - >>> from blog.models import Author - >>> joe = Author.objects.create(name="Joe") - >>> entry.authors.add(joe) - -Django will complain if you try to assign or add an object of the wrong type. - -Retrieving objects -================== - -To retrieve objects from your database, you construct a ``QuerySet`` via a -``Manager`` on your model class. - -A ``QuerySet`` represents a collection of objects from your database. It can -have zero, one or many *filters* -- criteria that narrow down the collection -based on given parameters. In SQL terms, a ``QuerySet`` equates to a ``SELECT`` -statement, and a filter is a limiting clause such as ``WHERE`` or ``LIMIT``. - -You get a ``QuerySet`` by using your model's ``Manager``. Each model has at -least one ``Manager``, and it's called ``objects`` by default. Access it -directly via the model class, like so:: - - >>> Blog.objects - <django.db.models.manager.Manager object at ...> - >>> b = Blog(name='Foo', tagline='Bar') - >>> b.objects - Traceback: - ... - AttributeError: "Manager isn't accessible via Blog instances." - -.. note:: - - ``Managers`` are accessible only via model classes, rather than from model - instances, to enforce a separation between "table-level" operations and - "record-level" operations. - -The ``Manager`` is the main source of ``QuerySets`` for a model. It acts as a -"root" ``QuerySet`` that describes all objects in the model's database table. -For example, ``Blog.objects`` is the initial ``QuerySet`` that contains all -``Blog`` objects in the database. - -Retrieving all objects ----------------------- - -The simplest way to retrieve objects from a table is to get all of them. -To do this, use the ``all()`` method on a ``Manager``:: - - >>> all_entries = Entry.objects.all() - -The ``all()`` method returns a ``QuerySet`` of all the objects in the database. - -(If ``Entry.objects`` is a ``QuerySet``, why can't we just do ``Entry.objects``? -That's because ``Entry.objects``, the root ``QuerySet``, is a special case -that cannot be evaluated. The ``all()`` method returns a ``QuerySet`` that -*can* be evaluated.) - - -Retrieving specific objects with filters ----------------------------------------- - -The root ``QuerySet`` provided by the ``Manager`` describes all objects in the -database table. Usually, though, you'll need to select only a subset of the -complete set of objects. - -To create such a subset, you refine the initial ``QuerySet``, adding filter -conditions. The two most common ways to refine a ``QuerySet`` are: - - ``filter(**kwargs)`` - Returns a new ``QuerySet`` containing objects that match the given - lookup parameters. - - ``exclude(**kwargs)`` - Returns a new ``QuerySet`` containing objects that do *not* match the - given lookup parameters. - -The lookup parameters (``**kwargs`` in the above function definitions) should -be in the format described in `Field lookups`_ below. - -For example, to get a ``QuerySet`` of blog entries from the year 2006, use -``filter()`` like so:: - - Entry.objects.filter(pub_date__year=2006) - -We don't have to add an ``all()`` -- ``Entry.objects.all().filter(...)``. That -would still work, but you only need ``all()`` when you want all objects from the -root ``QuerySet``. - -.. _chaining-filters: - -Chaining filters -~~~~~~~~~~~~~~~~ - -The result of refining a ``QuerySet`` is itself a ``QuerySet``, so it's -possible to chain refinements together. For example:: - - >>> Entry.objects.filter( - ... headline__startswith='What' - ... ).exclude( - ... pub_date__gte=datetime.now() - ... ).filter( - ... pub_date__gte=datetime(2005, 1, 1) - ... ) - -This takes the initial ``QuerySet`` of all entries in the database, adds a -filter, then an exclusion, then another filter. The final result is a -``QuerySet`` containing all entries with a headline that starts with "What", -that were published between January 1, 2005, and the current day. - -.. _filtered-querysets-are-unique: - -Filtered QuerySets are unique -~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ - -Each time you refine a ``QuerySet``, you get a brand-new ``QuerySet`` that is -in no way bound to the previous ``QuerySet``. Each refinement creates a -separate and distinct ``QuerySet`` that can be stored, used and reused. - -Example:: - - >> q1 = Entry.objects.filter(headline__startswith="What") - >> q2 = q1.exclude(pub_date__gte=datetime.now()) - >> q3 = q1.filter(pub_date__gte=datetime.now()) - -These three ``QuerySets`` are separate. The first is a base ``QuerySet`` -containing all entries that contain a headline starting with "What". The second -is a subset of the first, with an additional criteria that excludes records -whose ``pub_date`` is greater than now. The third is a subset of the first, -with an additional criteria that selects only the records whose ``pub_date`` is -greater than now. The initial ``QuerySet`` (``q1``) is unaffected by the -refinement process. - -.. _querysets-are-lazy: - -QuerySets are lazy -~~~~~~~~~~~~~~~~~~ - -``QuerySets`` are lazy -- the act of creating a ``QuerySet`` doesn't involve any -database activity. You can stack filters together all day long, and Django won't -actually run the query until the ``QuerySet`` is *evaluated*. Take a look at -this example:: - - >>> q = Entry.objects.filter(headline__startswith="What") - >>> q = q.filter(pub_date__lte=datetime.now()) - >>> q = q.exclude(body_text__icontains="food") - >>> print q - -Though this looks like three database hits, in fact it hits the database only -once, at the last line (``print q``). In general, the results of a ``QuerySet`` -aren't fetched from the database until you "ask" for them. When you do, the -``QuerySet`` is *evaluated* by accessing the database. For more details on -exactly when evaluation takes place, see :ref:`when-querysets-are-evaluated`. - - -.. _retrieving-single-object-with-get: - -Retrieving a single object with get ------------------------------------ - -``.filter()`` will always give you a ``QuerySet``, even if only a single -object matches the query - in this case, it will be a ``QuerySet`` containing -a single element. - -If you know there is only one object that matches your query, you can use -the ``get()`` method on a `Manager` which returns the object directly:: - - >>> one_entry = Entry.objects.get(pk=1) - -You can use any query expression with ``get()``, just like with ``filter()`` - -again, see `Field lookups`_ below. - -Note that there is a difference between using ``.get()``, and using -``.filter()`` with a slice of ``[0]``. If there are no results that match the -query, ``.get()`` will raise a ``DoesNotExist`` exception. This exception is an -attribute of the model class that the query is being performed on - so in the -code above, if there is no ``Entry`` object with a primary key of 1, Django will -raise ``Entry.DoesNotExist``. - -Similarly, Django will complain if more than one item matches the ``get()`` -query. In this case, it will raise ``MultipleObjectsReturned``, which again is -an attribute of the model class itself. - - -Other QuerySet methods ----------------------- - -Most of the time you'll use ``all()``, ``get()``, ``filter()`` and ``exclude()`` -when you need to look up objects from the database. However, that's far from all -there is; see the :ref:`QuerySet API Reference <queryset-api>` for a complete -list of all the various ``QuerySet`` methods. - -.. _limiting-querysets: - -Limiting QuerySets ------------------- - -Use a subset of Python's array-slicing syntax to limit your ``QuerySet`` to a -certain number of results. This is the equivalent of SQL's ``LIMIT`` and -``OFFSET`` clauses. - -For example, this returns the first 5 objects (``LIMIT 5``):: - - >>> Entry.objects.all()[:5] - -This returns the sixth through tenth objects (``OFFSET 5 LIMIT 5``):: - - >>> Entry.objects.all()[5:10] - -Negative indexing (i.e. ``Entry.objects.all()[-1]``) is not supported. - -Generally, slicing a ``QuerySet`` returns a new ``QuerySet`` -- it doesn't -evaluate the query. An exception is if you use the "step" parameter of Python -slice syntax. For example, this would actually execute the query in order to -return a list of every *second* object of the first 10:: - - >>> Entry.objects.all()[:10:2] - -To retrieve a *single* object rather than a list -(e.g. ``SELECT foo FROM bar LIMIT 1``), use a simple index instead of a -slice. For example, this returns the first ``Entry`` in the database, after -ordering entries alphabetically by headline:: - - >>> Entry.objects.order_by('headline')[0] - -This is roughly equivalent to:: - - >>> Entry.objects.order_by('headline')[0:1].get() - -Note, however, that the first of these will raise ``IndexError`` while the -second will raise ``DoesNotExist`` if no objects match the given criteria. See -:meth:`~django.db.models.QuerySet.get` for more details. - -.. _field-lookups-intro: - -Field lookups -------------- - -Field lookups are how you specify the meat of an SQL ``WHERE`` clause. They're -specified as keyword arguments to the ``QuerySet`` methods ``filter()``, -``exclude()`` and ``get()``. - -Basic lookups keyword arguments take the form ``field__lookuptype=value``. -(That's a double-underscore). For example:: - - >>> Entry.objects.filter(pub_date__lte='2006-01-01') - -translates (roughly) into the following SQL:: - - SELECT * FROM blog_entry WHERE pub_date <= '2006-01-01'; - -.. admonition:: How this is possible - - Python has the ability to define functions that accept arbitrary name-value - arguments whose names and values are evaluated at runtime. For more - information, see `Keyword Arguments`_ in the official Python tutorial. - - .. _`Keyword Arguments`: http://docs.python.org/tutorial/controlflow.html#keyword-arguments - -If you pass an invalid keyword argument, a lookup function will raise -``TypeError``. - -The database API supports about two dozen lookup types; a complete reference -can be found in the :ref:`field lookup reference <field-lookups>`. To give you a taste of what's available, here's some of the more common lookups -you'll probably use: - - :lookup:`exact` - An "exact" match. For example:: - - >>> Entry.objects.get(headline__exact="Man bites dog") - - Would generate SQL along these lines: - - .. code-block:: sql - - SELECT ... WHERE headline = 'Man bites dog'; - - If you don't provide a lookup type -- that is, if your keyword argument - doesn't contain a double underscore -- the lookup type is assumed to be - ``exact``. - - For example, the following two statements are equivalent:: - - >>> Blog.objects.get(id__exact=14) # Explicit form - >>> Blog.objects.get(id=14) # __exact is implied - - This is for convenience, because ``exact`` lookups are the common case. - - :lookup:`iexact` - A case-insensitive match. So, the query:: - - >>> Blog.objects.get(name__iexact="beatles blog") - - Would match a ``Blog`` titled "Beatles Blog", "beatles blog", or even - "BeAtlES blOG". - - :lookup:`contains` - Case-sensitive containment test. For example:: - - Entry.objects.get(headline__contains='Lennon') - - Roughly translates to this SQL: - - .. code-block:: sql - - SELECT ... WHERE headline LIKE '%Lennon%'; - - Note this will match the headline ``'Today Lennon honored'`` but not - ``'today lennon honored'``. - - There's also a case-insensitive version, :lookup:`icontains`. - - :lookup:`startswith`, :lookup:`endswith` - Starts-with and ends-with search, respectively. There are also - case-insensitive versions called :lookup:`istartswith` and - :lookup:`iendswith`. - -Again, this only scratches the surface. A complete reference can be found in the -:ref:`field lookup reference <field-lookups>`. - -Lookups that span relationships -------------------------------- - -Django offers a powerful and intuitive way to "follow" relationships in -lookups, taking care of the SQL ``JOIN``\s for you automatically, behind the -scenes. To span a relationship, just use the field name of related fields -across models, separated by double underscores, until you get to the field you -want. - -This example retrieves all ``Entry`` objects with a ``Blog`` whose ``name`` -is ``'Beatles Blog'``:: - - >>> Entry.objects.filter(blog__name__exact='Beatles Blog') - -This spanning can be as deep as you'd like. - -It works backwards, too. To refer to a "reverse" relationship, just use the -lowercase name of the model. - -This example retrieves all ``Blog`` objects which have at least one ``Entry`` -whose ``headline`` contains ``'Lennon'``:: - - >>> Blog.objects.filter(entry__headline__contains='Lennon') - -If you are filtering across multiple relationships and one of the intermediate -models doesn't have a value that meets the filter condition, Django will treat -it as if there is an empty (all values are ``NULL``), but valid, object there. -All this means is that no error will be raised. For example, in this filter:: - - Blog.objects.filter(entry__authors__name='Lennon') - -(if there was a related ``Author`` model), if there was no ``author`` -associated with an entry, it would be treated as if there was also no ``name`` -attached, rather than raising an error because of the missing ``author``. -Usually this is exactly what you want to have happen. The only case where it -might be confusing is if you are using ``isnull``. Thus:: - - Blog.objects.filter(entry__authors__name__isnull=True) - -will return ``Blog`` objects that have an empty ``name`` on the ``author`` and -also those which have an empty ``author`` on the ``entry``. If you don't want -those latter objects, you could write:: - - Blog.objects.filter(entry__authors__isnull=False, - entry__authors__name__isnull=True) - -Spanning multi-valued relationships -~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ - -.. versionadded:: 1.0 - -When you are filtering an object based on a ``ManyToManyField`` or a reverse -``ForeignKey``, there are two different sorts of filter you may be -interested in. Consider the ``Blog``/``Entry`` relationship (``Blog`` to -``Entry`` is a one-to-many relation). We might be interested in finding blogs -that have an entry which has both *"Lennon"* in the headline and was published -in 2008. Or we might want to find blogs that have an entry with *"Lennon"* in -the headline as well as an entry that was published in 2008. Since there are -multiple entries associated with a single ``Blog``, both of these queries are -possible and make sense in some situations. - -The same type of situation arises with a ``ManyToManyField``. For example, if -an ``Entry`` has a ``ManyToManyField`` called ``tags``, we might want to find -entries linked to tags called *"music"* and *"bands"* or we might want an -entry that contains a tag with a name of *"music"* and a status of *"public"*. - -To handle both of these situations, Django has a consistent way of processing -``filter()`` and ``exclude()`` calls. Everything inside a single ``filter()`` -call is applied simultaneously to filter out items matching all those -requirements. Successive ``filter()`` calls further restrict the set of -objects, but for multi-valued relations, they apply to any object linked to -the primary model, not necessarily those objects that were selected by an -earlier ``filter()`` call. - -That may sound a bit confusing, so hopefully an example will clarify. To -select all blogs that contain entries with both *"Lennon"* in the headline -and that were published in 2008 (the same entry satisfying both conditions), -we would write:: - - Blog.objects.filter(entry__headline__contains='Lennon', - entry__pub_date__year=2008) - -To select all blogs that contain an entry with *"Lennon"* in the headline -**as well as** an entry that was published in 2008, we would write:: - - Blog.objects.filter(entry__headline__contains='Lennon').filter( - entry__pub_date__year=2008) - -In this second example, the first filter restricted the queryset to all those -blogs linked to that particular type of entry. The second filter restricted -the set of blogs *further* to those that are also linked to the second type of -entry. The entries select by the second filter may or may not be the same as -the entries in the first filter. We are filtering the ``Blog`` items with each -filter statement, not the ``Entry`` items. - -All of this behavior also applies to ``exclude()``: all the conditions in a -single ``exclude()`` statement apply to a single instance (if those conditions -are talking about the same multi-valued relation). Conditions in subsequent -``filter()`` or ``exclude()`` calls that refer to the same relation may end up -filtering on different linked objects. - -.. _query-expressions: - -Filters can reference fields on the model ------------------------------------------ - -.. versionadded:: 1.1 - -In the examples given so far, we have constructed filters that compare -the value of a model field with a constant. But what if you want to compare -the value of a model field with another field on the same model? - -Django provides the ``F()`` object to allow such comparisons. Instances -of ``F()`` act as a reference to a model field within a query. These -references can then be used in query filters to compare the values of two -different fields on the same model instance. - -For example, to find a list of all blog entries that have had more comments -than pingbacks, we construct an ``F()`` object to reference the comment count, -and use that ``F()`` object in the query:: - - >>> from django.db.models import F - >>> Entry.objects.filter(n_comments__gt=F('n_pingbacks')) - -Django supports the use of addition, subtraction, multiplication, -division and modulo arithmetic with ``F()`` objects, both with constants -and with other ``F()`` objects. To find all the blog entries with more than -*twice* as many comments as pingbacks, we modify the query:: - - >>> Entry.objects.filter(n_comments__gt=F('n_pingbacks') * 2) - -To find all the entries where the rating of the entry is less than the -sum of the pingback count and comment count, we would issue the -query:: - - >>> Entry.objects.filter(rating__lt=F('n_comments') + F('n_pingbacks')) - -You can also use the double underscore notation to span relationships in -an ``F()`` object. An ``F()`` object with a double underscore will introduce -any joins needed to access the related object. For example, to retrieve all -the entries where the author's name is the same as the blog name, we could -issue the query: - - >>> Entry.objects.filter(authors__name=F('blog__name')) - -The pk lookup shortcut ----------------------- - -For convenience, Django provides a ``pk`` lookup shortcut, which stands for -"primary key". - -In the example ``Blog`` model, the primary key is the ``id`` field, so these -three statements are equivalent:: - - >>> Blog.objects.get(id__exact=14) # Explicit form - >>> Blog.objects.get(id=14) # __exact is implied - >>> Blog.objects.get(pk=14) # pk implies id__exact - -The use of ``pk`` isn't limited to ``__exact`` queries -- any query term -can be combined with ``pk`` to perform a query on the primary key of a model:: - - # Get blogs entries with id 1, 4 and 7 - >>> Blog.objects.filter(pk__in=[1,4,7]) - - # Get all blog entries with id > 14 - >>> Blog.objects.filter(pk__gt=14) - -``pk`` lookups also work across joins. For example, these three statements are -equivalent:: - - >>> Entry.objects.filter(blog__id__exact=3) # Explicit form - >>> Entry.objects.filter(blog__id=3) # __exact is implied - >>> Entry.objects.filter(blog__pk=3) # __pk implies __id__exact - -Escaping percent signs and underscores in LIKE statements ---------------------------------------------------------- - -The field lookups that equate to ``LIKE`` SQL statements (``iexact``, -``contains``, ``icontains``, ``startswith``, ``istartswith``, ``endswith`` -and ``iendswith``) will automatically escape the two special characters used in -``LIKE`` statements -- the percent sign and the underscore. (In a ``LIKE`` -statement, the percent sign signifies a multiple-character wildcard and the -underscore signifies a single-character wildcard.) - -This means things should work intuitively, so the abstraction doesn't leak. -For example, to retrieve all the entries that contain a percent sign, just use -the percent sign as any other character:: - - >>> Entry.objects.filter(headline__contains='%') - -Django takes care of the quoting for you; the resulting SQL will look something -like this: - -.. code-block:: sql - - SELECT ... WHERE headline LIKE '%\%%'; - -Same goes for underscores. Both percentage signs and underscores are handled -for you transparently. - -.. _caching-and-querysets: - -Caching and QuerySets ---------------------- - -Each ``QuerySet`` contains a cache, to minimize database access. It's important -to understand how it works, in order to write the most efficient code. - -In a newly created ``QuerySet``, the cache is empty. The first time a -``QuerySet`` is evaluated -- and, hence, a database query happens -- Django -saves the query results in the ``QuerySet``'s cache and returns the results -that have been explicitly requested (e.g., the next element, if the -``QuerySet`` is being iterated over). Subsequent evaluations of the -``QuerySet`` reuse the cached results. - -Keep this caching behavior in mind, because it may bite you if you don't use -your ``QuerySet``\s correctly. For example, the following will create two -``QuerySet``\s, evaluate them, and throw them away:: - - >>> print [e.headline for e in Entry.objects.all()] - >>> print [e.pub_date for e in Entry.objects.all()] - -That means the same database query will be executed twice, effectively doubling -your database load. Also, there's a possibility the two lists may not include -the same database records, because an ``Entry`` may have been added or deleted -in the split second between the two requests. - -To avoid this problem, simply save the ``QuerySet`` and reuse it:: - - >>> queryset = Entry.objects.all() - >>> print [p.headline for p in queryset] # Evaluate the query set. - >>> print [p.pub_date for p in queryset] # Re-use the cache from the evaluation. - -.. _complex-lookups-with-q: - -Complex lookups with Q objects -============================== - -Keyword argument queries -- in ``filter()``, etc. -- are "AND"ed together. If -you need to execute more complex queries (for example, queries with ``OR`` -statements), you can use ``Q`` objects. - -A ``Q`` object (``django.db.models.Q``) is an object used to encapsulate a -collection of keyword arguments. These keyword arguments are specified as in -"Field lookups" above. - -For example, this ``Q`` object encapsulates a single ``LIKE`` query:: - - Q(question__startswith='What') - -``Q`` objects can be combined using the ``&`` and ``|`` operators. When an -operator is used on two ``Q`` objects, it yields a new ``Q`` object. - -For example, this statement yields a single ``Q`` object that represents the -"OR" of two ``"question__startswith"`` queries:: - - Q(question__startswith='Who') | Q(question__startswith='What') - -This is equivalent to the following SQL ``WHERE`` clause:: - - WHERE question LIKE 'Who%' OR question LIKE 'What%' - -You can compose statements of arbitrary complexity by combining ``Q`` objects -with the ``&`` and ``|`` operators and use parenthetical grouping. Also, ``Q`` -objects can be negated using the ``~`` operator, allowing for combined lookups -that combine both a normal query and a negated (``NOT``) query:: - - Q(question__startswith='Who') | ~Q(pub_date__year=2005) - -Each lookup function that takes keyword-arguments (e.g. ``filter()``, -``exclude()``, ``get()``) can also be passed one or more ``Q`` objects as -positional (not-named) arguments. If you provide multiple ``Q`` object -arguments to a lookup function, the arguments will be "AND"ed together. For -example:: - - Poll.objects.get( - Q(question__startswith='Who'), - Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)) - ) - -... roughly translates into the SQL:: - - SELECT * from polls WHERE question LIKE 'Who%' - AND (pub_date = '2005-05-02' OR pub_date = '2005-05-06') - -Lookup functions can mix the use of ``Q`` objects and keyword arguments. All -arguments provided to a lookup function (be they keyword arguments or ``Q`` -objects) are "AND"ed together. However, if a ``Q`` object is provided, it must -precede the definition of any keyword arguments. For example:: - - Poll.objects.get( - Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)), - question__startswith='Who') - -... would be a valid query, equivalent to the previous example; but:: - - # INVALID QUERY - Poll.objects.get( - question__startswith='Who', - Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6))) - -... would not be valid. - -.. seealso:: - - The `OR lookups examples`_ in the Django unit tests show some possible uses - of ``Q``. - - .. _OR lookups examples: http://code.djangoproject.com/browser/django/trunk/tests/modeltests/or_lookups/tests.py - -Comparing objects -================= - -To compare two model instances, just use the standard Python comparison operator, -the double equals sign: ``==``. Behind the scenes, that compares the primary -key values of two models. - -Using the ``Entry`` example above, the following two statements are equivalent:: - - >>> some_entry == other_entry - >>> some_entry.id == other_entry.id - -If a model's primary key isn't called ``id``, no problem. Comparisons will -always use the primary key, whatever it's called. For example, if a model's -primary key field is called ``name``, these two statements are equivalent:: - - >>> some_obj == other_obj - >>> some_obj.name == other_obj.name - -.. _topics-db-queries-delete: - -Deleting objects -================ - -The delete method, conveniently, is named ``delete()``. This method immediately -deletes the object and has no return value. Example:: - - e.delete() - -You can also delete objects in bulk. Every ``QuerySet`` has a ``delete()`` -method, which deletes all members of that ``QuerySet``. - -For example, this deletes all ``Entry`` objects with a ``pub_date`` year of -2005:: - - Entry.objects.filter(pub_date__year=2005).delete() - -Keep in mind that this will, whenever possible, be executed purely in -SQL, and so the ``delete()`` methods of individual object instances -will not necessarily be called during the process. If you've provided -a custom ``delete()`` method on a model class and want to ensure that -it is called, you will need to "manually" delete instances of that -model (e.g., by iterating over a ``QuerySet`` and calling ``delete()`` -on each object individually) rather than using the bulk ``delete()`` -method of a ``QuerySet``. - -When Django deletes an object, it emulates the behavior of the SQL -constraint ``ON DELETE CASCADE`` -- in other words, any objects which -had foreign keys pointing at the object to be deleted will be deleted -along with it. For example:: - - b = Blog.objects.get(pk=1) - # This will delete the Blog and all of its Entry objects. - b.delete() - -Note that ``delete()`` is the only ``QuerySet`` method that is not exposed on a -``Manager`` itself. This is a safety mechanism to prevent you from accidentally -requesting ``Entry.objects.delete()``, and deleting *all* the entries. If you -*do* want to delete all the objects, then you have to explicitly request a -complete query set:: - - Entry.objects.all().delete() - -.. _topics-db-queries-update: - -Updating multiple objects at once -================================= - -.. versionadded:: 1.0 - -Sometimes you want to set a field to a particular value for all the objects in -a ``QuerySet``. You can do this with the ``update()`` method. For example:: - - # Update all the headlines with pub_date in 2007. - Entry.objects.filter(pub_date__year=2007).update(headline='Everything is the same') - -You can only set non-relation fields and ``ForeignKey`` fields using this -method. To update a non-relation field, provide the new value as a constant. -To update ``ForeignKey`` fields, set the new value to be the new model -instance you want to point to. For example:: - - >>> b = Blog.objects.get(pk=1) - - # Change every Entry so that it belongs to this Blog. - >>> Entry.objects.all().update(blog=b) - -The ``update()`` method is applied instantly and returns the number of rows -affected by the query. The only restriction on the ``QuerySet`` that is -updated is that it can only access one database table, the model's main -table. You can filter based on related fields, but you can only update columns -in the model's main table. Example:: - - >>> 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') - -Be aware that the ``update()`` method is converted directly to an SQL -statement. It is a bulk operation for direct updates. It doesn't run any -``save()`` methods on your models, or emit the ``pre_save`` or ``post_save`` -signals (which are a consequence of calling ``save()``). If you want to save -every item in a ``QuerySet`` and make sure that the ``save()`` method is -called on each instance, you don't need any special function to handle that. -Just loop over them and call ``save()``:: - - for item in my_queryset: - item.save() - -.. versionadded:: 1.1 - -Calls to update can also use :ref:`F() objects <query-expressions>` to update -one field based on the value of another field in the model. This is especially -useful for incrementing counters based upon their current value. For example, to -increment the pingback count for every entry in the blog:: - - >>> Entry.objects.all().update(n_pingbacks=F('n_pingbacks') + 1) - -However, unlike ``F()`` objects in filter and exclude clauses, you can't -introduce joins when you use ``F()`` objects in an update -- you can only -reference fields local to the model being updated. If you attempt to introduce -a join with an ``F()`` object, a ``FieldError`` will be raised:: - - # THIS WILL RAISE A FieldError - >>> Entry.objects.update(headline=F('blog__name')) - -Related objects -=============== - -When you define a relationship in a model (i.e., a ``ForeignKey``, -``OneToOneField``, or ``ManyToManyField``), instances of that model will have -a convenient API to access the related object(s). - -Using the models at the top of this page, for example, an ``Entry`` object ``e`` -can get its associated ``Blog`` object by accessing the ``blog`` attribute: -``e.blog``. - -(Behind the scenes, this functionality is implemented by Python descriptors_. -This shouldn't really matter to you, but we point it out here for the curious.) - -Django also creates API accessors for the "other" side of the relationship -- -the link from the related model to the model that defines the relationship. -For example, a ``Blog`` object ``b`` has access to a list of all related -``Entry`` objects via the ``entry_set`` attribute: ``b.entry_set.all()``. - -All examples in this section use the sample ``Blog``, ``Author`` and ``Entry`` -models defined at the top of this page. - -.. _descriptors: http://users.rcn.com/python/download/Descriptor.htm - -One-to-many relationships -------------------------- - -Forward -~~~~~~~ - -If a model has a ``ForeignKey``, instances of that model will have access to -the related (foreign) object via a simple attribute of the model. - -Example:: - - >>> e = Entry.objects.get(id=2) - >>> e.blog # Returns the related Blog object. - -You can get and set via a foreign-key attribute. As you may expect, changes to -the foreign key aren't saved to the database until you call ``save()``. -Example:: - - >>> e = Entry.objects.get(id=2) - >>> e.blog = some_blog - >>> e.save() - -If a ``ForeignKey`` field has ``null=True`` set (i.e., it allows ``NULL`` -values), you can assign ``None`` to it. Example:: - - >>> e = Entry.objects.get(id=2) - >>> e.blog = None - >>> e.save() # "UPDATE blog_entry SET blog_id = NULL ...;" - -Forward access to one-to-many relationships is cached the first time the -related object is accessed. Subsequent accesses to the foreign key on the same -object instance are cached. Example:: - - >>> e = Entry.objects.get(id=2) - >>> print e.blog # Hits the database to retrieve the associated Blog. - >>> print e.blog # Doesn't hit the database; uses cached version. - -Note that the ``select_related()`` ``QuerySet`` method recursively prepopulates -the cache of all one-to-many relationships ahead of time. Example:: - - >>> e = Entry.objects.select_related().get(id=2) - >>> print e.blog # Doesn't hit the database; uses cached version. - >>> print e.blog # Doesn't hit the database; uses cached version. - -.. _backwards-related-objects: - -Following relationships "backward" -~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ - -If a model has a ``ForeignKey``, instances of the foreign-key model will have -access to a ``Manager`` that returns all instances of the first model. By -default, this ``Manager`` is named ``FOO_set``, where ``FOO`` is the source -model name, lowercased. This ``Manager`` returns ``QuerySets``, which can be -filtered and manipulated as described in the "Retrieving objects" section -above. - -Example:: - - >>> b = Blog.objects.get(id=1) - >>> b.entry_set.all() # Returns all Entry objects related to Blog. - - # b.entry_set is a Manager that returns QuerySets. - >>> b.entry_set.filter(headline__contains='Lennon') - >>> b.entry_set.count() - -You can override the ``FOO_set`` name by setting the ``related_name`` -parameter in the ``ForeignKey()`` definition. For example, if the ``Entry`` -model was altered to ``blog = ForeignKey(Blog, related_name='entries')``, the -above example code would look like this:: - - >>> b = Blog.objects.get(id=1) - >>> b.entries.all() # Returns all Entry objects related to Blog. - - # b.entries is a Manager that returns QuerySets. - >>> b.entries.filter(headline__contains='Lennon') - >>> b.entries.count() - -You cannot access a reverse ``ForeignKey`` ``Manager`` from the class; it must -be accessed from an instance:: - - >>> Blog.entry_set - Traceback: - ... - AttributeError: "Manager must be accessed via instance". - -In addition to the ``QuerySet`` methods defined in "Retrieving objects" above, -the ``ForeignKey`` ``Manager`` has additional methods used to handle the set of -related objects. A synopsis of each is below, and complete details can be found -in the :doc:`related objects reference </ref/models/relations>`. - -``add(obj1, obj2, ...)`` - Adds the specified model objects to the related object set. - -``create(**kwargs)`` - Creates a new object, saves it and puts it in the related object set. - Returns the newly created object. - -``remove(obj1, obj2, ...)`` - Removes the specified model objects from the related object set. - -``clear()`` - Removes all objects from the related object set. - -To assign the members of a related set in one fell swoop, just assign to it -from any iterable object. The iterable can contain object instances, or just -a list of primary key values. For example:: - - b = Blog.objects.get(id=1) - b.entry_set = [e1, e2] - -In this example, ``e1`` and ``e2`` can be full Entry instances, or integer -primary key values. - -If the ``clear()`` method is available, any pre-existing objects will be -removed from the ``entry_set`` before all objects in the iterable (in this -case, a list) are added to the set. If the ``clear()`` method is *not* -available, all objects in the iterable will be added without removing any -existing elements. - -Each "reverse" operation described in this section has an immediate effect on -the database. Every addition, creation and deletion is immediately and -automatically saved to the database. - -Many-to-many relationships --------------------------- - -Both ends of a many-to-many relationship get automatic API access to the other -end. The API works just as a "backward" one-to-many relationship, above. - -The only difference is in the attribute naming: The model that defines the -``ManyToManyField`` uses the attribute name of that field itself, whereas the -"reverse" model uses the lowercased model name of the original model, plus -``'_set'`` (just like reverse one-to-many relationships). - -An example makes this easier to understand:: - - e = Entry.objects.get(id=3) - e.authors.all() # Returns all Author objects for this Entry. - e.authors.count() - e.authors.filter(name__contains='John') - - a = Author.objects.get(id=5) - a.entry_set.all() # Returns all Entry objects for this Author. - -Like ``ForeignKey``, ``ManyToManyField`` can specify ``related_name``. In the -above example, if the ``ManyToManyField`` in ``Entry`` had specified -``related_name='entries'``, then each ``Author`` instance would have an -``entries`` attribute instead of ``entry_set``. - -One-to-one relationships ------------------------- - -One-to-one relationships are very similar to many-to-one relationships. If you -define a :class:`~django.db.models.OneToOneField` on your model, instances of -that model will have access to the related object via a simple attribute of the -model. - -For example:: - - class EntryDetail(models.Model): - entry = models.OneToOneField(Entry) - details = models.TextField() - - ed = EntryDetail.objects.get(id=2) - ed.entry # Returns the related Entry object. - -The difference comes in "reverse" queries. The related model in a one-to-one -relationship also has access to a :class:`~django.db.models.Manager` object, but -that :class:`~django.db.models.Manager` represents a single object, rather than -a collection of objects:: - - e = Entry.objects.get(id=2) - e.entrydetail # returns the related EntryDetail object - -If no object has been assigned to this relationship, Django will raise -a ``DoesNotExist`` exception. - -Instances can be assigned to the reverse relationship in the same way as -you would assign the forward relationship:: - - e.entrydetail = ed - -How are the backward relationships possible? --------------------------------------------- - -Other object-relational mappers require you to define relationships on both -sides. The Django developers believe this is a violation of the DRY (Don't -Repeat Yourself) principle, so Django only requires you to define the -relationship on one end. - -But how is this possible, given that a model class doesn't know which other -model classes are related to it until those other model classes are loaded? - -The answer lies in the :setting:`INSTALLED_APPS` setting. The first time any model is -loaded, Django iterates over every model in :setting:`INSTALLED_APPS` and creates the -backward relationships in memory as needed. Essentially, one of the functions -of :setting:`INSTALLED_APPS` is to tell Django the entire model domain. - -Queries over related objects ----------------------------- - -Queries involving related objects follow the same rules as queries involving -normal value fields. When specifying the value for a query to match, you may -use either an object instance itself, or the primary key value for the object. - -For example, if you have a Blog object ``b`` with ``id=5``, the following -three queries would be identical:: - - Entry.objects.filter(blog=b) # Query using object instance - Entry.objects.filter(blog=b.id) # Query using id from instance - Entry.objects.filter(blog=5) # Query using id directly - -Falling back to raw SQL -======================= - -If you find yourself needing to write an SQL query that is too complex for -Django's database-mapper to handle, you can fall back on writing SQL by hand. -Django has a couple of options for writing raw SQL queries; see -:doc:`/topics/db/sql`. - -Finally, it's important to note that the Django database layer is merely an -interface to your database. You can access your database via other tools, -programming languages or database frameworks; there's nothing Django-specific -about your database. |