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