How can I sort a django queryset by the total of two fields?

Question

I want to annotate a list of objects (which each have a set of tags and an integer value points) so that they can be sorted by the total of count of the tags plus the points.

However, I cannot find any way to annotate the objects with the sum of two columns. Any idea as to how I should do this?

These are the models I am using, with the leaderboard() function being the one that I am having difficulty making work.

class Game (models.Model):
    users = models.ManyToManyField(User, through='Player', related_name='games')

    def leaderboard (self):
        """ Return a list of players ranked by score, where score is the total of the count of the players tags and their points. """
        leaderboard = self.player_set.annotate(
            tagcount=models.Count('tags')
        ).extra(
            select={'score': 'tagcount + points'},
            order_by=('score',)
        )
        return leaderboard

class Player (models.Model):
    game = models.ForeignKey(Game)
    user = models.ForeignKey(User)
    points = models.SmallIntegerField(default=0, help_text="Points that have been awarded to the player")

class Tag (models.Model):
    game = models.ForeignKey(Game, related_name='tags')
    player = ForeignKey(Player, related_name='tags')

Edit 2: Solution using extra

Okay, so I got extra to work, by manually counting the number of tags and adding that to the points.

def leaderboard (self):
    """ Return a list of players ranked by score, where score is the total of the count of the players tags and their points. """
    return self.players.extra(
        select={'score': '(select count(*) from kaos_tag where kaos_tag.tagger_id=kaos_player.id) + points'},
        order_by=('-score',)
    )

possible duplicate of [Django order_by sum of fields](http://stackoverflow.com/questions/3160798/django-order-by-sum-of-fields) — DrTyrsa, May 23 '12 at 10:46
@DrTyrsa I'm trying to do the same thing, but with one of those fields being a count as opposed to an actual field. — borntyping, May 23 '12 at 11:18
I'd go for denolmazition: add `tags_count` filed to the model and update it with signals. Or use [raw queries](https://docs.djangoproject.com/en/dev/topics/db/sql/) as an alternative. — DrTyrsa, May 23 '12 at 11:25

score 1 · Accepted Answer · answered May 23 '12 at 10:50

1

use extra

Django | QuerySet API reference | Django documentation -> https://docs.djangoproject.com/en/1.3/ref/models/querysets/#extra

players.objects.extra(
    select={'fieldsum':'tags__count + points'},
    order_by=('fieldsum',)
)

answered May 23 '12 at 10:50

Mohammad Efazati

4,812
2
35
50

@Efazati This doesn't work - the database doesn't do the same translation as django, and so this is thrown: `Caught DatabaseError while rendering: no such column: tags__count` – borntyping May 23 '12 at 11:14
@Ziaix add your model to your questuin – Mohammad Efazati May 23 '12 at 11:17
@Efazati I've updated it with a summary of the models being used. – borntyping May 23 '12 at 11:26

How can I sort a django queryset by the total of two fields?

1 Answers1