Django aggregate functions which operate conditionally (i.e. generate SQL `CASE WHEN` statements)
(Django 1.4 and 1.5 are not possible due to limitation of older `SQLCompiler` class)
Note: from Django 1.8 on this module is not needed as support is built-in: https://docs.djangoproject.com/en/1.8/ref/models/conditional-expressions/#case
Sometimes you need some conditional logic to decide which related rows to ‘aggregate’ in your aggregation function.
In SQL you can do this with a CASE clause, for example:
SELECT stats_stat.campaign_id, SUM( CASE WHEN ( stats_stat.stat_type = a AND stats_stat.event_type = v ) THEN stats_stat.count ELSE 0 END ) AS impressions FROM stats_stat GROUP BY stats_stat.campaign_id
Note this is different to doing Django’s normal .filter(...).aggregate(Sum(...)) …what we’re doing is effectively inside the Sum(...) part of the ORM.
I believe these ‘conditional aggregates’ are most (perhaps only) useful when doing a GROUP BY type of query - they allow you to control exactly how the values in the group get aggregated, for example to only sum up rows matching certain criteria.
pip install django-conditional-aggregates
from django.db.models import Q from djconnagg import ConditionalSum # recreate the SQL example from above in pure Django ORM: report = ( Stat.objects .values('campaign_id') # values + annotate => GROUP BY .annotate( impressions=ConditionalSum( 'count', when=Q(stat_type='a', event_type='v') ), ) )
Note that standard Django Q objects are used to formulate the CASE WHEN(...) clause. Just like in the rest of the ORM, you can combine them with () | & ~ operators to make a complex query.
ConditionalSum and ConditionalCount aggregate functions are provided. There is also a base class if you need to make your own. The implementation of ConditionalSum is very simple and looks like this:
from djconnagg.aggregates import ConditionalAggregate, SQLConditionalAggregate class ConditionalSum(ConditionalAggregate): name = 'ConditionalSum' class SQLClass(SQLConditionalAggregate): sql_function = 'SUM' default = 0
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Hashes for django-conditional-aggregates-0.5.0.tar.gz