Django annotation does not group output as intended-CodePudding

The following is my model:

class Project(models.Model):
     name = models.CharField(max_length=1000, null=True, blank=True)
     date_created = models.DateTimeField(auto_now=True)
     status = models.CharField(max_length=1000, null=True, blank=True)

The status field has about 5 different options (Won, Lost, Open, Pending, Cancelled).

I need to know how to get the number of projects with x status in each month in a given time range query.

I was able to get the correct data shape using the following annotation- but for some reason, the output given delivers an object for every found status within a month. For instance, if there is at least 1 "Won" and 1 "Open" project found in the same month, two separate objects will return for the same month. Additionally, the status options here are hard-coded and would need to be modified if a new status was added.

queryset = list(opps.annotate(
    month=TruncMonth('date_created'),
).values('month').annotate(
    total=Count('id'),
    Win=Count('id', filter=Q(status='Win')),
    Loss=Count('id', filter=Q(status='Loss')),
    Open=Count('id', filter=Q(status='Open')),
    Dormant=Count('id', filter=Q(status='Dormant')),
    Pending=Count('id', filter=Q(status='Pending')),
    Cancelled=Count('id', filter=Q(status='Cancelled')),
))

Here's a sample of my current output.

[{'month': datetime.datetime(2022, 5, 1, 0, 0, tzinfo=zoneinfo.ZoneInfo(key='UTC')), 'total': 1, 'Win': 0, 'Loss': 1, 'Open': 0, 'Dormant': 0, 'Pending': 0, 'Cancelled': 0}
{'month': datetime.datetime(2022, 5, 1, 0, 0, tzinfo=zoneinfo.ZoneInfo(key='UTC')), 'total': 1, 'Win': 0, 'Loss': 1, 'Open': 0, 'Dormant': 0, 'Pending': 0, 'Cancelled': 0}
{'month': datetime.datetime(2022, 5, 1, 0, 0, tzinfo=zoneinfo.ZoneInfo(key='UTC')), 'total': 1, 'Win': 0, 'Loss': 0, 'Open': 1, 'Dormant': 0, 'Pending': 0, 'Cancelled': 0}
{'month': datetime.datetime(2022, 6, 1, 0, 0, tzinfo=zoneinfo.ZoneInfo(key='UTC')), 'total': 1, 'Win': 0, 'Loss': 0, 'Open': 1, 'Dormant': 0, 'Pending': 0, 'Cancelled': 0}]

CodePudding user response：

If your Model does not define an ordering via Meta class, you have to specify the ordering before your second annotate call.

queryset = list(opps
                .annotate(month=TruncMonth('date_created'))
                .order_by('month')  # <== this is necessary for the correct results
                .values('month')
                .annotate(total=Count('id'),
                          Win=Count('id', filter=Q(status='Win')),
                          Loss=Count('id', filter=Q(status='Loss')),
                          Open=Count('id', filter=Q(status='Open')),
                          Dormant=Count('id', filter=Q(status='Dormant')),
                          Pending=Count('id', filter=Q(status='Pending')),
                          Cancelled=Count('id', filter=Q(status='Cancelled'))))