python / expert
Snippet
Custom SQL Aggregates with Conditional FILTER Clauses in Django ORM
Custom SQL aggregates allow extending Django's ORM expression capabilities. Subclassing `Aggregate` with a custom template allows compiling PostgreSQL native aggregate `FILTER (WHERE ...)` clauses directly into SQL queries.
snippet.py
python
1
2
3
4
5
6
7
8
9
10
11
12
13
14
from django.db.models import Aggregate, FloatFieldclass FilteredAvg(Aggregate):function = 'AVG'template = '%(function)s(%(expressions)s) FILTER (WHERE %(condition)s)'output_field = FloatField()def __init__(self, expression, condition, **extra):super().__init__(expression, condition=condition, **extra)# Usage Example:# Article.objects.aggregate(# avg_high_rating=FilteredAvg('rating', condition="rating >= 4.0")# )
django
Breakdown
1
class FilteredAvg(Aggregate):
Subclasses Django's aggregate expression base class to introduce conditional SQL aggregations.
2
template = '%(function)s(%(expressions)s) FILTER (WHERE %(condition)s)'
Defines the raw SQL string template using PostgreSQL FILTER syntax for conditional aggregates.
3
super().__init__(expression, condition=condition, **extra)
Passes the target expression and filter condition keyword argument to the Django ORM query compiler.