I’m new to Django framework, may be this problem seem to be fun to some experienced developers here.

So, I have a django model:

STATUS_CHOICES = (
    (‘CL’, ‘CLOSED’),
    (‘FR’, ‘FORWARDED’),
    (‘PE’),
)

class References(modles.Model):
    ref_no = models.CharField(max_length=)
    date_received = models.DateField()
    status = models.CharField(max_length=2, choices=STATUS_CHOICES)

I am required to query the data in this format:-

Sample data:

I created three different querysets but don’t know how to join them to produce above results: –

# TOTAL
q_total = References.objects.all() \
.values('date_received__year', 'date_received__month') \
.annotate(dcount=Count(‘ref_no’)) \
.order_by('date_received__year', ‘date_received__month')

# CLOSED
q_total = References.objects.filter(Q(status__iexact='CL') \
.values('date_received__year', 'date_received__month') \
.annotate(dcount=Count(‘ref_no’)) \
.order_by('date_received__year', ‘date_received__month')

# FORWARDED
q_total = References.objects.filter(Q(status__iexact='FR') \
.values('date_received__year', 'date_received__month') \
.annotate(dcount=Count(‘ref_no’)) \
.order_by('date_received__year', ‘date_received__month')

# PENDING
q_total = References.objects.filter(Q(status__iexact='PE') \
.values('date_received__year', 'date_received__month') \
.annotate(dcount=Count(‘ref_no’)) \
.order_by('date_received__year', ‘date_received__month')

Any help would be appreciated. The above is just a small sample problem.

>Solution :

It is not necessary to JOIN or merge, you can do this all in the same queryset:

from django.db.models import Count, Q

q_total = (
    References.objects.values('date_received__year', 'date_received__month')
    .annotate(
        closed_count=Count('ref_no', filter=Q(status__iexact='CL')),
        forward_count=Count('ref_no', filter=Q(status__iexact='FR')),
        pending_count=Count('ref_no', filter=Q(status__iexact='PE')),
    )
    .order_by('date_received__year', 'date_received__month')
)

for each row it will thus have three attributes: .closed_count, .forward_count, .pending_count.