use cases for #14030

examples.py

"""
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()