Te sugerimos que revises esta respuesta en un entorno controlado antes de pasarlo a producción, un saludo.
Solución:
Este no es el problema con Django ORM, esta es solo la forma en que funcionan las bases de datos relacionales. Cuando está construyendo conjuntos de consultas simples como
Player.objects.annotate(weapon_count=Count('unit_set__weapon_set'))
o
Player.objects.annotate(rarity_sum=Sum('unit_set__rarity'))
ORM hace exactamente lo que usted espera que haga: únase Player con Weapon
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"
o Player con Unit
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"
y realizar ya sea COUNT o SUM agregación sobre ellos.
Tenga en cuenta que aunque la primera consulta tiene dos combinaciones entre tres tablas, la tabla intermedia Unit no está en las columnas a las que se hace referencia en SELECT, ni en el GROUP BY cláusula. El único papel que Unit juega aquí es para unirse Player con Weapon.
Ahora, si observa su tercer conjunto de consultas, las cosas se complican más. Nuevamente, como en la primera consulta, las combinaciones están entre tres tablas, pero ahora Unit se hace referencia en SELECT como hay SUM agregación para Unit.rarity:
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"
Y esta es la diferencia crucial entre la segunda y la tercera consulta. En la segunda consulta, te unes Player para Unit, entonces un solo Unit aparecerá una vez por cada jugador al que haga referencia.
Pero en la tercera consulta te unes Player para Unit y luego Unit para Weapon, entonces no solo una Unit aparecerá una vez por cada jugador al que haga referencia, pero también para cada arma que hace referencia Unit.
Echemos un vistazo al ejemplo simple:
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);
Un jugador, una unidad y dos armas que hacen referencia a la misma unidad.
Confirme que el problema existe:
>>> from sandbox.models import Player
>>> from django.db.models import Count, Sum
>>> Player.objects.annotate(weapon_count=Count('unit_set__weapon_set')).values()
>>> Player.objects.annotate(rarity_sum=Sum('unit_set__rarity')).values()
>>> Player.objects.annotate(
... weapon_count=Count('unit_set__weapon_set', distinct=True),
... rarity_sum=Sum('unit_set__rarity')).values()
A partir de este ejemplo, es fácil ver que el problema es que en la consulta combinada, la unidad aparecerá en la lista dos veces, una vez para cada una de las armas que hacen referencia a ella:
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
¿Qué deberías hacer?
Como mencionó @ivissani, una de las soluciones más fáciles sería escribir subconsultas para cada una de las agregaciones:
>>> 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()
que produce el siguiente SQL
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"
Algunas notas para complementar la excelente respuesta de rktavi:
1) Este problema aparentemente se ha considerado un error durante 10 años. Incluso se menciona en la documentación oficial.
2) Al convertir los QuerySets de mi proyecto real en subconsultas (según la respuesta de rktavi), noté que la combinación de anotaciones básicas (para el distinct=True cuenta que siempre funcionó correctamente) con un Subquery (para las sumas) produce un procesamiento extremadamente largo (35 segundos frente a 100 ms) y resultados incorrectos para la suma. Este es true en mi configuración real (11 cuentas filtradas en varias relaciones anidadas y 1 suma filtrada en una relación anidada múltiple, SQLite3) pero no se pueden reproducir con los modelos simples anteriores. Este problema puede ser complicado porque otra parte de su código podría agregar una anotación a su QuerySet (por ejemplo, un Table.order_FOO() función), lo que lleva al problema.
3) Con la misma configuración, tengo evidencia anecdótica de que los QuerySets de tipo subconsulta son más rápidos en comparación con los QuerySets de anotaciones básicas (en los casos en los que solo tiene distinct=True cuenta, por supuesto). Pude observar esto tanto con SQLite3 local (83 ms frente a 260 ms) como con PostgreSQL alojado (320 ms frente a 540 ms).
Como resultado de lo anterior, evitaré por completo el uso de anotaciones básicas en favor de las subconsultas.