A QuerySet by aggregate 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 )

The users of a site can contribute their images to several contests. Then other users can vote them up or down.

Everything works fine, but now I want to display a page on which users can see all contributions to a certain contest. The images shall be ordered by their score. Therefore I have tried the following:

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

As I feared it doesn't work since 'score' is no database field that could be used in queries.

1
28
1/31/2019 5:27:36 PM

Accepted Answer

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

So query may look like this:

Contest.objects.get(pk=id).image_set.annotate(score=Sum('vote__value')).order_by( 'score' )
47
1/24/2009 1:44:37 PM

You can write your own sort 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 nicely because we sorted the list, which we're going to provide to the template.


Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon