Django Annotate not working on some cases

Question

I have a Django queryset with annotation that works well doing a count over year/month, but for some reason THE SAME CODE for another model does not work.

Code that works:

usages = Usage.objects.all()
usages = usages.annotate(year=ExtractYear('usage_datetime'),
                            month = ExtractMonth('usage_datetime')).values('year','month').annotate(total=Count('id'))

Code that dont work (only calculates 1 on the count):

events = Event.objects.all()
events = events.annotate(year=ExtractYear('created'),
                            month = ExtractMonth('created')).values('year','month').annotate(total=Count('id'))

The first one outputs:

<QuerySet [{'year': 2020, 'month': 9, 'total': 13}, {'year': 2020, 'month': 8, 'total': 18}, {'year': 2020, 'month': 7, 'total': 29}, {'year': 2020, 'month': 6, 'total': 31},

The second one:

<QuerySet [{'year': 2020, 'month': 8, 'total': 1}, {'year': 2020, 'month': 8, 'total': 1}, {'year': 2020, 'month': 8, 'total': 1}, {'year': 2020, 'month': 8, 'total': 1} 'total': 1}, '...(remaining elements truncated)...']>

The second one is not counting like the first one.... not sure what it may be.... Since the only difference is on the model, I would assume thats the issue, so here are my models:

models.py

class Event(models.Model):
    name = models.CharField(max_length=255)
    created = models.DateTimeField(auto_now_add=True, editable=False)
    last_updated = models.DateTimeField(auto_now=True, editable=False)
    
    class Meta:
        ordering = ('-created',)

class Usage(models.Model):
    name = models.CharField(max_length=255)
    usage_datetime = models.DateTimeField(auto_now_add=True)

EDIT Here is the query the Events are performing

SELECT django_datetime_extract('year', "EventTracker_event"."created", 'UTC') AS "year", 
       django_datetime_extract('month', "EventTracker_event"."created", 'UTC') AS "month", 
       COUNT("EventTracker_event"."id") AS "total" 
  FROM "EventTracker_event" 
 GROUP BY django_datetime_extract('year', "EventTracker_event"."created", 'UTC'), 
       django_datetime_extract('month', "EventTracker_event"."created", 'UTC'), 
       "EventTracker_event"."created" 
 ORDER BY "EventTracker_event"."created" DESC

The created for some reason is making its way on the group by....

You can try a couple of things: 1. Print the SQL queries - https://stackoverflow.com/a/11235636/817277 and check if they match. 2. Check if a model manager has overriden `objects.all()` — Pramod, Aug 07 '20 at 13:32
@Pramod good idea.... so the Events one is actually using the `created` on the group by! (will edit to add it) — Walucas, Aug 07 '20 at 14:10
@Pramod problem was on the ordering! I removed it and it started working — Walucas, Aug 07 '20 at 14:14

score 2 · Accepted Answer · answered Aug 07 '20 at 14:34

2

Removing the ordering from the model solved the problem

answered Aug 07 '20 at 14:34

Walucas

2,549
1
21
44

Django Annotate not working on some cases

EDIT Here is the query the Events are performing

1 Answers1