28

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.

double-beep
  • 5,031
  • 17
  • 33
  • 41
okoman
  • 5,529
  • 11
  • 41
  • 45

3 Answers3

47

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' )
Alex Koshelev
  • 16,879
  • 2
  • 34
  • 28
  • I'm getting the results out of order... they're mostly in order, but some of them are misplaced... what could cause that? – Jiaaro Jul 16 '09 at 14:46
  • This is the solution only for that specific case, and not for the question title: "QuerySet order by method". This is NOT the solution to order a queryset by method: @S.Lott response is the right solution for the generic case – Stefan Manastirliu Feb 01 '13 at 12:36
9

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.

S.Lott
  • 384,516
  • 81
  • 508
  • 779
  • Yes, you can. You just do the sum as part of the query. – Aaron Maenpaa Jan 24 '09 at 13:36
  • 'RelatedManager' object has no attribute 'sort' :( – okoman Jan 24 '09 at 13:45
  • 2
    @okoman... that means you didn't convert to a list using the list() function – Jiaaro Jul 16 '09 at 14:47
  • I don't know why this is getting downvoted. It happens to be in this case that you can sort by aggregating a certain column in the query, but generally if you have anything more complex, you are SOL. This solution works fine, though it's pretty expensive... – agscala Nov 12 '10 at 20:56
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.

Alex Koshelev
  • 16,879
  • 2
  • 34
  • 28