Les annotations Django Count et Sum interfèrent les unes avec les autres

Ce n'est pas le problème avec Django ORM, c'est juste la façon dont les bases de données relationnelles fonctionnent. Lorsque vous créez des ensembles de requêtes simples comme

SELECT "sandbox_player"."id", "sandbox_player"."name", 
(
    SELECT COUNT(U2."id") AS "weapon_count"
    FROM "sandbox_player" U0 
    LEFT OUTER JOIN "sandbox_unit" U1
        ON (U0."id" = U1."player_id")
    LEFT OUTER JOIN "sandbox_weapon" U2 
        ON (U1."id" = U2."unit_id")
    WHERE U0."id" = ("sandbox_player"."id") 
    GROUP BY U0."id", U0."name"
) AS "weapon_count", 
(
    SELECT SUM(U1."rarity") AS "rarity_sum"
    FROM "sandbox_player" U0
    LEFT OUTER JOIN "sandbox_unit" U1
        ON (U0."id" = U1."player_id")
    WHERE U0."id" = ("sandbox_player"."id")
GROUP BY U0."id", U0."name") AS "rarity_sum"
FROM "sandbox_player"

ou

>>> from django.db.models import Count, IntegerField, OuterRef, Subquery, Sum
>>> weapon_count = Player.objects.annotate(weapon_count=Count('unit_set__weapon_set')).filter(pk=OuterRef('pk'))
>>> rarity_sum = Player.objects.annotate(rarity_sum=Sum('unit_set__rarity')).filter(pk=OuterRef('pk'))
>>> qs = Player.objects.annotate(
...     weapon_count=Subquery(weapon_count.values('weapon_count'), output_field=IntegerField()),
...     rarity_sum=Subquery(rarity_sum.values('rarity_sum'), output_field=IntegerField())
... )
>>> qs.values()
<QuerySet [{'id': 1, 'name': 'player_1', 'weapon_count': 2, 'rarity_sum': 10}]>

ORM fait exactement ce que vous attendez de lui: rejoignez Player with Weapon

sqlite> SELECT "sandbox_player"."id",
   ...>        "sandbox_player"."name",
   ...>        "sandbox_weapon"."id",
   ...>        "sandbox_unit"."rarity"
   ...> FROM "sandbox_player"
   ...>          LEFT OUTER JOIN "sandbox_unit" ON ("sandbox_player"."id" = "sandbox_unit"."player_id")
   ...>          LEFT OUTER JOIN "sandbox_weapon" ON ("sandbox_unit"."id" = "sandbox_weapon"."unit_id");
id          name        id          rarity    
----------  ----------  ----------  ----------
1           player_1    1           10        
1           player_1    2           10   

ou Player avec Unit

>>> from sandbox.models import Player
>>> from django.db.models import Count, Sum

>>> Player.objects.annotate(weapon_count=Count('unit_set__weapon_set')).values()
<QuerySet [{'id': 1, 'name': 'player_1', 'weapon_count': 2}]>

>>> Player.objects.annotate(rarity_sum=Sum('unit_set__rarity')).values()
<QuerySet [{'id': 1, 'name': 'player_1', 'rarity_sum': 10}]>


>>> Player.objects.annotate(
...     weapon_count=Count('unit_set__weapon_set', distinct=True),
...     rarity_sum=Sum('unit_set__rarity')).values()
<QuerySet [{'id': 1, 'name': 'player_1', 'weapon_count': 2, 'rarity_sum': 20}]>

et effectuez une agrégation COUNT ou SUM sur eux.

Notez que bien que la première requête ait deux jointures entre trois tables, la table intermédiaire Unit n'est ni dans les colonnes référencées dans SELECT , ni dans la clause GROUP BY . Le seul rôle joué par l' Unit ici est de rejoindre le Player avec une Weapon .

Maintenant, si vous regardez votre troisième jeu de requêtes, les choses se compliquent. Encore une fois, comme dans la première requête, les jointures sont entre trois tables, mais maintenant Unit est référencée dans SELECT car il existe une agrégation SUM pour Unit.rarity :

insert into sandbox_player values (1, "player_1");

insert into sandbox_unit values(1, 10, 1);

insert into sandbox_weapon values (1, 1), (2, 1);

Et c'est la différence cruciale entre la deuxième et la troisième requêtes. Dans la deuxième requête, vous joignez le Player à l' Unit , donc une seule Unit sera répertoriée une fois pour chaque joueur auquel elle fait référence.

Mais dans la troisième requête, vous joignez le Player à l' Unit , puis l' Unit à l' Weapon , donc non seulement une seule Unit sera répertoriée une fois pour chaque joueur auquel elle fait référence, mais également pour chaque arme qui fait référence à l' Unit .

