Django: Get count of specific values by month in queryset

Viewed 51

My model is like so:

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 sum total of projects each month with the following annotation:

Project.objects.all().annotate(month=TruncMonth('date_created')).values(
        'month').annotate(total=Count('pk'), ).order_by('date_created')

However, I am looking for an output like this:

    [{date: 'January 2020', total: 30, won: 10, lost:10, open: 10, pending: 0, cancelled: 0}, 
{date: 'February 2020', total: 30, won: 10, lost:10, open: 10, pending: 0, cancelled: 0}, 
{date: 'March 2020', total: 30, won: 10, lost:10, open: 10, pending: 0, cancelled: 0}]

Ideally I would like the keys within the final output to be dynamic (i.e. if a new status is added, we won't need to hardcode a new key into the query) but I don't know if this is possible or not.

UPDATE: The following works to provide the correct data shape but is not a complete solution:

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')),
    ))

The problem with this annotation is that it returns 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. Here's a sample of my 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}]
0 Answers
Related