Django: sum annotation on relation with a limit clause

user_229511 0
Viewed 614

I have a situation similar to the following one:

class Player(models.Model):
   pass

class Item(models.Model):
   player = models.ForeignKey(Player,
                              on_delete=models.CASCADE,
                              related_name='item_set')
   power = models.IntegerField()

I would like to annotate Player.objects.all() with Sum(item_set__power), taking into account only the top N Item when sorted by descending power. Ideally, I would like to do this with a subquery, but I don't know how to write it. How can this be done?

django-queryset django python
1 Answers

This is the solution using raw queryset (it was easier to implement for me than using ORM, but it might be possible with ORM using Subquery):

N = 2
query = """
SELECT id,
   (
       SELECT SUM(power) 
         FROM (SELECT power FROM myapp_item WHERE myapp_item.player_id = players.id ORDER BY power DESC LIMIT %s)
   )
   AS power__sum
FROM myapp_player AS players GROUP BY players.id
"""
players = Player.objects.raw(query, [N])

Update

Adding annotations is not possible with RawQueryset, but you can use RawSQL expression:

from django.db.models.expressions import RawSQL

N = 2
queryset = Player.objects.all()
query2 = """
SELECT SUM(power) 
  FROM (SELECT  power FROM myapp_item WHERE myapp_item.player_id = myapp_player.id ORDER BY power DESC LIMIT %s)
"""
queryset.annotate(power__sum=RawSQL(query2, (N,)), my_annotation1=..., my_annotation2=...)
user_72617570
Related