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')
.
See the django documentation
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With