Django - How to get count for select fields in model within same dictionary

user_12815134 0
Viewed 276

I am currently working on producing data analytics, and I am struggling with grouping the data in the format that is required.

This is my models.py

class CustomerInformation(models.Model):

    status = (
        ('lead', 'Lead'),
        ('client', 'Client'),
    )

    customer_name = models.CharField(max_length=100)
    status = models.CharField(max_length=100, choices=status, default='lead')
    creator = models.ForeignKey('UserProfile', related_name='customers', on_delete=models.CASCADE, null=True, blank=True)
    created_date = models.DateField(default=timezone.now)

Assuming there are 5 instances of this model, namely:

1) customer_name: '-', status: 'lead', creator: 'User 1', created_date: '23 May 20'
2) customer_name: '-', status: 'lead', creator: 'User 1', created_date: '21 May 20'
3) customer_name: '-', status: 'lead', creator: 'User 2', created_date: '08 May 20'
4) customer_name: '-', status: 'client', creator: 'User 2', created_date: '16 May 20'
5) customer_name: '-', status: 'client', creator: 'User 2', created_date: '04 April 20'

I am trying to get the data to be in this format:

{ April: [{ creator: 'User 2', lead_count: 0, client_count: 1}],
  May: [{ creator: 'User 1', lead_count: 2, client_count: 0},
         { creator: 'User 2', lead_count: 1, client_count: 1}]
}

Basically, I want to be able to count the number of times a value (lead or client) is selected from the status field, per user, per month. I tried using annotation, but it returned the counts in separate dictionaries (1 dictionary for the count of leads and another for the count of clients), but I would want them to be in the above format if possible. I thought of using key assignment to assign the count of the second dictionary to the first dictionary, but it seemed too messy to me, so I was hoping that Django has the functionality to format it in the above format using queries and annotations. Ultimately I will be organising the data and would be sending it through a serializer to be sent out through the Django Rest Framework. All help is welcome, and thank you in advance, do advise me on what to do as I am new to Django!

django-rest-framework django python
1 Answers

for grouping count you can use this : 

CustomerInformation.objects.annotate(lead_count=Count('status',filter=Q(status="lead"))).annotate(client_count=Count('status',filter=Q(status="client")))
user_118588590
Related