<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Aggregation — Django v1.2 documentation</title>
<link rel="stylesheet" href="../../_static/default.css" type="text/css" />
<link rel="stylesheet" href="../../_static/pygments.css" type="text/css" />
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '../../',
VERSION: '1.2',
COLLAPSE_INDEX: false,
FILE_SUFFIX: '.html',
HAS_SOURCE: true
};
</script>
<script type="text/javascript" src="../../_static/jquery.js"></script>
<script type="text/javascript" src="../../_static/underscore.js"></script>
<script type="text/javascript" src="../../_static/doctools.js"></script>
<link rel="top" title="Django v1.2 documentation" href="../../index.html" />
<link rel="up" title="Models and databases" href="index.html" />
<link rel="next" title="Managers" href="managers.html" />
<link rel="prev" title="Making queries" href="queries.html" />
<script type="text/javascript" src="../../templatebuiltins.js"></script>
<script type="text/javascript">
(function($) {
if (!django_template_builtins) {
// templatebuiltins.js missing, do nothing.
return;
}
$(document).ready(function() {
// Hyperlink Django template tags and filters
var base = "../../ref/templates/builtins.html";
if (base == "#") {
// Special case for builtins.html itself
base = "";
}
// Tags are keywords, class '.k'
$("div.highlight\\-html\\+django span.k").each(function(i, elem) {
var tagname = $(elem).text();
if ($.inArray(tagname, django_template_builtins.ttags) != -1) {
var fragment = tagname.replace(/_/, '-');
$(elem).html("<a href='" + base + "#" + fragment + "'>" + tagname + "</a>");
}
});
// Filters are functions, class '.nf'
$("div.highlight\\-html\\+django span.nf").each(function(i, elem) {
var filtername = $(elem).text();
if ($.inArray(filtername, django_template_builtins.tfilters) != -1) {
var fragment = filtername.replace(/_/, '-');
$(elem).html("<a href='" + base + "#" + fragment + "'>" + filtername + "</a>");
}
});
});
})(jQuery);
</script>
</head>
<body>
<div class="document">
<div id="custom-doc" class="yui-t6">
<div id="hd">
<h1><a href="../../index.html">Django v1.2 documentation</a></h1>
<div id="global-nav">
<a title="Home page" href="../../index.html">Home</a> |
<a title="Table of contents" href="../../contents.html">Table of contents</a> |
<a title="Global index" href="../../genindex.html">Index</a> |
<a title="Module index" href="../../py-modindex.html">Modules</a>
</div>
<div class="nav">
« <a href="queries.html" title="Making queries">previous</a>
|
<a href="../index.html" title="Using Django" accesskey="U">up</a>
|
<a href="managers.html" title="Managers">next</a> »</div>
</div>
<div id="bd">
<div id="yui-main">
<div class="yui-b">
<div class="yui-g" id="topics-db-aggregation">
<div class="section" id="s-aggregation">
<span id="aggregation"></span><h1>Aggregation<a class="headerlink" href="#aggregation" title="Permalink to this headline">¶</a></h1>
<div class="versionadded">
<span class="title">New in Django 1.1:</span> <a class="reference internal" href="../../releases/1.1.html"><em>Please, see the release notes</em></a></div>
<p>The topic guide on <a class="reference internal" href="queries.html"><em>Django’s database-abstraction API</em></a>
described the way that you can use Django queries that create,
retrieve, update and delete individual objects. However, sometimes you will
need to retrieve values that are derived by summarizing or <em>aggregating</em> a
collection of objects. This topic guide describes the ways that aggregate values
can be generated and returned using Django queries.</p>
<p>Throughout this guide, we’ll refer to the following models. These models are
used to track the inventory for a series of online bookstores:</p>
<div class="highlight-python" id="queryset-model-example"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Author</span><span class="p">(</span><span class="n">models</span><span class="o">.</span><span class="n">Model</span><span class="p">):</span>
<span class="n">name</span> <span class="o">=</span> <span class="n">models</span><span class="o">.</span><span class="n">CharField</span><span class="p">(</span><span class="n">max_length</span><span class="o">=</span><span class="mi">100</span><span class="p">)</span>
<span class="n">age</span> <span class="o">=</span> <span class="n">models</span><span class="o">.</span><span class="n">IntegerField</span><span class="p">()</span>
<span class="n">friends</span> <span class="o">=</span> <span class="n">models</span><span class="o">.</span><span class="n">ManyToManyField</span><span class="p">(</span><span class="s">'self'</span><span class="p">,</span> <span class="n">blank</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">Publisher</span><span class="p">(</span><span class="n">models</span><span class="o">.</span><span class="n">Model</span><span class="p">):</span>
<span class="n">name</span> <span class="o">=</span> <span class="n">models</span><span class="o">.</span><span class="n">CharField</span><span class="p">(</span><span class="n">max_length</span><span class="o">=</span><span class="mi">300</span><span class="p">)</span>
<span class="n">num_awards</span> <span class="o">=</span> <span class="n">models</span><span class="o">.</span><span class="n">IntegerField</span><span class="p">()</span>
<span class="k">class</span> <span class="nc">Book</span><span class="p">(</span><span class="n">models</span><span class="o">.</span><span class="n">Model</span><span class="p">):</span>
<span class="n">isbn</span> <span class="o">=</span> <span class="n">models</span><span class="o">.</span><span class="n">CharField</span><span class="p">(</span><span class="n">max_length</span><span class="o">=</span><span class="mi">9</span><span class="p">)</span>
<span class="n">name</span> <span class="o">=</span> <span class="n">models</span><span class="o">.</span><span class="n">CharField</span><span class="p">(</span><span class="n">max_length</span><span class="o">=</span><span class="mi">300</span><span class="p">)</span>
<span class="n">pages</span> <span class="o">=</span> <span class="n">models</span><span class="o">.</span><span class="n">IntegerField</span><span class="p">()</span>
<span class="n">price</span> <span class="o">=</span> <span class="n">models</span><span class="o">.</span><span class="n">DecimalField</span><span class="p">(</span><span class="n">max_digits</span><span class="o">=</span><span class="mi">10</span><span class="p">,</span> <span class="n">decimal_places</span><span class="o">=</span><span class="mi">2</span><span class="p">)</span>
<span class="n">rating</span> <span class="o">=</span> <span class="n">models</span><span class="o">.</span><span class="n">FloatField</span><span class="p">()</span>
<span class="n">authors</span> <span class="o">=</span> <span class="n">models</span><span class="o">.</span><span class="n">ManyToManyField</span><span class="p">(</span><span class="n">Author</span><span class="p">)</span>
<span class="n">publisher</span> <span class="o">=</span> <span class="n">models</span><span class="o">.</span><span class="n">ForeignKey</span><span class="p">(</span><span class="n">Publisher</span><span class="p">)</span>
<span class="n">pubdate</span> <span class="o">=</span> <span class="n">models</span><span class="o">.</span><span class="n">DateField</span><span class="p">()</span>
<span class="k">class</span> <span class="nc">Store</span><span class="p">(</span><span class="n">models</span><span class="o">.</span><span class="n">Model</span><span class="p">):</span>
<span class="n">name</span> <span class="o">=</span> <span class="n">models</span><span class="o">.</span><span class="n">CharField</span><span class="p">(</span><span class="n">max_length</span><span class="o">=</span><span class="mi">300</span><span class="p">)</span>
<span class="n">books</span> <span class="o">=</span> <span class="n">models</span><span class="o">.</span><span class="n">ManyToManyField</span><span class="p">(</span><span class="n">Book</span><span class="p">)</span>
</pre></div>
</div>
<div class="section" id="s-generating-aggregates-over-a-queryset">
<span id="generating-aggregates-over-a-queryset"></span><h2>Generating aggregates over a QuerySet<a class="headerlink" href="#generating-aggregates-over-a-queryset" title="Permalink to this headline">¶</a></h2>
<p>Django provides two ways to generate aggregates. The first way is to generate
summary values over an entire <tt class="docutils literal"><span class="pre">QuerySet</span></tt>. For example, say you wanted to
calculate the average price of all books available for sale. Django's query
syntax provides a means for describing the set of all books:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">Book</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
</pre></div>
</div>
<p>What we need is a way to calculate summary values over the objects that
belong to this <tt class="docutils literal"><span class="pre">QuerySet</span></tt>. This is done by appending an <tt class="docutils literal"><span class="pre">aggregate()</span></tt>
clause onto the <tt class="docutils literal"><span class="pre">QuerySet</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="kn">import</span> <span class="n">Avg</span>
<span class="gp">>>> </span><span class="n">Book</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">all</span><span class="p">()</span><span class="o">.</span><span class="n">aggregate</span><span class="p">(</span><span class="n">Avg</span><span class="p">(</span><span class="s">'price'</span><span class="p">))</span>
<span class="go">{'price__avg': 34.35}</span>
</pre></div>
</div>
<p>The <tt class="docutils literal"><span class="pre">all()</span></tt> is redundant in this example, so this could be simplified to:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">Book</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">aggregate</span><span class="p">(</span><span class="n">Avg</span><span class="p">(</span><span class="s">'price'</span><span class="p">))</span>
<span class="go">{'price__avg': 34.35}</span>
</pre></div>
</div>
<p>The argument to the <tt class="docutils literal"><span class="pre">aggregate()</span></tt> clause describes the aggregate value that
we want to compute - in this case, the average of the <tt class="docutils literal"><span class="pre">price</span></tt> field on the
<tt class="docutils literal"><span class="pre">Book</span></tt> model. A list of the aggregate functions that are available can be
found in the <a class="reference internal" href="../../ref/models/querysets.html#aggregation-functions"><em>QuerySet reference</em></a>.</p>
<p><tt class="docutils literal"><span class="pre">aggregate()</span></tt> is a terminal clause for a <tt class="docutils literal"><span class="pre">QuerySet</span></tt> that, when invoked,
returns a dictionary of name-value pairs. The name is an identifier for the
aggregate value; the value is the computed aggregate. The name is
automatically generated from the name of the field and the aggregate function.
If you want to manually specify a name for the aggregate value, you can do so
by providing that name when you specify the aggregate clause:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">Book</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">aggregate</span><span class="p">(</span><span class="n">average_price</span><span class="o">=</span><span class="n">Avg</span><span class="p">(</span><span class="s">'price'</span><span class="p">))</span>
<span class="go">{'average_price': 34.35}</span>
</pre></div>
</div>
<p>If you want to generate more than one aggregate, you just add another
argument to the <tt class="docutils literal"><span class="pre">aggregate()</span></tt> clause. So, if we also wanted to know
the maximum and minimum price of all books, we would issue the query:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="kn">import</span> <span class="n">Avg</span><span class="p">,</span> <span class="n">Max</span><span class="p">,</span> <span class="n">Min</span><span class="p">,</span> <span class="n">Count</span>
<span class="gp">>>> </span><span class="n">Book</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">aggregate</span><span class="p">(</span><span class="n">Avg</span><span class="p">(</span><span class="s">'price'</span><span class="p">),</span> <span class="n">Max</span><span class="p">(</span><span class="s">'price'</span><span class="p">),</span> <span class="n">Min</span><span class="p">(</span><span class="s">'price'</span><span class="p">))</span>
<span class="go">{'price__avg': 34.35, 'price__max': Decimal('81.20'), 'price__min': Decimal('12.99')}</span>
</pre></div>
</div>
</div>
<div class="section" id="s-generating-aggregates-for-each-item-in-a-queryset">
<span id="generating-aggregates-for-each-item-in-a-queryset"></span><h2>Generating aggregates for each item in a QuerySet<a class="headerlink" href="#generating-aggregates-for-each-item-in-a-queryset" title="Permalink to this headline">¶</a></h2>
<p>The second way to generate summary values is to generate an independent
summary for each object in a <tt class="docutils literal"><span class="pre">QuerySet</span></tt>. For example, if you are retrieving
a list of books, you may want to know how many authors contributed to
each book. Each Book has a many-to-many relationship with the Author; we
want to summarize this relationship for each book in the <tt class="docutils literal"><span class="pre">QuerySet</span></tt>.</p>
<p>Per-object summaries can be generated using the <tt class="docutils literal"><span class="pre">annotate()</span></tt> clause.
When an <tt class="docutils literal"><span class="pre">annotate()</span></tt> clause is specified, each object in the <tt class="docutils literal"><span class="pre">QuerySet</span></tt>
will be annotated with the specified values.</p>
<p>The syntax for these annotations is identical to that used for the
<tt class="docutils literal"><span class="pre">aggregate()</span></tt> clause. Each argument to <tt class="docutils literal"><span class="pre">annotate()</span></tt> describes an
aggregate that is to be calculated. For example, to annotate Books with
the number of authors:</p>
<div class="highlight-python"><pre># Build an annotated queryset
>>> q = Book.objects.annotate(Count('authors'))
# Interrogate the first object in the queryset
>>> q[0]
<Book: The Definitive Guide to Django>
>>> q[0].authors__count
2
# Interrogate the second object in the queryset
>>> q[1]
<Book: Practical Django Projects>
>>> q[1].authors__count
1</pre>
</div>
<p>As with <tt class="docutils literal"><span class="pre">aggregate()</span></tt>, the name for the annotation is automatically derived
from the name of the aggregate function and the name of the field being
aggregated. You can override this default name by providing an alias when you
specify the annotation:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">q</span> <span class="o">=</span> <span class="n">Book</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span><span class="n">num_authors</span><span class="o">=</span><span class="n">Count</span><span class="p">(</span><span class="s">'authors'</span><span class="p">))</span>
<span class="gp">>>> </span><span class="n">q</span><span class="p">[</span><span class="mi">0</span><span class="p">]</span><span class="o">.</span><span class="n">num_authors</span>
<span class="go">2</span>
<span class="gp">>>> </span><span class="n">q</span><span class="p">[</span><span class="mi">1</span><span class="p">]</span><span class="o">.</span><span class="n">num_authors</span>
<span class="go">1</span>
</pre></div>
</div>
<p>Unlike <tt class="docutils literal"><span class="pre">aggregate()</span></tt>, <tt class="docutils literal"><span class="pre">annotate()</span></tt> is <em>not</em> a terminal clause. The output
of the <tt class="docutils literal"><span class="pre">annotate()</span></tt> clause is a <tt class="docutils literal"><span class="pre">QuerySet</span></tt>; this <tt class="docutils literal"><span class="pre">QuerySet</span></tt> can be
modified using any other <tt class="docutils literal"><span class="pre">QuerySet</span></tt> operation, including <tt class="docutils literal"><span class="pre">filter()</span></tt>,
<tt class="docutils literal"><span class="pre">order_by</span></tt>, or even additional calls to <tt class="docutils literal"><span class="pre">annotate()</span></tt>.</p>
</div>
<div class="section" id="s-joins-and-aggregates">
<span id="joins-and-aggregates"></span><h2>Joins and aggregates<a class="headerlink" href="#joins-and-aggregates" title="Permalink to this headline">¶</a></h2>
<p>So far, we have dealt with aggregates over fields that belong to the
model being queried. However, sometimes the value you want to aggregate
will belong to a model that is related to the model you are querying.</p>
<p>When specifying the field to be aggregated in an aggregate function, Django
will allow you to use the same <a class="reference internal" href="queries.html#field-lookups-intro"><em>double underscore notation</em></a> that is used when referring to related fields in
filters. Django will then handle any table joins that are required to retrieve
and aggregate the related value.</p>
<p>For example, to find the price range of books offered in each store,
you could use the annotation:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">Store</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span><span class="n">min_price</span><span class="o">=</span><span class="n">Min</span><span class="p">(</span><span class="s">'books__price'</span><span class="p">),</span> <span class="n">max_price</span><span class="o">=</span><span class="n">Max</span><span class="p">(</span><span class="s">'books__price'</span><span class="p">))</span>
</pre></div>
</div>
<p>This tells Django to retrieve the Store model, join (through the
many-to-many relationship) with the Book model, and aggregate on the
price field of the book model to produce a minimum and maximum value.</p>
<p>The same rules apply to the <tt class="docutils literal"><span class="pre">aggregate()</span></tt> clause. If you wanted to
know the lowest and highest price of any book that is available for sale
in a store, you could use the aggregate:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">Store</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">aggregate</span><span class="p">(</span><span class="n">min_price</span><span class="o">=</span><span class="n">Min</span><span class="p">(</span><span class="s">'books__price'</span><span class="p">),</span> <span class="n">max_price</span><span class="o">=</span><span class="n">Max</span><span class="p">(</span><span class="s">'books__price'</span><span class="p">))</span>
</pre></div>
</div>
<p>Join chains can be as deep as you require. For example, to extract the
age of the youngest author of any book available for sale, you could
issue the query:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">Store</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">aggregate</span><span class="p">(</span><span class="n">youngest_age</span><span class="o">=</span><span class="n">Min</span><span class="p">(</span><span class="s">'books__authors__age'</span><span class="p">))</span>
</pre></div>
</div>
</div>
<div class="section" id="s-aggregations-and-other-queryset-clauses">
<span id="aggregations-and-other-queryset-clauses"></span><h2>Aggregations and other QuerySet clauses<a class="headerlink" href="#aggregations-and-other-queryset-clauses" title="Permalink to this headline">¶</a></h2>
<div class="section" id="s-filter-and-exclude">
<span id="filter-and-exclude"></span><h3><tt class="docutils literal"><span class="pre">filter()</span></tt> and <tt class="docutils literal"><span class="pre">exclude()</span></tt><a class="headerlink" href="#filter-and-exclude" title="Permalink to this headline">¶</a></h3>
<p>Aggregates can also participate in filters. Any <tt class="docutils literal"><span class="pre">filter()</span></tt> (or
<tt class="docutils literal"><span class="pre">exclude()</span></tt>) applied to normal model fields will have the effect of
constraining the objects that are considered for aggregation.</p>
<p>When used with an <tt class="docutils literal"><span class="pre">annotate()</span></tt> clause, a filter has the effect of
constraining the objects for which an annotation is calculated. For example,
you can generate an annotated list of all books that have a title starting
with "Django" using the query:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">Book</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">name__startswith</span><span class="o">=</span><span class="s">"Django"</span><span class="p">)</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span><span class="n">num_authors</span><span class="o">=</span><span class="n">Count</span><span class="p">(</span><span class="s">'authors'</span><span class="p">))</span>
</pre></div>
</div>
<p>When used with an <tt class="docutils literal"><span class="pre">aggregate()</span></tt> clause, a filter has the effect of
constraining the objects over which the aggregate is calculated.
For example, you can generate the average price of all books with a
title that starts with "Django" using the query:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">Book</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">name__startswith</span><span class="o">=</span><span class="s">"Django"</span><span class="p">)</span><span class="o">.</span><span class="n">aggregate</span><span class="p">(</span><span class="n">Avg</span><span class="p">(</span><span class="s">'price'</span><span class="p">))</span>
</pre></div>
</div>
<div class="section" id="s-filtering-on-annotations">
<span id="filtering-on-annotations"></span><h4>Filtering on annotations<a class="headerlink" href="#filtering-on-annotations" title="Permalink to this headline">¶</a></h4>
<p>Annotated values can also be filtered. The alias for the annotation can be
used in <tt class="docutils literal"><span class="pre">filter()</span></tt> and <tt class="docutils literal"><span class="pre">exclude()</span></tt> clauses in the same way as any other
model field.</p>
<p>For example, to generate a list of books that have more than one author,
you can issue the query:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">Book</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span><span class="n">num_authors</span><span class="o">=</span><span class="n">Count</span><span class="p">(</span><span class="s">'authors'</span><span class="p">))</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">num_authors__gt</span><span class="o">=</span><span class="mi">1</span><span class="p">)</span>
</pre></div>
</div>
<p>This query generates an annotated result set, and then generates a filter
based upon that annotation.</p>
</div>
<div class="section" id="s-order-of-annotate-and-filter-clauses">
<span id="order-of-annotate-and-filter-clauses"></span><h4>Order of <tt class="docutils literal"><span class="pre">annotate()</span></tt> and <tt class="docutils literal"><span class="pre">filter()</span></tt> clauses<a class="headerlink" href="#order-of-annotate-and-filter-clauses" title="Permalink to this headline">¶</a></h4>
<p>When developing a complex query that involves both <tt class="docutils literal"><span class="pre">annotate()</span></tt> and
<tt class="docutils literal"><span class="pre">filter()</span></tt> clauses, particular attention should be paid to the order
in which the clauses are applied to the <tt class="docutils literal"><span class="pre">QuerySet</span></tt>.</p>
<p>When an <tt class="docutils literal"><span class="pre">annotate()</span></tt> clause is applied to a query, the annotation is
computed over the state of the query up to the point where the annotation
is requested. The practical implication of this is that <tt class="docutils literal"><span class="pre">filter()</span></tt> and
<tt class="docutils literal"><span class="pre">annotate()</span></tt> are not commutative operations -- that is, there is a
difference between the query:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">Publisher</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span><span class="n">num_books</span><span class="o">=</span><span class="n">Count</span><span class="p">(</span><span class="s">'book'</span><span class="p">))</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">book__rating__gt</span><span class="o">=</span><span class="mf">3.0</span><span class="p">)</span>
</pre></div>
</div>
<p>and the query:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">Publisher</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">book__rating__gt</span><span class="o">=</span><span class="mf">3.0</span><span class="p">)</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span><span class="n">num_books</span><span class="o">=</span><span class="n">Count</span><span class="p">(</span><span class="s">'book'</span><span class="p">))</span>
</pre></div>
</div>
<p>Both queries will return a list of Publishers that have at least one good
book (i.e., a book with a rating exceeding 3.0). However, the annotation in
the first query will provide the total number of all books published by the
publisher; the second query will only include good books in the annotated
count. In the first query, the annotation precedes the filter, so the
filter has no effect on the annotation. In the second query, the filter
preceeds the annotation, and as a result, the filter constrains the objects
considered when calculating the annotation.</p>
</div>
</div>
<div class="section" id="s-order-by">
<span id="order-by"></span><h3><tt class="docutils literal"><span class="pre">order_by()</span></tt><a class="headerlink" href="#order-by" title="Permalink to this headline">¶</a></h3>
<p>Annotations can be used as a basis for ordering. When you
define an <tt class="docutils literal"><span class="pre">order_by()</span></tt> clause, the aggregates you provide can reference
any alias defined as part of an <tt class="docutils literal"><span class="pre">annotate()</span></tt> clause in the query.</p>
<p>For example, to order a <tt class="docutils literal"><span class="pre">QuerySet</span></tt> of books by the number of authors
that have contributed to the book, you could use the following query:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">Book</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span><span class="n">num_authors</span><span class="o">=</span><span class="n">Count</span><span class="p">(</span><span class="s">'authors'</span><span class="p">))</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="s">'num_authors'</span><span class="p">)</span>
</pre></div>
</div>
</div>
<div class="section" id="s-values">
<span id="values"></span><h3><tt class="docutils literal"><span class="pre">values()</span></tt><a class="headerlink" href="#values" title="Permalink to this headline">¶</a></h3>
<p>Ordinarily, annotations are generated on a per-object basis - an annotated
<tt class="docutils literal"><span class="pre">QuerySet</span></tt> will return one result for each object in the original
<tt class="docutils literal"><span class="pre">QuerySet</span></tt>. However, when a <tt class="docutils literal"><span class="pre">values()</span></tt> clause is used to constrain the
columns that are returned in the result set, the method for evaluating
annotations is slightly different. Instead of returning an annotated result
for each result in the original <tt class="docutils literal"><span class="pre">QuerySet</span></tt>, the original results are
grouped according to the unique combinations of the fields specified in the
<tt class="docutils literal"><span class="pre">values()</span></tt> clause. An annotation is then provided for each unique group;
the annotation is computed over all members of the group.</p>
<p>For example, consider an author query that attempts to find out the average
rating of books written by each author:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">Author</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span><span class="n">average_rating</span><span class="o">=</span><span class="n">Avg</span><span class="p">(</span><span class="s">'book__rating'</span><span class="p">))</span>
</pre></div>
</div>
<p>This will return one result for each author in the database, annotated with
their average book rating.</p>
<p>However, the result will be slightly different if you use a <tt class="docutils literal"><span class="pre">values()</span></tt> clause:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">Author</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">values</span><span class="p">(</span><span class="s">'name'</span><span class="p">)</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span><span class="n">average_rating</span><span class="o">=</span><span class="n">Avg</span><span class="p">(</span><span class="s">'book__rating'</span><span class="p">))</span>
</pre></div>
</div>
<p>In this example, the authors will be grouped by name, so you will only get
an annotated result for each <em>unique</em> author name. This means if you have
two authors with the same name, their results will be merged into a single
result in the output of the query; the average will be computed as the
average over the books written by both authors.</p>
<div class="section" id="s-order-of-annotate-and-values-clauses">
<span id="order-of-annotate-and-values-clauses"></span><h4>Order of <tt class="docutils literal"><span class="pre">annotate()</span></tt> and <tt class="docutils literal"><span class="pre">values()</span></tt> clauses<a class="headerlink" href="#order-of-annotate-and-values-clauses" title="Permalink to this headline">¶</a></h4>
<p>As with the <tt class="docutils literal"><span class="pre">filter()</span></tt> clause, the order in which <tt class="docutils literal"><span class="pre">annotate()</span></tt> and
<tt class="docutils literal"><span class="pre">values()</span></tt> clauses are applied to a query is significant. If the
<tt class="docutils literal"><span class="pre">values()</span></tt> clause precedes the <tt class="docutils literal"><span class="pre">annotate()</span></tt>, the annotation will be
computed using the grouping described by the <tt class="docutils literal"><span class="pre">values()</span></tt> clause.</p>
<p>However, if the <tt class="docutils literal"><span class="pre">annotate()</span></tt> clause precedes the <tt class="docutils literal"><span class="pre">values()</span></tt> clause,
the annotations will be generated over the entire query set. In this case,
the <tt class="docutils literal"><span class="pre">values()</span></tt> clause only constrains the fields that are generated on
output.</p>
<p>For example, if we reverse the order of the <tt class="docutils literal"><span class="pre">values()</span></tt> and <tt class="docutils literal"><span class="pre">annotate()</span></tt>
clause from our previous example:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">Author</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span><span class="n">average_rating</span><span class="o">=</span><span class="n">Avg</span><span class="p">(</span><span class="s">'book__rating'</span><span class="p">))</span><span class="o">.</span><span class="n">values</span><span class="p">(</span><span class="s">'name'</span><span class="p">,</span> <span class="s">'average_rating'</span><span class="p">)</span>
</pre></div>
</div>
<p>This will now yield one unique result for each author; however, only
the author's name and the <tt class="docutils literal"><span class="pre">average_rating</span></tt> annotation will be returned
in the output data.</p>
<p>You should also note that <tt class="docutils literal"><span class="pre">average_rating</span></tt> has been explicitly included
in the list of values to be returned. This is required because of the
ordering of the <tt class="docutils literal"><span class="pre">values()</span></tt> and <tt class="docutils literal"><span class="pre">annotate()</span></tt> clause.</p>
<p>If the <tt class="docutils literal"><span class="pre">values()</span></tt> clause precedes the <tt class="docutils literal"><span class="pre">annotate()</span></tt> clause, any annotations
will be automatically added to the result set. However, if the <tt class="docutils literal"><span class="pre">values()</span></tt>
clause is applied after the <tt class="docutils literal"><span class="pre">annotate()</span></tt> clause, you need to explicitly
include the aggregate column.</p>
</div>
<div class="section" id="s-interaction-with-default-ordering-or-order-by">
<span id="interaction-with-default-ordering-or-order-by"></span><h4>Interaction with default ordering or <tt class="docutils literal"><span class="pre">order_by()</span></tt><a class="headerlink" href="#interaction-with-default-ordering-or-order-by" title="Permalink to this headline">¶</a></h4>
<p>Fields that are mentioned in the <tt class="docutils literal"><span class="pre">order_by()</span></tt> part of a queryset (or which
are used in the default ordering on a model) are used when selecting the
output data, even if they are not otherwise specified in the <tt class="docutils literal"><span class="pre">values()</span></tt>
call. These extra fields are used to group "like" results together and they
can make otherwise identical result rows appear to be separate. This shows up,
particularly, when counting things.</p>
<p>By way of example, suppose you have a model like this:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Item</span><span class="p">(</span><span class="n">models</span><span class="o">.</span><span class="n">Model</span><span class="p">):</span>
<span class="n">name</span> <span class="o">=</span> <span class="n">models</span><span class="o">.</span><span class="n">CharField</span><span class="p">(</span><span class="n">max_length</span><span class="o">=</span><span class="mi">10</span><span class="p">)</span>
<span class="n">data</span> <span class="o">=</span> <span class="n">models</span><span class="o">.</span><span class="n">IntegerField</span><span class="p">()</span>
<span class="k">class</span> <span class="nc">Meta</span><span class="p">:</span>
<span class="n">ordering</span> <span class="o">=</span> <span class="p">[</span><span class="s">"name"</span><span class="p">]</span>
</pre></div>
</div>
<p>The important part here is the default ordering on the <tt class="docutils literal"><span class="pre">name</span></tt> field. If you
want to count how many times each distinct <tt class="docutils literal"><span class="pre">data</span></tt> value appears, you might
try this:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># Warning: not quite correct!</span>
<span class="n">Item</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">values</span><span class="p">(</span><span class="s">"data"</span><span class="p">)</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span><span class="n">Count</span><span class="p">(</span><span class="s">"id"</span><span class="p">))</span>
</pre></div>
</div>
<p>...which will group the <tt class="docutils literal"><span class="pre">Item</span></tt> objects by their common <tt class="docutils literal"><span class="pre">data</span></tt> values and
then count the number of <tt class="docutils literal"><span class="pre">id</span></tt> values in each group. Except that it won't
quite work. The default ordering by <tt class="docutils literal"><span class="pre">name</span></tt> will also play a part in the
grouping, so this query will group by distinct <tt class="docutils literal"><span class="pre">(data,</span> <span class="pre">name)</span></tt> pairs, which
isn't what you want. Instead, you should construct this queryset:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Item</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">values</span><span class="p">(</span><span class="s">"data"</span><span class="p">)</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span><span class="n">Count</span><span class="p">(</span><span class="s">"id"</span><span class="p">))</span><span class="o">.</span><span class="n">order_by</span><span class="p">()</span>
</pre></div>
</div>
<p>...clearing any ordering in the query. You could also order by, say, <tt class="docutils literal"><span class="pre">data</span></tt>
without any harmful effects, since that is already playing a role in the
query.</p>
<p>This behavior is the same as that noted in the queryset documentation for
<a class="reference internal" href="../../ref/models/querysets.html#django.db.models.QuerySet.distinct" title="django.db.models.QuerySet.distinct"><tt class="xref py py-meth docutils literal"><span class="pre">distinct()</span></tt></a> and the general rule is the same:
normally you won't want extra columns playing a part in the result, so clear
out the ordering, or at least make sure it's restricted only to those fields
you also select in a <tt class="docutils literal"><span class="pre">values()</span></tt> call.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">You might reasonably ask why Django doesn't remove the extraneous columns
for you. The main reason is consistency with <tt class="docutils literal"><span class="pre">distinct()</span></tt> and other
places: Django <strong>never</strong> removes ordering constraints that you have
specified (and we can't change those other methods' behavior, as that
would violate our <a class="reference internal" href="../../misc/api-stability.html"><em>API stability</em></a> policy).</p>
</div>
</div>
</div>
<div class="section" id="s-aggregating-annotations">
<span id="aggregating-annotations"></span><h3>Aggregating annotations<a class="headerlink" href="#aggregating-annotations" title="Permalink to this headline">¶</a></h3>
<p>You can also generate an aggregate on the result of an annotation. When you
define an <tt class="docutils literal"><span class="pre">aggregate()</span></tt> clause, the aggregates you provide can reference
any alias defined as part of an <tt class="docutils literal"><span class="pre">annotate()</span></tt> clause in the query.</p>
<p>For example, if you wanted to calculate the average number of authors per
book you first annotate the set of books with the author count, then
aggregate that author count, referencing the annotation field:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">Book</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span><span class="n">num_authors</span><span class="o">=</span><span class="n">Count</span><span class="p">(</span><span class="s">'authors'</span><span class="p">))</span><span class="o">.</span><span class="n">aggregate</span><span class="p">(</span><span class="n">Avg</span><span class="p">(</span><span class="s">'num_authors'</span><span class="p">))</span>
<span class="go">{'num_authors__avg': 1.66}</span>
</pre></div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<div class="yui-b" id="sidebar">
<div class="sphinxsidebar">
<div class="sphinxsidebarwrapper">
<h3><a href="../../contents.html">Table Of Contents</a></h3>
<ul>
<li><a class="reference internal" href="#">Aggregation</a><ul>
<li><a class="reference internal" href="#generating-aggregates-over-a-queryset">Generating aggregates over a QuerySet</a></li>
<li><a class="reference internal" href="#generating-aggregates-for-each-item-in-a-queryset">Generating aggregates for each item in a QuerySet</a></li>
<li><a class="reference internal" href="#joins-and-aggregates">Joins and aggregates</a></li>
<li><a class="reference internal" href="#aggregations-and-other-queryset-clauses">Aggregations and other QuerySet clauses</a><ul>
<li><a class="reference internal" href="#filter-and-exclude"><tt class="docutils literal"><span class="pre">filter()</span></tt> and <tt class="docutils literal"><span class="pre">exclude()</span></tt></a><ul>
<li><a class="reference internal" href="#filtering-on-annotations">Filtering on annotations</a></li>
<li><a class="reference internal" href="#order-of-annotate-and-filter-clauses">Order of <tt class="docutils literal"><span class="pre">annotate()</span></tt> and <tt class="docutils literal"><span class="pre">filter()</span></tt> clauses</a></li>
</ul>
</li>
<li><a class="reference internal" href="#order-by"><tt class="docutils literal"><span class="pre">order_by()</span></tt></a></li>
<li><a class="reference internal" href="#values"><tt class="docutils literal"><span class="pre">values()</span></tt></a><ul>
<li><a class="reference internal" href="#order-of-annotate-and-values-clauses">Order of <tt class="docutils literal"><span class="pre">annotate()</span></tt> and <tt class="docutils literal"><span class="pre">values()</span></tt> clauses</a></li>
<li><a class="reference internal" href="#interaction-with-default-ordering-or-order-by">Interaction with default ordering or <tt class="docutils literal"><span class="pre">order_by()</span></tt></a></li>
</ul>
</li>
<li><a class="reference internal" href="#aggregating-annotations">Aggregating annotations</a></li>
</ul>
</li>
</ul>
</li>
</ul>
<h3>Browse</h3>
<ul>
<li>Prev: <a href="queries.html">Making queries</a></li>
<li>Next: <a href="managers.html">Managers</a></li>
</ul>
<h3>You are here:</h3>
<ul>
<li>
<a href="../../index.html">Django v1.2 documentation</a>
<ul><li><a href="../index.html">Using Django</a>
<ul><li><a href="index.html">Models and databases</a>
<ul><li>Aggregation</li></ul>
</li></ul></li></ul>
</li>
</ul>
<h3>This Page</h3>
<ul class="this-page-menu">
<li><a href="../../_sources/topics/db/aggregation.txt"
rel="nofollow">Show Source</a></li>
</ul>
<div id="searchbox" style="display: none">
<h3>Quick search</h3>
<form class="search" action="../../search.html" method="get">
<input type="text" name="q" size="18" />
<input type="submit" value="Go" />
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
<p class="searchtip" style="font-size: 90%">
Enter search terms or a module, class or function name.
</p>
</div>
<script type="text/javascript">$('#searchbox').show(0);</script>
</div>
</div>
<h3>Last update:</h3>
<p class="topless">Oct 20, 2010</p>
</div>
</div>
<div id="ft">
<div class="nav">
« <a href="queries.html" title="Making queries">previous</a>
|
<a href="../index.html" title="Using Django" accesskey="U">up</a>
|
<a href="managers.html" title="Managers">next</a> »</div>
</div>
</div>
<div class="clearer"></div>
</div>
</body>
</html>
distrib
>
Arklinux
>
devel
>
i586
>
media
>
main
>
by-pkgid
>
5fcb1fedf34660bc240dc59b7bfcebc4
>
files
>
435
