CODEWITHSUNDEEP
pythondjangodatabase
okoman
okoman

Reputation: 5657

A QuerySet by aggregate field value

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.

Upvotes: 28

Views: 29999

Answers (3)

S.Lott
S.Lott

Reputation: 391818

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.

Upvotes: 9

Alex Koshelev
Alex Koshelev

Reputation: 17449

The db-level order_by cannot sort queryset by model's python method.

The solution is to introduce score field to Image model and recalculate it on every Vote update. Some sort of denormalization. When you will can to sort by it.

Upvotes: 2

Alex Koshelev
Alex Koshelev

Reputation: 17449

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' )

Upvotes: 47

Related Questions