Django聚合计数 [英] Django Aggregation Count

问题描述

我正在尝试使用聚合函数过滤模型。

I'm trying to filter my model with an aggregate function.

我有一个模型A和一个模型B，并且模型A上带有外键。

I have a Model A and a Model B with a foreign key on the model A.

annotate_pool = queryset.annotate(nb_bets=Count('bets')).all()
for obj in annotate_pool:
    bets_obj = obj.bets.all()
    bets_length = len(bets_obj)
    print(obj.nb_bets, bets_length)

并且注释给我的结果与函数长度不同。

And the annotation doesn't give me the same result as the function length.

1 1
1 2
1 2
1 2
1 2
1 1
1 1
2 2

这是我的模型：

class Pronostic(models.Model):
    cote_total = models.FloatField(default=0.0)
    trust = models.IntegerField()
    mise_ratio = models.IntegerField(default=10)
    safe = models.BooleanField(default=False)

class Bet(models.Model):
    name = models.CharField(max_length=255)
    match = models.ForeignKey('pronostics.Match', on_delete=models.CASCADE, related_name='bets')
    cote = models.FloatField()
    status = models.IntegerField(choices=STATUS, default=0)
    pronostic = models.ForeignKey('pronostics.Pronostic', related_name='bets', on_delete=models.CASCADE)

len（bets_obj）应该给我与 Count（'bets'）相同的结果。
这是怎么回事？为什么 Count 给我一个错误的结果？

len(bets_obj) should give me the same result as Count('bets'). What is going on? Why does Count give me a wrong result?

谢谢您。

编辑：

我正在使用django-rest-framework，然后尝试添加自定义过滤器。 （在此处查看文档）。
预期结果是：
obj.nb_bets 应该等于 bets_length 。由于我想这样过滤我的模型：

I'm using django-rest-framework, and try to add a custom filter. (see doc here). The result expected is: obj.nb_bets should be equal to bets_length. Since I want to filter my models like this:

queryset.annotate(nb_bets=Count('bets')).filter(nb_bets__gte=2)

或

queryset.annotate(nb_bets=Count('bets')).filter(nb_bets__lte=2)

这是我的查询集中包含的SQL查询：

Here is the SQL query contained in my queryset:

SELECT "pronostics_pronostic"."id",
       "pronostics_pronostic"."cote_total",
       "pronostics_pronostic"."trust",
       "pronostics_pronostic"."mise_ratio",
       "pronostics_pronostic"."safe"
FROM "pronostics_pronostic"
LEFT OUTER JOIN "pronostics_bet" 
       ON ("pronostics_pronostic"."id" = "pronostics_bet"."pronostic_id")
LEFT OUTER JOIN "pronostics_match" 
       ON ("pronostics_bet"."match_id" = "pronostics_match"."id")
WHERE ("pronostics_pronostic"."visible" = TRUE
       AND "pronostics_pronostic"."safe" = TRUE)
ORDER BY "pronostics_match"."date" DESC

如果您需要更多信息，请告诉我。

If you need more information, let me know.

推荐答案

根据发布的SQL，您的 Pronostic 对象由字段进行排序。日期匹配对象中的日期。如果删除订单（使用空的 order_by（）），我希望数字匹配。测试以下内容：

Based on the SQL you posted, your Pronostic objects are ordered by the field date in a related Match object. If you remove the ordering (using an empty order_by()), I would expect the numbers to match up. Test this:

annotate_pool = queryset.order_by().annotate(nb_bets=Count('bets')).all()

这篇关于Django聚合计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！