Skip to main content
Help us improve PyPI by participating in user testing. All experience levels needed!

Django aggregate functions which operate conditionally (i.e. generate SQL `CASE WHEN` statements)

Project description

Build Status Latest PyPI version

Python 2.7

Django 1.6 Django 1.7

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

Usage:

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

Project details


Release history Release notifications

This version
History Node

0.5.0

History Node

0.4.0

History Node

0.3.2

History Node

0.3.1

History Node

0.3.0

History Node

0.2.0

History Node

0.1.0

Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Filename, size & hash SHA256 hash help File type Python version Upload date
django-conditional-aggregates-0.5.0.tar.gz (4.4 kB) Copy SHA256 hash SHA256 Source None Jul 9, 2015

Supported by

Elastic Elastic Search Pingdom Pingdom Monitoring Google Google BigQuery Sentry Sentry Error logging CloudAMQP CloudAMQP RabbitMQ AWS AWS Cloud computing Fastly Fastly CDN DigiCert DigiCert EV certificate StatusPage StatusPage Status page