QuerySet by cumulative field value

Question

Let's say I have the following model:

class Contest: title = models.CharField( max_length = 200 ) description = models.TextField() class Image: title = models.CharField( max_length = 200 ) description = models.TextField() contest = models.ForeignKey( Contest ) user = models.ForeignKey( User ) def score( self ): return self.vote_set.all().aggregate( models.Sum( 'value' ) )[ 'value__sum' ] class Vote: value = models.SmallIntegerField() user = models.ForeignKey( User ) image = models.ForeignKey( Image ) 

Site users can submit their images to several competitions. Then other users can vote for them up or down.

Everything works fine, but now I want to display a page on which users can see all contributions to a particular contest. Images should be sorted by rating. So I tried the following:

 Contest.objects.get( pk = id ).image_set.order_by( 'score' ) 

As I feared, this would not work, since 'score' is not a database field that could be used in queries.

Answer 1

Oh, of course, I forgot about the new aggregation support in Django and its annotate functionality.

So the query might look like this:

 Contest.objects.get(pk=id).image_set.annotate(score=Sum('vote__value')).order_by( 'score' ) 

Answer 2

You can write your own view in Python very simply.

 def getScore( anObject ): return anObject.score() objects= list(Contest.objects.get( pk = id ).image_set) objects.sort( key=getScore ) 

This works well because we sorted the list that we are going to provide to the template.

Answer 3

The db level order_by cannot sort the request request by the python model method.

The solution is to enter the score field in the Image model and recalculate it with every Vote update. Some denormalization. When you can sort by it.