How to correctly annotate a QuerySet with the number of elements in a Many2Many relation?

user_1919237 0
Viewed 84

I'm modelling amateur soccer Games where two teams of players play against each other. I'm trying to annotate the QuerySet with the number of players in each team to then identify games with different number of players in each team.

The models I have are pretty straight forward:

class Player(models.Model):
    nickname = models.CharField(...)

class Team(models.Model):
    players = models.ManyToManyField(Player)

class Game(modeos.Model):
    team1 = models.ForeignKey(Team)
    team2 = models.ForeignKey(Team)

When I go to the shell (python manage shell) and execute the following command to get the number of players of the teams in each match I got the expected results:

[(game.team1.players.count(), game.team2.players.count()) for game in Game.objects.all()]

[(7, 7), (7, 10), (7, 7), (7, 7), (7, 7)]

However, when I try to do the same with annotations:

Game.objects.annotate(t1c=Count('team1__players'), t2c=Count('team2__players')).values('t1c','t2c')

I got incorrect results:

<QuerySet [{'t1c': 49, 't2c': 49}, {'t1c': 70, 't2c': 70}, {'t1c': 49, 't2c': 49}, {'t1c': 49, 't2c': 49}, {'t1c': 49, 't2c': 49}]>

Which are the multiplication of both values.

What am I doing wrong?

django-queryset django python
2 Answers

As documented

Combining multiple aggregations with annotate() will yield the wrong results because joins are used instead of subqueries

For most aggregates, there is no way to avoid this problem, however, the Count aggregate has a distinct parameter that may help

Game.objects.annotate(
        t1c=Count('team1__players', distinct=True),
        t2c=Count('team2__players', distinct=True)
    ).values('t1c','t2c')
user_25050710

As the other answer noted, the django docs point out that the result is incorrect because joins are used instead of subqueries. The ORM does provide a way to use subqueries instead, which is useful when distinct=True doesn't fix your problem (and in my experience, more performant than joins with distinct=True).

First, your model definition for Game is not correct. ForeignKey has to have on_delete defined, and when you have two ForeignKeys to the same model you have to define a related_name for at least one of them or you will get a conflict. So, I am using this as the definition of the Game model

class Game(models.Model):
    played = models.DateField()
    team1 = models.ForeignKey(Team, on_delete=CASCADE, related_name='team1_game')
    team2 = models.ForeignKey(Team, on_delete=CASCADE, related_name='team2_game')

To do your Count aggregation using a subquery:

t1c_count_query = Player.objects.filter(
    team__id=OuterRef('team1_id')
).values(
    'team__team1_game__id'  # Required for proper group by in the subquery
).annotate(
    player_count=Count('id')
).values(
    'player_count'  # Required to select only a single value in a subquery
)

Game.objects.annotate(t1c=Subquery(t1c_count_query))

And you can do the same for t2c and use them in the same query and everything works.

This is made easier by the django-sql-utils app. After you pip install django-sql-utils

from sql_util.utils import SubqueryCount

Game.objects.annotate(
    t1c=SubqueryCount('team1__players'),
    t2c=SubqueryCount('team2__players')
).values('t1c', 't2c')
user_39838040
Related