Aggregation over a few models - Django

Question

Hi folks,

I'm trying to compute the average of a field over various subsets of a queryset.

Player.objects.order_by('-score').filter(sex='male').aggregate(Avg('level'))

This works perfectly!

---

But... if I try to compute it for the top 50 players it does not work.

Player.objects.order_by('-score').filter(sex='male')[:50].aggregate(Avg('level'))

This last one returns the exact same result as the query above it, which is wrong.

---

What am I doing wrong?

Help would be very much appreciated!

Answer 1

topfifty = Player.objects.order_by('-score')[:50]
Player.objects.filter(sex='male',pk__in=topfifty).aggregate(avglevel=Avg('level'))

edit: i haven't tested this, but i think you get the idea of where i'm going.

topfifty = Player.objects.order_by('-score')[:50]
ids = []
for t in topfifty:
    ids += [t.id]

Player.objects.filter(sex='male',pk__in=ids).aggregate(avglevel=Avg('level'))

it's kind of hackish but the best i can think of. perhaps consider filtering for male and taking the top 50 males instead of this which takes the top fifty and then filters out the males.

Answer 2

Hmm. The docs say

"Slicing. As explained in Limiting QuerySets, a QuerySet can be sliced, using Python's array-slicing syntax. Usually slicing a QuerySet returns another (unevaluated) QuerySet, but Django will execute the database query if you use the "step" parameter of slice syntax." http://docs.djangoproject.com/en/dev/ref/models/querysets/

So I'd try

Player.objects.order_by('-score').filter(sex='male')[0:50:1].aggregate(Avg('level'))

Answer 3

Break the problem up into two logical steps; figure out the target set, perform the aggregate calculation.

top50_male_players = Player.objects.filter(sex='male').order_by('-score')[:50]
result = Player.objects.filter(pk__in=top50_male_players).aggregate(Avg('level'))

Thanks to lazy evaluation of the QuerySet it will be performed in a single DB operation. You can play with this in the shell to verify the query counts.

> from django.db import connection
> connection.queries = []
> top50_male_players = Player.objects.filter(sex='male').order_by('-score')[:50]
> len(connection.queries)
0
> result = Player.objects.filter(pk__in=top50_male_players).aggregate(Avg('level'))
> len(connection.queries)
1
> result
{'level__avg': <some number>}
> len(connection.queries)
1