Jetons un coup d'œil à l'exemple simple:

SELECT "sandbox_player"."id",
       "sandbox_player"."name",
       COUNT(DISTINCT "sandbox_weapon"."id") AS "weapon_count",
       SUM("sandbox_unit"."rarity")          AS "rarity_sum"
FROM "sandbox_player"
         LEFT OUTER JOIN "sandbox_unit" ON ("sandbox_player"."id" = "sandbox_unit"."player_id")
         LEFT OUTER JOIN "sandbox_weapon" ON ("sandbox_unit"."id" = "sandbox_weapon"."unit_id")
GROUP BY "sandbox_player"."id", "sandbox_player"."name"

Un joueur, une unité et deux armes qui font référence à la même unité.

Confirmez que le problème existe:

SELECT "sandbox_player"."id", "sandbox_player"."name", SUM("sandbox_unit"."rarity") AS "rarity_sum"
FROM "sandbox_player"
LEFT OUTER JOIN "sandbox_unit" ON ("sandbox_player"."id" = "sandbox_unit"."player_id")
GROUP BY "sandbox_player"."id", "sandbox_player"."name"

À partir de cet exemple, il est facile de voir que le problème est que dans la requête combinée, l'unité sera répertoriée deux fois, une fois pour chacune des armes qui la référencent:

SELECT "sandbox_player"."id", "sandbox_player"."name", COUNT("sandbox_weapon"."id") AS "weapon_count"
FROM "sandbox_player"
LEFT OUTER JOIN "sandbox_unit" 
    ON ("sandbox_player"."id" = "sandbox_unit"."player_id")
LEFT OUTER JOIN "sandbox_weapon" 
    ON ("sandbox_unit"."id" = "sandbox_weapon"."unit_id")
GROUP BY "sandbox_player"."id", "sandbox_player"."name"

Que devrais tu faire?

Comme @ivissani l'a mentionné, l'une des solutions les plus simples serait d'écrire des sous-requêtes pour chacune des agrégations:

Player.objects.annotate(rarity_sum=Sum('unit_set__rarity'))

qui produit le SQL suivant

Player.objects.annotate(weapon_count=Count('unit_set__weapon_set'))

Quelques notes pour compléter l'excellente réponse de rktavi:

1) Ce problème est apparemment considéré comme un bogue depuis 10 ans déjà. Il est même mentionné dans la documentation officielle .

2) Lors de la conversion des QuerySets de mon projet actuel en sous-requêtes (selon la réponse de rktavi), j'ai remarqué que combiner des annotations Subquery (pour les comptes distinct=True qui fonctionnaient toujours correctement) avec une sous- Subquery (pour les sommes) donne un traitement extrêmement long ( 35 sec contre 100 ms) et des résultats incorrects pour la somme. Cela est vrai dans ma configuration actuelle (11 comptes filtrés sur diverses relations imbriquées et 1 somme filtrée sur une relation multi-imbriquée, SQLite3) mais ne peut pas être reproduit avec les modèles simples ci-dessus. Ce problème peut être délicat car une autre partie de votre code pourrait ajouter une annotation à votre QuerySet (par exemple une fonction Table.order_FOO() ), conduisant au problème.

3) Avec la même configuration, j'ai des preuves anecdotiques que les QuerySets de type sous-requête sont plus rapides que les QuerySets d'annotation à nu (dans les cas où vous n'avez que distinct=True comptes distinct=True , bien sûr). J'ai pu observer cela à la fois avec SQLite3 local (83 ms contre 260 ms) et PostgreSQL hébergé (320 ms contre 540 ms).

En raison de ce qui précède, j'éviterai complètement d'utiliser des annotations simples en faveur des sous-requêtes.

Sur la base de l'excellente réponse de @rktavi, j'ai créé deux classes helpers qui simplifient les Subquery / Count et Subquery / Sum :

from django.db.models import OuterRef

weapons = Weapon.objects.filter(unit__player_id=OuterRef('id'))
units = Unit.objects.filter(player_id=OuterRef('id'))

qs = Player.objects.annotate(weapon_count=SubqueryCount(weapons),
                             rarity_sum=SubquerySum(units, 'rarity'))

On peut utiliser ces aides comme ceci:

class SubqueryCount(Subquery):
    template = "(SELECT count(*) FROM (%(subquery)s) _count)"
    output_field = PositiveIntegerField()


class SubquerySum(Subquery):
    template = '(SELECT sum(_sum."%(column)s") FROM (%(subquery)s) _sum)'

    def __init__(self, queryset, column, output_field=None, **extra):
        if output_field is None:
            output_field = queryset.model._meta.get_field(column)
        super().__init__(queryset, output_field, column=column, **extra)