Ordering a query by aggregate sum in Django, but not getting result as expected

Question

I am trying to create a Django site that shows a list of products, and orders them by the total score of votes they have received. 'Product' is one class, and 'Vote' is another, with a field called score which can be 1, 0, or -1. I want Products to be ordered according to the sum of the score for votes associated with each particular product.

Here is my models.py:

class Product(models.Model):
    Content = models.TextField()
    creation_date = models.DateTimeField( default=datetime.now )
    total_votes = models.IntegerField( default=0 )

    def __unicode__(self):
            return self.content

class Vote(models.Model):
        voter = models.ForeignKey( User )
        product = models.ForeignKey( Product )
        score = models.IntegerField( default=0, choices=VOTE_SCORE_CHOICES)
        def __unicode__(self):
                return '%s - %s : %s' % (self.product.id, self.voter, self.score)

And here's my views.py:

def show_products( request):
        product_list = Product.objects.all()

        # set the field 'total_votes' in prduct to the sum of votes for each sentence
        for p in product_list:
                try:
                        v  = Vote.objects.filter( product = p ).aggregate(Sum('score'))['score__sum']
                except IndexError:
                        v  = 0
                p.total_votes = v
        p.save()

        # get the product that has the highest vote score
        try:
                top_product = product_list.order_by('-total_votes')[0].content
        except IndexError:
                top_product = 'no product'

        # reorder product_list according to total_votes
        product_list = product_list.order_by('total_votes')

        return render_to_response('product/product_detail.html',
                        {'product_list': product_list,
                                'top_produce': top_product,}, context_instance=RequestContext(request))

So you see I'm getting the sum of the score for votes associated with each product, then assigning this number to each product's 'total_votes' field, and then reordering the product list according to 'total_votes'.

However, the results are not as expected, and the products get ordered separately from the vote scores. Can somebody tell me what's wrong with the code here? And also, is this the right way to be approaching this issue?

Thank you

Answer 1

This seems an unnecessarily complicated way to do this. You're aggregating votes for every product separately, saving the result in the product, then ordering by those vote sums.

Instead of that, seems like you should be doing it all in one go:

product_list = Product.objects.annotate(total_votes=Sum('vote__score')).order_by('total_votes')

(I'm presuming your initial reference to sentence and b are meant to refer to product and p respectively - otherwise you'll need to give more details about what these actually are.)