Escher Escher - 5 months ago 56
Python Question

Is it possible to GROUP BY an aggregate query with django ORM?

I'm trying to calculate the equivalent of

SELECT SUM(...) FROM ... GROUP BY ...
. Here's a simplified analogy:

Let's say
Salesperson
objects sell stuff and get a commission on the margin they generate through each
Sale
:

sp = Salesperson.objects.get(pk=1)
my_sales = Sale.objects.filter(fk_salesperson=sp)

#calculate commission owing to sp
commission = 0
for sale in my_sales:
commission += sp.commission_rate\
* (sale.selling_price - sale.cost_price)


That last loop could be done with something like:

.annotate( commission= ( F('selling_price')-F('cost_price') )\
* sp.commission_rate )


But can I then further aggregate the query for all
Salesperson
objects? I.e. I want to know every salesperson's commission (i.e. roughly
SELECT SUM( (sale_price-cost_price) * commission_rate) FROM Sales GROUP BY Salesperson
). I could do something like below, but I'm trying to do it with ORM:

commissions = []
salespeople = Salesperson.objects.all()
for sp in salespeople:
data = Sale.objects.filter(fk_salesperson=sp)\
.annotate(salesperson=F('sp__email')\
.annotate(commission= ( F('selling_price')-F('cost_price') )\
* sp.commission_rate )
commissions.append(data)


Is there a way to do this with a single query (making the reporting db server do the work) rather than doing it on my application server?

Answer

The Sum() aggregate function is available in django.db.models.

from django.db.models import F, Sum

Sales.objects.values('salesperson__id').annotate(commission=Sum(
    (F('selling_price') - F('cost_price')) * F('salesperson__commission_rate')
))

Of course, replace sales__ by your actual related name, according to your models definition.

Comments