Django - ordering a filter by the sum of ForeignKey values?

Question

Hi!

I have the following model:

class User:
    name = models.CharField( max_length = 200 )
    problem_user = models.BooleanField(default=False)
    def points_total(self):
        points_added = PointsAdded.objects.filter(user=self)
        points_total = 0
        for point in points_added:
            points_total += point.points
        return points_total

class PointsAdded:
    points = models.IntegerField()
    user = models.ForeignKey( User )

I want to return a list of Users for whom problem_user is False, ordered by the points_total field.

I know that I need to use annotate (as described here) but I am a newbie and don't really know how to use it, because the example is slightly different from my case. This doesn't work:

leaders = VideoUser.objects.filter(problem_user=False).pointsadded_set. \
                .annotate(points_total=Sum('points__value')) \
                .order_by('points_total')

The alternative seems to be to write out the whole of my points_total function inside the annotate() section of the expression above - is that possible?

Is there a way to achieve what I want to do with Django functionality or must I fall back to SQL? Or should I redesign my database :)

Answer 1

You don't need the pointsadded_set. If I understand your models correctly, this should work:

VideoUser.objects.filter(problem_user=False) \
            .annotate(points_total=Sum('pointsadded__points__value')) \
            .order_by('points_total')