Django Aggregation for Goals

Question

I'm saving every Sale in a Store. I want to use aggregation to sum all of the sales in a month for every store. And i want to filter the stores that reach the goal (100.000$).

I've already came up with a solution using python and a list. But i wanted to know if there is a better solution using only the ORM.

Sales model

Store        Sale     Date
Store A      5.000    11/01/2014
Store A      3.000    11/01/2014
Store B      1.000    15/01/2014
Store C      8.000    17/01/2014
...

The result should be this:

Month: January

Store   Amount
A       120.000
B       111.000
C       150.000

and discard
D        70.000

Thanks for your help.

Answer 1

Other suggested methods discard a lot of data that takes a fraction of a second to load, and that could be useful later on in your code. Hence this answer.

Instead of querying on the Sales object, you can query on the Store object. The query is roughly the same, except for the relations:

from django.db.models import Sum

stores = Store.objects.filter(sales__date__month=month, sales__date__year=year) \
    .annotate(montly_sales=Sum('sales__amount')) \
    .filter(montly_sales__gte=100000) \
    # optionally prefetch all `sales` objects if you know you need them
    .prefetch_related('sales') 

>>> [s for s in stores]
[
    <Store object 1>,
    <Store object 2>,
    etc.
]

All Store objects have an extra attribute montly_sales that has the total amount of sales for that particular month. By filtering on month and year before annotating, the annotation only uses the filtered related objects. Note that the sales attribute on the store still contains all sales for that store.

With this method, all store attributes are easily accessible, unlike when you use .values to group your results.