web developer & system programmer

coder . cl

ramblings and thoughts on programming...


custom aggregates on django

published: 28-09-2011 / updated: 28-09-2011
posted in: development, programming, python, tips
by Daniel Molina Wegener

Python provides a query API through its ORM library. All models can define queries using the most common aggregates like Sum, Avg, Max, Min and Count. Since you cannot use SQL functions with Django aggregates, you must define your own aggregates to be used with functions. For example in one of my recent projects I’ve used some MySQL functions — and PostgreSQL functions on the PostgreSQL version — with the Sum and Avg aggregates. You can build your own Aggregates and place them, for example, on the app/aggregates.py module.

Django works using two layers to build queries. The first layer is model descriptive and the second one is the SQL compiler, which has its basis on the model description layer to construct each query. So you need to define both classes to build your own Aggregate — and skip the Django side processing that requires some complex queries. The first one is the SQL layer, which must define the proper SQL elements to be used to construct the query aggregate.

from django.db import models


class SumWithFunctionSQL(models.sql.aggregates.Aggregate):
    sql_function = 'SUM'
    sql_template = '%(function)s( %(proc_func)s( %(field)s ) )'


class SumWithFunction(models.Aggregate):
    name = 'Sum'

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

On the example above, we will provide proc_func extra argument to the aggregate constructor, so we will be able to use the Sum aggregate wrapping a function call on each processed row, which will be provided by the proc_func argument. On the next example, we will see an If construct to be used with MySQL, which can be replaced by Case statements on PostgreSQL.

from django.db import models
from django.conf import settings

sum_if_sql_template = '%(%s)s(IF(%(%s)s, %(%s)s, %(%s)s))' % 
                      ('function',
                       'condition',
                       'when_true',
                       'when_false')

class SumIfSQL(models.sql.aggregates.Aggregate):
    sql_function = 'SUM'
    sql_template = sum_if_sql_template


class SumIf(models.Aggregate):
    name = 'Sum'

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

The example above uses the condition, when_true and when_false extra arguments to be passed to the aggregate constructor, where the condition holds the SQL condition which must meet the SQL layer, when_true the value to be used in the Sum aggregate when the condition evaluates to true and when_false hold the value to be used when the condition evaluates to false.

query = AccountDetail.
        objects.
        values('account', 'currency', 'interest', 'dividend').
        annotate(quantity_sum=Sum('quantity'),
                 amount_sum=Sum('amount'),
                 applied_interest=SumIf('amount',
                                        condition='amount > 0.0',
                                        when_true='amount * interest',
                                        when_false='0.0'),
                 applied_dividend=SumIf('amount',
                                        condition='amount < 0.0',
                                        when_true='amount * dividend',
                                        when_false='0.0')).
        order_by('account', 'currency', 'interest', 'dividend').
        filter(**filter)
details = query.all()
return details

On the example above we have made a simple usage of the SumIf aggregate. I hope that you will like how it works and build a similar solution. The examples here do are not using the SQL layer compiler due to its complexity, but if you study enough you will be able to use the SQL compiler.


No coments yet.

post a comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>