Login

Aggregation class "Sum" with Case

Author:
marinho
Posted:
July 6, 2010
Language:
Python
Version:
1.1
Score:
0 (after 0 ratings)

Just use it like below:

from downloaded_file import SumCase
MyClass.objects.aggregate(
  sum1=SumCase('salary', case='salary < 4', when=True),
  sum1=SumCase('salary', case='type', when='director'),
)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
from django.db import models

class SQLSumCase(models.sql.aggregates.Aggregate):
    is_ordinal = True
    sql_function = 'SUM'
    sql_template = "%(function)s(CASE %(case)s WHEN %(when)s THEN %(field)s ELSE 0 END)"

    def __init__(self, col, **extra):
        if isinstance(extra['when'], basestring):
            extra['when'] = "'%s'"%extra['when']

        if not extra.get('case', None):
            extra['case'] = '"%s"."%s"'%(extra['source'].model._meta.db_table, extra['source'].name)

        if extra['when'] is None:
            extra['when'] = True
            extra['case'] += ' IS NULL '

        super(SQLSumCase, self).__init__(col, **extra)

class SumCase(models.Aggregate): # TODO
    name = 'SUM'

    def add_to_query(self, query, alias, col, source, is_summary):
        aggregate = SQLSumCase(col, source=source, is_summary=is_summary, **self.extra)
        query.aggregates[alias] = aggregate

More like this

  1. Template tag - list punctuation for a list of items by shapiromatron 10 months, 1 week ago
  2. JSONRequestMiddleware adds a .json() method to your HttpRequests by cdcarter 10 months, 2 weeks ago
  3. Serializer factory with Django Rest Framework by julio 1 year, 5 months ago
  4. Image compression before saving the new model / work with JPG, PNG by Schleidens 1 year, 6 months ago
  5. Help text hyperlinks by sa2812 1 year, 6 months ago

Comments

marinho (on July 9, 2010):

Fixed bug with NULL cases

#

stan (on October 11, 2011):

Useful.

But it does not works when you have join (Ambiguous column etc).

The following patch works only when the case in SumCase is about a column from the main table. I will try to fix the mymodel__myattr case later.

class SQLSumCase(models.sql.aggregates.Aggregate): is_ordinal = True sql_function = 'SUM' sql_template = "%(function)s(CASE %(case)s WHEN %(when)s THEN %(field)s ELSE 0 END)"

def __init__(self, col, **extra):
    if isinstance(extra['when'], basestring):
        extra['when'] = "'%s'"%extra['when']

    if not extra.get('case', None):
        extra['case'] = '"%s"."%s"'%(extra['source'].model._meta.db_table, extra['source'].name)
    # FIXME: handle relations via double underscores. 
    else:
        extra['case'] = '"%s"."%s"'%(extra['source'].model._meta.db_table, extra['case'])

    if extra['when'] is None:
        extra['when'] = True
        extra['case'] += ' IS NULL '

    super(SQLSumCase, self).__init__(col, **extra)

#

adickey (on March 17, 2014):

Nice custom aggregation example but I think for this custom aggregation to work properly 'is_ordinal' will have to be set to False. From the django source:

  • is_ordinal, a boolean indicating if the output of this aggregate is an integer (e.g., a count)

#

Please login first before commenting.