Django: QuerySet order by method

Question

Hi!

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 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.

Now my question: Is there a way to achieve what I want to do with Django functionality or must I fall back to SQL?

Answer 1

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.

Answer 2

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.

Answer 3

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