Django: Caching aggregate query?

Question

I've got an Invoice model, and I want to keep the total up to date. So I've added a property like this

@property
def item_total(self):
    if not hasattr(self, 'item_total_cache'):
        self.item_total_cache = self.items.aggregate(t=Sum('amount'))['t']
    return self.item_total_cache

Because item_total might get called a few times during one page request and I don't want to hit the DB every time. Is this a good way to do it? Will my Invoice object exist beyond one page request and thus the item_total might actually get stale?

Ideally, I'd like to compute the item_total at the same time the invoices are requested... I know I can do this manually, but the Django admin site, for instance, doesn't know it's going to need the sum. Is there someway I can "inject" this extra tidbit of information so that all Invoice.objects.all/filter/get() requests run the aggregate at the same time?

Answer 1

Can I suggest another solution? Why not store the count and make it auto increment/decrement by using signals?

It depends on the amount of reads/writes you have ofcourse, but caching something like this is probably the more efficient solution. And possible quite a bit easyer too.

Either way, you are caching the count properly. The Invoice object shouldn't become stale unless you cache it some other way. This cache is bound to the instance and with a new request you can assume that you get a new instance aswell. Unless you're getting your items from memcached that is.

As for injecting the cache when needed. How would that work if you use get() or filter()? In that case your count would be lower and your cache incorrect. It should be fairly doable for all() though. You can simply overwrite the default manager to add this data automatically while fetching. But that would mean that for every Item you request, your Invoice will be fetched aswell.

Answer 2

This is a good solution, one I've used and recommended before. Your Invoice object won't persist beyond a single page request as long as you don't keep any references to it in a global scope - if it's just created in a view and passed to a template, it will be fine.

There are a couple of ways to get the admin site to generate the aggregate automatically. One is to define a custom Manager and override get_query_set to return self.annotate(Sum('amount')) - this will ensure that the aggregate is always created for every single query on the Invoice model, which may not be what you want.

An alternative is to define a separate method on the manager - with_aggregate, for example - which returns that, but then override the ModelAdmin's get_queryset method to return the result of that method. This gives you more control to decide whether or not to use the aggregate in your own views.