Django的注释和值（）：在“按组”的额外字段会导致意想不到的结果 [英] Django annotate and values(): extra field in 'group by' causes unexpected results

问题描述

我必须失去了一些东西显而易见的，因为并不如预期的行为是为这个简单的要求。这里是我的模型类：

 类相逢（models.Model）：
    ACTIVITY_TYPE = models.CharField（MAX_LENGTH = 2，
                           选择=（（IP，IP），（运算，运算），（AE，ae的）））
    成本= models.DecimalField（max_digits = 8，decimal_places = 2）
 

我要找到每个活动类型的总成本。我的查询是：

 ＆GT;＆GT;＆GT; Encounter.objects.values​​（'ACTIVITY_TYPE'）。注释（总和（'成本'））
 

其中产量：

 ＆GT;＆GT;＆GT; [{cost__sum'：十进制（140.00），ACTIVITY_TYPE'：u'ip}，
     {'cost__sum'：十进制（100.00），ACTIVITY_TYPE'：u'op}，
     {'cost__sum'：十进制（0.00），ACTIVITY_TYPE'：u'ip'}]
 

在结果集有2个IP类型的遭遇。这是因为它不是由唯一的分组 ACTIVITY_TYPE 但 ACTIVITY_TYPE和成本这不会给预期的结果。此生成的SQL查询是：

 选择encounter_encounter。ACTIVITY_TYPE
    SUM（encounter_encounter。TOTAL_COST）作为total_cost__sum
    从encounter_encounter
    GROUP BYencounter_encounter。ACTIVITY_TYPE
             encounter_encounterTOTAL_COST＆LT;＆LT;＆LT;＆LT;本食堂THINGS
    ORDER BYencounter_encounter。TOTAL_COSTDESC
 

我怎样才能让这个查询工作，如预期（以及由的文档如果我没有得到它错了），使其只能通过 ACTIVITY_TYPE ？

解决方案

由于@Skirmantas正确地指出，这个问题是有关 ORDER_BY 。虽然没有明确地在查询指出，在模型的元类的默认排序被添加到查询，然后由子句添加到组，因为SQL要求如此。

解决方法是，删除默认排序或添加一个空 ORDER_BY（）来重新排序：

 ＆GT;＆GT;＆GT; Encounter.objects.values​​（'ACTIVITY_TYPE'）。注释（总和（'成本'））。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的注释和值（）：在“按组”的额外字段会导致意想不到的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！