在Django ORM中,“值”和“注释”;无法按分组 [英] In Django ORM, "values" and "annotate" are not working to group by
问题描述
我有一个这样的表:
型号:
类餐(models.Model):
用户= models.ForeignKey(settings.AUTH_USER_MODEL,默认= 1)
date_of_meal = models.DateField()
morning_meal = models.SmallIntegerField(默认= 0)
mid_day_meal =模型.SmallIntegerField(默认值= 0)
night_meal =模型.SmallIntegerField(默认值= 0)
已更新= models.DateTimeField(auto_now = True,auto_now_add = False)
timestamp = models.DateTimeField(auto_now = False,auto_now_add = True)
class Meta:
ordering = ['-updated','-timestamp']
def __str __():
return str(self.date_of_meal)
代码:
meals_list = Meal.objects.values('date_of_meal')。annotate(mm = Sum('morning_meal'),mdm = Sum('mid_day_meal'),nm = Sum('night_meal'))
输出:
< QuerySet [{'mdm':0, 'mm':1,'date_of_meal':datetime.date(2017,3,
23),'nm':
1},{'mdm':1,'mm':1,' date_of_meal':datetime.date(2017,3,23),'nm':0},{
'mdm':1,'mm':0,'date_of_meal':datetime.date(2017,3, 22),'nm':1},{'mdm':
0,'mm':1,'date_of_meal':datetime.date(2017,3,22),'nm':1},{ 'mdm':1,'m
m':1,'date_of_meal':datetime.date(2017,3,21),'nm':1},{'mdm':1,'mm': 1,
'date_of_meal':datetime.date(2017,3,21),'nm':1},{'mdm':1,'mm':1,'date
_of_meal': datetime.date(2017,3,20),'nm':1},{'mdm':1,'mm':1,'date_of_me
al':datetime.date(2017,3,20) ,'nm':1},{'mdm':1,'mm':0,'date_of_meal':d
atetime.date(2017,3,19),'nm':0},{' mdm':0,'mm':0,'date_of_meal':datetim
e.date(2017,3,19),'nm':1}]>
它多次重复同一日期。
但是我想这样:
< QuerySet [{'mdm':1,'mm':2,'date_of_meal ':datetime.date(2017,3,23),'nm':
1},
{'mdm':1,'mm':1,'date_of_meal':datetime.date(2017 ,3,22),'nm':2},
............
............
等等........
]>
如何获得预期的输出?
(答案隐藏在注释中,因此我将其转换为正确的答案。我花了几个小时才遇到相同的问题。)
关键问题在于Django自动将您排序的字段添加到 values()
中。因此,根据您的情况,您认为您正在执行 .values(’date_of_meal')
。但是由于模型上的 ordering = ['-updated','-timestamp']
,django将其转换为`.values('date_of_meal','updated',而不是'timestamp')。
使用如此意外的 .values()
,您当然不会获得所需的group_by行为。
解决方案是通过添加空的 .order_by()$来重置默认顺序。 c $ c>,或者在您的情况下为
.order_by('date_of_meal')
。
请参见 django文档
I have a table like this:
Now I want to sum up the meals on each date. I have written the code below. But it doesn't work as I wanted.
Model:
class Meal(models.Model):
user = models.ForeignKey(settings.AUTH_USER_MODEL, default=1)
date_of_meal = models.DateField()
morning_meal = models.SmallIntegerField(default=0)
mid_day_meal = models.SmallIntegerField(default=0)
night_meal = models.SmallIntegerField(default=0)
updated = models.DateTimeField(auto_now=True, auto_now_add=False)
timestamp = models.DateTimeField(auto_now=False, auto_now_add=True)
class Meta:
ordering = ['-updated', '-timestamp']
def __str__(self):
return str(self.date_of_meal)
Code:
meals_list = Meal.objects.values('date_of_meal').annotate(mm=Sum('morning_meal'), mdm=Sum('mid_day_meal'), nm=Sum('night_meal'))
Output:
<QuerySet [{'mdm': 0, 'mm': 1, 'date_of_meal': datetime.date(2017, 3,
23), 'nm':
1}, {'mdm': 1, 'mm': 1, 'date_of_meal': datetime.date(2017, 3, 23), 'nm': 0}, {
'mdm': 1, 'mm': 0, 'date_of_meal': datetime.date(2017, 3, 22), 'nm': 1}, {'mdm':
0, 'mm': 1, 'date_of_meal': datetime.date(2017, 3, 22), 'nm': 1}, {'mdm': 1, 'm
m': 1, 'date_of_meal': datetime.date(2017, 3, 21), 'nm': 1}, {'mdm': 1, 'mm': 1,
'date_of_meal': datetime.date(2017, 3, 21), 'nm': 1}, {'mdm': 1, 'mm': 1, 'date
_of_meal': datetime.date(2017, 3, 20), 'nm': 1}, {'mdm': 1, 'mm': 1, 'date_of_me
al': datetime.date(2017, 3, 20), 'nm': 1}, {'mdm': 1, 'mm': 0, 'date_of_meal': d
atetime.date(2017, 3, 19), 'nm': 0}, {'mdm': 0, 'mm': 0, 'date_of_meal': datetim
e.date(2017, 3, 19), 'nm': 1}]>
It repeats same date multiple times.
But I wanted liked this:
<QuerySet [{'mdm': 1, 'mm': 2, 'date_of_meal': datetime.date(2017, 3, 23), 'nm':
1},
{'mdm': 1, 'mm': 1, 'date_of_meal': datetime.date(2017, 3, 22), 'nm': 2},
............
............
so on........
]>
How can I get my expected output ?
(The answer is hidden in the comments, so I've turned it into a proper answer. I spend a few hours having the same problem.)
The key problem is that Django automatically adds the fields you sort on to values()
. So in your case, you think you're doing .values('date_of_meal')
. But because of the ordering = ['-updated', '-timestamp']
on your model, django turns it into `.values('date_of_meal', 'updated', 'timestamp') instead...
With such an unexpected .values()
you of course don't get the group_by behaviour you expect.
The solution is to "reset" the default ordering by either adding an empty .order_by()
or, in your case, .order_by('date_of_meal')
.
这篇关于在Django ORM中,“值”和“注释”;无法按分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!