CODEWITHSUNDEEP
django
Josh
Josh

Reputation: 285

Annotate queryset with max value of subqueryset

I have the following set of models:

Hikes(models.Model)
   name = ...
   difficulty = models.IntegerField

Regions(models.Model)
   hike = models.ManyToManyField(Hikes)

Now, I'm trying to return the most difficult hike within a region, but I need to display this on a queryset which returns multiple regions. I imagine the best way to do this would be to annotate each Region with a "hardesthike" field or something like that, but can't figure out the code. I can get the code to work for an individual region, but can't get it to work on a queryset of multiple regions.

My code for an individual region is:

q = Region.objects.get(id=1)
hikes = q.hike.all().values('difficulty')
max = hikes.aggregate(Max('difficulty'))

How can I annotate this new value onto the initial region? Preferably in simple integer form, so that I can sort by "Hardest Hike in a Region". Also, when I try to do any forloop in my views with any sort of aggregate function, I notice my webpage slows down substantially (I have 10k+ regions/hikes in my DB) -- any way around this?

Thanks!

Upvotes: 1

Views: 361

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476564

You can annotate with:

from django.db.models import Max

Regions.objects.annotate(
    max_hike=Max('hike__difficulty')
)

so if you are only interested in the Region with id=1:

from django.db.models import Max

Regions.objects.annotate(
    max_hike=Max('hike__difficulty')
).filter(pk=1)

or if you want to retrieve that Region object:

from django.db.models import Max

Regions.objects.annotate(
    max_hike=Max('hike__difficulty')
).get(pk=1)

Note: normally a Django model is given a singular name, so Region instead of Regions.

Upvotes: 1

Related Questions