gpt4 book ai didi

Django 注释不适用于 order_by

转载 作者:行者123 更新时间:2023-12-04 19:55:25 24 4
gpt4 key购买 nike

我想获取 MyModel order_by('-end_date') 的最后 100 条记录,并对不同的获胜者类型进行 SUM 注释

MyModel.objects.all()[:100].order_by('-end_game_time').values('winner').annotate(total=Count('winner'))

结果查询如下,我没有预期的组
<QuerySet [{'winner': 3, 'total': 1}, {'winner': 15, 'total': 1}, 'total': 1}, {'winner': 3, 'total': 1}, {'winner': 5, 'total': 1}, {'winner': 15, 'total': 1}, {'winner': 5, 'total': 1}, {'winner': 3, 'total': 1}, '...(remaining elements truncated)...']>

生成的查询就像
SELECT "game_mymodel"."winner", COUNT("game_mymodel"."winner") AS "total" FROM "game_mymodel" GROUP BY "game_mymodel"."winner", "game_mymodel"."end_game_time" ORDER BY "game_mymodel"."end_game_time" DESC LIMIT 100

但是当我没有 order_by 时,结果正如我所料
MyModel.objects.all()[:100].values('winner').annotate(total=Count('winner'))
Out[52]: <QuerySet [{'winner': 5, 'total': 43}, {'winner': 1, 'total': 2}, {'winner': 15, 'total': 51}, {'winner': 2, 'total': 42}, {'winner': 3, 'total': 43}]>

和生成的查询 group_by 部分不同
SELECT "game_mymodel"."winner", COUNT("game_mymodel"."winner") AS "total" FROM "game_mymodel" GROUP BY "game_mymodel"."winner" LIMIT 100

最佳答案

据我所知,不可能在单个查询中实现您想要做的事情,您在 SQL 中想要的是:

SELECT "game_mymodel"."winner", COUNT("game_mymodel"."winner") AS "total" FROM "game_mymodel" GROUP BY "game_mymodel"."winner" ORDER BY "game_mymodel"."end_game_time" DESC LIMIT 100

这不是有效的 sql 查询,因此您需要有一个子查询来选择 100 个元素,然后对它们应用聚合。

首先构建子查询:
top_100_games = MyModel.objects.order_by('-end_game_time')[:100].only('id').all()

然后在主查询中使用它:
MyModel.objects.filter(id__in=top_100_games).values('winner').annotate(total=Count('winner'))

关于Django 注释不适用于 order_by,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/48535610/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com