Login

Aggregation class "Count" with Case

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

Use it like below:

totals = MyClass.aggregate(
    is_enabled_yes=CountCase('is_enabled', when=True),
    is_enabled_no=CountCase('is_enabled', when=False),
    count_if=CountCase('id', case="another_field in ('a','b')", when=True),
)

 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 SQLCountCase(models.sql.aggregates.Aggregate):
    is_ordinal = True
    sql_function = 'COUNT'
    sql_template = "%(function)s(CASE %(case)s WHEN %(when)s THEN 1 ELSE null 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(SQLCountCase, self).__init__(col, **extra)

class CountCase(models.Aggregate):
    name = 'COUNT'

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

More like this

  1. Image compression before saving the new model / work with JPG, PNG by Schleidens 6 days, 4 hours ago
  2. Help text hyperlinks by sa2812 1 month ago
  3. Stuff by NixonDash 3 months, 1 week ago
  4. Add custom fields to the built-in Group model by jmoppel 5 months, 1 week ago
  5. Month / Year SelectDateWidget based on django SelectDateWidget by pierreben 8 months, 3 weeks ago

Comments

marinho (on July 9, 2010):

Fixed bug with NULL cases

#

Please login first before commenting.