Django 注释和值():'group by' 中的额外字段会导致意外结果 [英] Django annotate and values(): extra field in 'group by' causes unexpected results

问题描述

我一定遗漏了一些明显的东西，因为对于这个简单的要求，行为并不像预期的那样.这是我的模型类:

class Encounter(models.Model):activity_type = models.CharField(max_length=2,选择=(('ip'，'ip')，('op'，'op')，('ae'，'ae')))成本=models.DecimalField(max_digits=8,decimal_places=2)

我想找出每种活动类型的总成本.我的查询是:

<预><代码>>>>Encounter.objects.values('activity_type').annotate(Sum('cost'))

产生的结果:

<预><代码>>>>[{'cost__sum': Decimal("140.00"), 'activity_type': u'ip'},{'cost__sum': Decimal("100.00"), 'activity_type': u'op'},{'cost__sum': Decimal("0.00"), 'activity_type': u'ip'}]

在结果集中有 2 个 'ip' 类型的遭遇.这是因为它不是仅按 activity_type 分组，而是按 activity_type AND cost 分组，这并没有给出预期的结果.为此生成的 SQL 查询是:

SELECT "encounter_encounter"."activity_type",SUM("encounter_encounter"."total_cost") AS "total_cost__sum"来自遭遇_遭遇"GROUP BY "encounter_encounter"."activity_type","encounter_encounter"."total_cost" <<<<这把事情搞砸了ORDER BY "encounter_encounter"."total_cost" DESC

我怎样才能使这个查询按预期工作(以及 docs(如果我没有弄错)并让它只在 activity_type 上进行分组?

解决方案

正如@Skirmantas 正确指出的那样，问题与 order_by 有关.尽管在查询中没有明确说明，但模型的 Meta 类中的默认排序会添加到查询中，然后将其添加到 group by 子句中，因为 SQL 需要这样做.

解决方案要么移除默认排序，要么添加一个空的order_by()来重置排序:

<预><代码>>>>Encounter.objects.values('activity_type').annotate(Sum('cost')).order_by()

I must be missing something obvious, as the behavior is not as expected for this simple requirement. Here is my model class:

class Encounter(models.Model):
    activity_type = models.CharField(max_length=2, 
                           choices=(('ip','ip'), ('op','op'), ('ae', 'ae')))
    cost = models.DecimalField(max_digits=8, decimal_places=2)

I want to find the total cost for each activity type. My query is:

>>> Encounter.objects.values('activity_type').annotate(Sum('cost'))

Which yields:

>>> [{'cost__sum': Decimal("140.00"), 'activity_type': u'ip'}, 
     {'cost__sum': Decimal("100.00"), 'activity_type': u'op'}, 
     {'cost__sum': Decimal("0.00"), 'activity_type': u'ip'}]

In the result set there are 2 'ip' type encounters. This is because it is not grouped by only activity_type but by activity_type AND cost which does not give the intended result. The generated SQL query for this is:

SELECT "encounter_encounter"."activity_type", 
    SUM("encounter_encounter"."total_cost") AS "total_cost__sum" 
    FROM "encounter_encounter" 
    GROUP BY "encounter_encounter"."activity_type", 
             "encounter_encounter"."total_cost"        <<<< THIS MESSES THINGS
    ORDER BY "encounter_encounter"."total_cost" DESC

How can I make this query work as expected (and as implied by the docs if I am not getting it wrong) and make it only do a group by on activity_type?

解决方案

As @Skirmantas correctly pointed, the problem was related to order_by. Although it is not explicitly stated in the query, the default ordering in the model's Meta class is added to the query, which is then added to the group by clause because SQL requires so.

The solution is either to remove the default ordering or add an empty order_by() to reset ordering:

>>> Encounter.objects.values('activity_type').annotate(Sum('cost')).order_by()

这篇关于Django 注释和值():'group by' 中的额外字段会导致意外结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！