Django Queryset - Get counts by model.choices-CodePudding

I have below model, which has all the details about customer Leads. Customer wants to see his Leads followup statuses.

class FollowUp(models.Model):
    CALL_CHOICES = [("0", "Call Unanswered"),
                  ("1", "Call Later"),
                  ("2", "Visit Scheduled"),
                  ("3", "Not Visited"),
                  ("4", "Not reachable"),
                  ("5", "Wrong Number"),
                  ("6", "Not Interested"),
                  ("7", "Deal Closed")]
    status = models.CharField(_("Call Status"),
            max_length = 20,
            choices = CALL_CHOICES,
            default = '1'
        )
    next_action_on = DateTimeField(_("Next Action"), auto_now_add=False, 
                null=True, blank=True)
    reminder = models.BooleanField(_("reminder"), default=False)  
    created = models.DateTimeField(auto_now_add=True)
    modified = models.DateTimeField(auto_now=True)

class Lead(models.Model):
    followups = models.ManyToManyField(FollowUp, verbose_name=_("Follow up"), blank=True)
    ...

How can I get Lead counts of each status choice. such as

{
 'Call Unanswered': 12 #Leads, 
 'Call Later': 10 # Leads  
 'Visit Scheduled': 20, #Leads, 
 ...
}

CodePudding user response：

Try this:

Lead.objects.aggregate(
    **{
        choice[1]: Count(
            'followups', filter=Q(followups__status=choice[0])
        ) for choice in Followup.CALL_CHOICES
    }
)

This would return something like:

{'Call Unanswered': 0,
 'Call Later': 0,
 'Visit Scheduled': 0,
 'Not Visited': 0,
 'Not reachable': 0,
 'Wrong Number': 0,
 'Not Interested': 0,
 'Deal Closed': 0}

CodePudding user response：

You would get the count like this:

>>> Leads.objects.filter(follow_up__status=0).count()
>>> 10

CodePudding user response：

I think you can use the for iterator to iterate through CALL_CHOICES and filter the rows based on the status.

queryset = Leads.objects.all()
res = {}
for i in FollowUp.CALL_CHOICES:
    res[i[1]] = queryset.filter(status=i[0]).aggregate(follows__total=Count('status'))['total']
print(res)

At the end of the script you will get each pair of values.