Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
use cases for #14030
"""
EXAMPLE 1: Get a report of online sales by month
------------------------------------------------ """
from django.db.models import Sum, DatePart, MONTH, YEAR
from store.models import Order
def get_monthly_sales():
# new class DatePart, subclass of ExpressionNode
# Add annotations for year and month, as well as an annotation
monthly_sales = Order.objects.annotate(
month=DatePart(MONTH, 'date_ordered'),
year=DatePart(YEAR, 'date_ordered')
)
# We can now specify these in .values() for grouping...
monthly_sales = monthly_sales.values('month', 'year')
# ...and then add the aggregate annotation:
monthly_sales = monthly_sales.annotate(month_total=Sum('total_amount'))
return monthly_sales.order_by('-year', '-month')
"""
>>> fmt = "{m.year}-{m.month}: {m.total_amount}"
>>> print "\n".join(fmt.format(m=m) for m in get_monthly_sales())
>>> 2014-01: 1234.56
>>> 2014-02: 2345.67
>>> 2014-03: 1212.12
"""
"""
EXAMPLE 2: Calculate line-item totals based on quantity
------------------------------------------------------- """
from django.db.models import F
from store.models import Order, OrderLineItem
def get_order_line_items(placed_order):
line_items = OrderLineItem.objects.filter(order=placed_order)
# Using F() expressions for annotations is intuitive enough
return line_items.annotate(line_total=F('quantity') * F('price'))
# We could even use these results to get the order total:
lines_items = get_order_line_items(placed_order)
order_total = line_items.aggregate(total=Sum('line_total'))['total']
"""
Example 3: Conditional expression
--------------------------------- """
from django.db.models import If, Case
# Why not add __gt__, __lt__, etc to ExpressionNode?
with_cateogry = products.annotate(category=If(F('cost') > 100, 'HIGH', 'LOW')))
# Or maybe we could have something like Case()?
cost_category = Case( # CASE
(F('cost') > 100, 'HIGH'), # WHEN cost > 100 THEN 'high'
(F('cost') > 50, 'MEDIUM'), # WHEN cost > 50 THEN 'med'
'LOW' # ELSE 'low'
)
with_category = products.annotate(category=category) # AS category
"""
Example 3: More possiblities...
------------------------------ """
from django.db.models import NullIf, Coalesce
# With more powerful expressions, we could fix #11305
ethnicity_counts = Members.annotate(
caucasian=Count(If(ethnicity == 'caucasian')),
african_american=Count(If(ethnicity == 'african-american'))
)
# Users could extend ExpressionNode for more obscure SQL functions
class Greatest(ExpressionNode):
def evaluate(self, evaluator, query, allow_joins):
# not sure how this would be implemeneted, but you get the idea
return self._evaluate_self()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.