StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Prior to the introduction of annotations, you might have used <a href="http://docs.djangoproject.com/en/dev/ref/models/querysets/#extra-select-none-where-none-params-none-tables-none-order-by-none-select-params-none" rel="noreferrer"><code>extra</code></a> to do something like this, which I <em>think</em> should return <code>0</code> in cases where there are no votes (if it doesn't for any particular database implementation, you can at least directly insert the necessary <code>COALESCE</code> function call - <code>COALESCE(SUM(value), 0)</code> - using this method):</p> <pre><code>pictures = gallery.picture_set.extra( select={ 'score': 'SELECT SUM(value) FROM yourapp_picturevote WHERE yourapp_picturevote.picture_id = yourapp_picture.id', }, order_by=['-score'] ) </code></pre> <p>I can't see any built-in way to add your own SQL to the new annotation stuff (which I haven't personally used yet), but it looks like you should be able to create a new annotation like so:</p> <pre><code>from django.db.models import aggregates from django.db.models.sql import aggregates as sql_aggregates class SumWithDefault(aggregates.Aggregate): name = 'SumWithDefault' class SQLSumWithDefault(sql_aggregates.Sum): sql_template = 'COALESCE(%(function)s(%(field)s), %(default)s)' setattr(sql_aggregates, 'SumWithDefault', SQLSumWithDefault) </code></pre> <p>This looks rather ugly as you need to monkeypatch the new aggregate into <code>django.db.models.sql.aggregates</code> due to the way the SQL aggregate classes are looked up, but all we've done here is added a new aggregate which subclasses <code>Sum</code>, hardcoding a call to the <code>COALESCE</code> function and adding a placeholder for the default value, which you <em>must</em> supply as a keyword argument (in this very basic example implementation, at least).</p> <p>This should let you do the following:</p> <pre><code>pictures = gallery.picture_set.annotate(score=SumWithDefault('picturevote__value', default=0).order_by('-score') </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload