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(**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.

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 the distinct() section 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.

If you don't want any ordering to be applied to a query, not even the default ordering, call order_by() with no parameters.

The syntax for ordering across related models has changed. See the Django 0.96 documentation for the old behaviour.

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.

reverse()¶

Use the reverse() method to reverse the order in which a queryset's elements are returned. Calling reverse() a second time restores the ordering back to the normal direction.

To retrieve the ''last'' five items in a queryset, you could do this:

my_queryset.reverse()[:5]

Note that this is not quite the same as slicing from the end of a sequence in Python. The above example will return the last item first, then the penultimate item and so on. If we had a Python sequence and looked at seq[-5:], we would see the fifth-last item first. Django doesn't support that mode of access (slicing from the end), because it's not possible to do it efficiently in SQL.

Also, note that reverse() should generally only be called on a QuerySet which has a defined ordering (e.g., when querying against a model which defines a default ordering, or when using 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()¶

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().

values(*fields)¶

Returns a ValuesQuerySet -- a QuerySet that evaluates to a list of dictionaries instead of 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 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 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 the distinct() section, above, for details.

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(*fields)¶

This is similar to values() except that instead of returning a list of dictionaries, it returns a list of tuples. 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(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()¶

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()¶

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()¶

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 only refer to ForeignKey relations in the list of fields passed to select_related. You can refer to 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.

Both the depth argument and the ability to specify field names in the call to select_related() are new in Django version 1.0.

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:

SELECT blog_entry.*, (pub_date > '2006-01-01')
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)
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.

New in Django 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= p.hometown # Requires a database call.