Avoiding O(n) queries with Django

Question

I've got models like this:

class PledgeItem(models.Model):
    title = models.CharField(...)
    usd_amount = models.DecimalField(...)

class Pledger(models.Model):
    name = models.CharField(...)
    ...

class Pledge(models.Model):
    pledger = models.ForeignKey(Pledger)
    item    = models.ForeignKey(PledgeItem)
    usd_amount = models.DecimalField(...)
    ...

My PledgeItem has a method to work out what percentage of it is pledged (e.g. an item might cost $100, and have 3 pledges of $20 each, meaning it is 60% pledged):

 class PledgeItem(models.Model):
     ...
     def percentage_pledged(self):
         pledge_total = Pledge.objects.filter(item = self).sum(usd_amount)
         return (pledge_total / self.usd_amount) * 100

For the purposes of this question, please assume I properly handle self.usd_amount being zero, and the case where there are no Pledges on the PledgeItem (though I've got to ask, why does sum(field) return None in those cases?).

Problem is, if I call percentage_pledged in a list of n PledgeItems, I have one query per PledgeItem. Is there an elegant way to resolve this without using save signals to update a percentage_pledged field? It'd be nice if I could prefetch that data somehow (i.e. fetch all Pledges in one go, and then loop through them).

I'm not sure what a solution would even look like (for example, where would that set of Pledges live?), but I'm sure this is a common issue (and one that has bugged me before), so I thought I'd see how people more experienced with Django have solved it. Maybe save signals are where this belongs, particularly for "low-write, high-read" type sites.

Answer 1

This is job for the new aggregation features in Django 1.1.

You want to 'annotate' a pledge_sum field to every PledgeItem in a queryset. This is easily done:

from django.db.models import Sum
PledgeItems.objects.all().annotate(pledge_sum=Sum(pledge__usdamount))

Obviously you can replace all() with whatever filters you want.

You'll still need to do the percentage calculation on each PledgeItem, but it won't result in any extra queries.