Ordering by two different aggregate values of a relation's fields

Question

With the following code:

from django.db import models

class Blogpost(models.Model): pass

class Vote(models.Model):
    question = models.ForeignKey(Blogpost)
    TYPE_CHOICES = (
        ('up', 'Up-Votes'),
        ('dn', 'Down-Votes'),
    )
    type = models.CharField(max_length=2, choices=TYPE_CHOICES)

What is the best way to obtain a set of all Blogposts, ordered by up-votes first, then down-votes, without writing SQL through the QuerySet.extra() function?

Answer 1

Change the values of the vote choices so that they are integers that will sort in the desired manner.

For example, an up vote could be a 1, a down vote a -1, and the default value could be -2.

Then you can retrieve your results with a single order_by:

posts = Blogpost.objects.order_by('vote__type')

This gives the added advantage in that it becomes very easy to get a vote total using the Sum aggregate

from django.db.models import Sum
vote_score = Vote.objects.filter(question=blogpost_instance).filter(type__gte=-1).aggregate(Sum('type'))