Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to prefetch aggregated @property in Django?

We have two models (simplified versions):

class Contestant(models.Model):
    email = models.EmailField(max_length=255, unique=True)
    # plus some other fields

    @property
    def total_points(self):
        return self.points.aggregate(total=Sum('value'))['total'] or 0

class Points(models.Model):
    contestant = models.ForeignKey(Contestant, related_name='points')
    value = models.PositiveIntegerField()
    # plus some other fields which determine based on what we
    # awarded ``Points.value``

When we display a list of contestants along with their total_points value, it results in an extra query for each result - i.e. the following queries are performed:

  1. fetch list of contestants
  2. fetch total_points value of 1st contestant
  3. fetch total_points value of 2nd contestant
  4. etc

I tried altering the queryset to prefetch the data as follows:

Contestant.objects.filter(...).prefetch_related('points')

..., however even though it works, the prefetched data is not utilized when listing contestants (so each result still tries to fetch total_points in a separate query).

Is it possible to:

  • somehow tell the ORM to use prefetched values for the @property field when populating data for individual model objects (e.g. access the prefetched value inside the Contestant.total_points @property method)?
  • or to prefetch them in a different way (as opposed to the example above)?
  • or to use a completely different approach achieving the same result?

(I'm listing results in tastypie, if it matters.)

Thank you.

like image 767
MicE Avatar asked Oct 01 '13 13:10

MicE


1 Answers

When your aim is to add aggregated values to each item, you should use annotate, instead of aggregate.

For example (a simple query, no additional methods required):

Contestant.objects.filter(...).annotate(total_points=Sum('points__value'))

If you really want to put this code out of your query: you can, but a model method is not a right way to do this. Methods on models are for operations on single instances. If you want to do something on a whole QuerySet use an ORM Manager instead.

With a Manager this would look like this:

class TotalPointsManager(models.Manager):
    def get_queryset(self):
        return super(TotalPointsManager, self).get_queryset().annotate(total_points=Sum('points__value'))

class Contestant(models.Model):
    email = models.EmailField(max_length=255, unique=True)

    objects = TotalPointsManager() # You are overriding the default manager!

and then you would construct your query as usual (you can drop prefetch_related):

Contestant.objects.filter(...)

...and total_points field would become "magically" available for every object.

like image 89
Ludwik Trammer Avatar answered Sep 18 '22 11:09

Ludwik Trammer