I want to annotate a list of objects (each of which has a set tagsand an integer value points) so that they can be sorted by the total number of tags plus points.
However, I cannot find a way to annotate objects with the sum of two columns. Any idea on how I should do this?
These are the models that I use, and the function leaderboard()is the one with which I have difficulty working.
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
Ok, so I had to work extra by manually counting the number of tags and adding them 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',)
)
source
share