这不是Django ORM的问题，这只是关系数据库的工作方式。当您构建简单的查询集时，例如
Player.objects.annotate(weapon_count=Count('unit_set__weapon_set'))
要么
Player.objects.annotate(rarity_sum=Sum('unit_set__rarity'))
ORM不正是你希望它做什么-加入Player与Weapon
SELECT "sand@R_418_2419@_player"."id", "sand@R_418_2419@_player"."name", COUNT("sand@R_418_2419@_weapon"."id") AS "weapon_count"
FROM "sand@R_418_2419@_player"
LEFT OUTER JOIN "sand@R_418_2419@_unit"
ON ("sand@R_418_2419@_player"."id" = "sand@R_418_2419@_unit"."player_id")
LEFT OUTER JOIN "sand@R_418_2419@_weapon"
ON ("sand@R_418_2419@_unit"."id" = "sand@R_418_2419@_weapon"."unit_id")
GROUP BY "sand@R_418_2419@_player"."id", "sand@R_418_2419@_player"."name"
或Player搭配Unit
SELECT "sand@R_418_2419@_player"."id", "sand@R_418_2419@_player"."name", SUM("sand@R_418_2419@_unit"."rarity") AS "rarity_sum"
FROM "sand@R_418_2419@_player"
LEFT OUTER JOIN "sand@R_418_2419@_unit" ON ("sand@R_418_2419@_player"."id" = "sand@R_418_2419@_unit"."player_id")
GROUP BY "sand@R_418_2419@_player"."id", "sand@R_418_2419@_player"."name"
并对其执行COUNT或SUM聚合。
请注意，尽管第一个查询在三个表之间具有两个联接，但是中间表Unit既不在引用的列中SELECT也不在GROUP BY子句中。那唯一的作用Unit在这里踢球是加入Player同Weapon。
现在，如果您查看第三个查询集，事情将变得更加复杂。再次，如在第一个查询中一样，联接位于三个表之间，但现在由于存在以下汇总而Unit被引用：SELECT``SUM``Unit.rarity
SELECT "sand@R_418_2419@_player"."id",
"sand@R_418_2419@_player"."name",
COUNT(DISTINCT "sand@R_418_2419@_weapon"."id") AS "weapon_count",
SUM("sand@R_418_2419@_unit"."rarity") AS "rarity_sum"
FROM "sand@R_418_2419@_player"
LEFT OUTER JOIN "sand@R_418_2419@_unit" ON ("sand@R_418_2419@_player"."id" = "sand@R_418_2419@_unit"."player_id")
LEFT OUTER JOIN "sand@R_418_2419@_weapon" ON ("sand@R_418_2419@_unit"."id" = "sand@R_418_2419@_weapon"."unit_id")
GROUP BY "sand@R_418_2419@_player"."id", "sand@R_418_2419@_player"."name"
这是第二和第三查询之间的关键区别。在第二个查询，要加入Player到Unit，所以单Unit将再次为每个玩家，它引用被列出。
但在第三个查询要加入Player到Unit再Unit至Weapon，所以不能只有一个Unit会被列出一次为每个玩家，它的参考，。
让我们看一个简单的例子：
insert into sand@R_418_2419@_player values (1, "player_1");
insert into sand@R_418_2419@_unit values(1, 10, 1);
insert into sand@R_418_2419@_weapon values (1, 1), (2, 1);
确认问题存在：
>>> from sand@R_418_2419@.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}]>
从该示例可以很容易地看出问题是在组合查询中该单元将被列出两次，而引用该单元的每种武器都将被列出一次：
sqlite> SELECT "sand@R_418_2419@_player"."id",
...> "sand@R_418_2419@_player"."name",
...> "sand@R_418_2419@_weapon"."id",
...> "sand@R_418_2419@_unit"."rarity"
...> FROM "sand@R_418_2419@_player"
...> LEFT OUTER JOIN "sand@R_418_2419@_unit" ON ("sand@R_418_2419@_player"."id" = "sand@R_418_2419@_unit"."player_id")
...> LEFT OUTER JOIN "sand@R_418_2419@_weapon" ON ("sand@R_418_2419@_unit"."id" = "sand@R_418_2419@_weapon"."unit_id");
id name id rarity
---------- ---------- ---------- ----------
1 player_1 1 10
1 player_1 2 10
正如@ivissani所提到的，最简单的解决方案之一是为每个聚合编写子查询：
>>> 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}]>
产生以下sql
SELECT "sand@R_418_2419@_player"."id", "sand@R_418_2419@_player"."name",
(
SELECT COUNT(U2."id") AS "weapon_count"
FROM "sand@R_418_2419@_player" U0
LEFT OUTER JOIN "sand@R_418_2419@_unit" U1
ON (U0."id" = U1."player_id")
LEFT OUTER JOIN "sand@R_418_2419@_weapon" U2
ON (U1."id" = U2."unit_id")
WHERE U0."id" = ("sand@R_418_2419@_player"."id")
GROUP BY U0."id", U0."name"
) AS "weapon_count",
(
SELECT SUM(U1."rarity") AS "rarity_sum"
FROM "sand@R_418_2419@_player" U0
LEFT OUTER JOIN "sand@R_418_2419@_unit" U1
ON (U0."id" = U1."player_id")
WHERE U0."id" = ("sand@R_418_2419@_player"."id")
GROUP BY U0."id", U0."name") AS "rarity_sum"
FROM "sand@R_418_2419@_player"
602392714
清零编程群