In Django, how can I get a count of records based on a subquery of a subquery?

A baseball player plays in a game if he makes one or more appearances in that game. So, to get a count of the number of games a player played in, you need to count the games that have an inning that have an appearance by that player.

Here are my models:

class Player(models.Model):
    ...

class Game(models.Model):
    ...

class Inning(models.Model):
    game = models.ForeignKey(Game, on_delete=models.CASCADE)

class Appearance(models.Model):
    inning = models.ForeignKey(Inning, on_delete=models.CASCADE)
    player = models.ForeignKey(Player, on_delete=models.CASCADE)

The SQL query to achieve what I want is:

SELECT COUNT(*)
FROM games_game
WHERE id IN (SELECT game_id
             FROM innings_inning
             WHERE id IN (SELECT inning_id
                          FROM appearances_appearance
                          WHERE player_id = 1))

How could I do this in Django without using Raw SQL?

Note that this is for a PlayerDetailView, so I just need it for a single Player object.

Answer

You can perform such count with:

Game.objects.filter(inning__appearance__player=my_player).distinct().count()

Where my_player is the player where you want to count the Games for.

Advertisement

Answer