I have the following models:-
class Group(models.Model):
name = models.CharField(max_length=200)
class Game(models.Model):
group = models.ForeignKey(Group, related_name='games')
date = models.DateField()
players = models.ManyToManyField(
Player, through='GameMembership', related_name='games'
)
class GameMembership(models.Model):
game = models.ForeignKey(Game, related_name='memberships')
player = models.ForeignKey(Player, related_name='memberships')
selected = models.BooleanField(default=False)
injured = models.BooleanField(default=False)
class Player(models.Model):
group = models.ForeignKey('groups.Group', related_name='players')
user = models.ForeignKey(settings.AUTH_USER_MODEL, related_name='players')
I want to annotate all of the players in the group with a score which is calculated as follows:-
For the last 10 games for which a player wasn't injured, score 5 if they were selected.
I can do this using Sum/Case/When if I ignore the "wasn't injured" clause, by using a manager method on Player which looks something like this:-
def with_availability_scores(self, group):
for_games = group.games.reverse()[:10]
return self.annotate(
availability_score=Sum(Case(
When(
memberships__selected=True, memberships__game__in=for_games,
then=5)
default=0, output_field=IntegerField()))
)
But the addition of the "injured" clause means that I can't use that for_games variable like that to begin with.
I suspect it can be done using Subquery and OuterRef but I can't quite figure out the exact syntax I need.
Any ideas?