[Django]-Django Aggregation for Goals

by

3👍

✅

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.

👤knbk

2👍

Without a good look at your models the best I can do is pseudocode. But I would expect you need something along the lines of 

from django.db.models import Sum
results = Sales.objects.filter(date__month=month, date__year=year)
results = results.values('store')
results = results.annotate(total_sales=Sum(sale))
return    results.filter(total_sales__gt=100)

Basically, what we’re doing is using django’s aggregation capabilities to compute the Sum of sales for each store. Per django’s documentation, we can use the values function to group our results by distinct values in a given field.

  • In line 2 we filter our sales to only sales from this month.
  • In line 3, we limit our results to the values for field store.
  • In line 4, we annotate each result with the Sum of all sales from the
    original query.
  • In line 5, we filter on that annotation, limiting the returned results to stores with total_sales greater than 100.
👤mklauber

0👍

You can use annotate to handle this. Since I do not know your model structure, That is an average guess

from djnago.db.models import Sum
Sales.objects.filter(date__month=3, date__year=2014).values('store').annotate(monthly_sale=Sum('sale'))

That will return you a Queryset of Stores and their monthly sales like:

>> [
    {"store": 1, "monthly_sale": 120.000},
    {"store": 2, "monthly_sale": 100.000},
    ...
]

In above query assume you have:

  • Sales model have a Date or Datetime field named date
  • Your Sale model have a ForeignKey relation to Store
  • Your Sales model have a numeric field (Integer, Decimal etc.) named sale

In your resulting QuerySet, store is the id of your store record. But since it is a ForeigKey, you can use relation to get its name etc…

Sales.objects.filter(date__month=3, date__year=2014).values('store__name').annotate(monthly_sale=Sum('sale'))


>> [
    {"store__name": "Store A", "monthly_sale": 120.000},
    {"store__name": "Store B", "monthly_sale": 100.000},
    ...
]
👤Mp0int

Leave a comment