0

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?

bodger
  • 1,112
  • 6
  • 24
  • just to have a clear overview what do you want to achieve - for the last 10 games, you want to get selected players, and for each of them add 0 annotation if `injured` is True, 5 otherwise, correct ? – dydek Sep 19 '18 at 09:51
  • Ah no. That would make my life a lot easier! I want each player's last 10 games to not include the ones where they were injured. So the 10-game window might be different for each player. – bodger Sep 19 '18 at 10:48

0 Answers0