Write an Effective query for django for a FOR Loop?

Question

I need to get number of policies bought in each month from these model

class Insurance(models.Model):
  #other fields..
  date_purchased_on = models.DateField(auto_now_add=True)    
  customer_region = models.CharField(
    max_length=10, choices=RegionType.choices)

so i have written a views to get a response

@api_view(['GET'])
def chart_data(request):
    # structure of the op should be all the months from 1 to 12
    #  [{"month": 1,"count": 100,}, { "month:2", "count":20}]

    filtered_data = []
    for i in range(1, 13):
        query = Insurance.objects.filter(date_purchased_on__month=i).count()
        data = {
            "month": i,
            "count": query
        }
        filtered_data.append(data)
        
    return Response(filtered_data)

now the problem is this query hits the database every time its like 12 times. is there a way to handle this to get in a single query or to reduce it

and my logger results

(0.000) SELECT COUNT(*) AS "__count" FROM "insurance_insurance" WHERE EXTRACT('month' FROM "insurance_insurance"."date_purchased_on") = 1; args=(1,); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "insurance_insurance" WHERE EXTRACT('month' FROM "insurance_insurance"."date_purchased_on") = 2; args=(2,); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "insurance_insurance" WHERE EXTRACT('month' FROM "insurance_insurance"."date_purchased_on") = 3; args=(3,); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "insurance_insurance" WHERE EXTRACT('month' FROM "insurance_insurance"."date_purchased_on") = 4; args=(4,); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "insurance_insurance" WHERE EXTRACT('month' FROM "insurance_insurance"."date_purchased_on") = 5; args=(5,); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "insurance_insurance" WHERE EXTRACT('month' FROM "insurance_insurance"."date_purchased_on") = 6; args=(6,); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "insurance_insurance" WHERE EXTRACT('month' FROM "insurance_insurance"."date_purchased_on") = 7; args=(7,); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "insurance_insurance" WHERE EXTRACT('month' FROM "insurance_insurance"."date_purchased_on") = 8; args=(8,); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "insurance_insurance" WHERE EXTRACT('month' FROM "insurance_insurance"."date_purchased_on") = 9; args=(9,); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "insurance_insurance" WHERE EXTRACT('month' FROM "insurance_insurance"."date_purchased_on") = 10; args=(10,); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "insurance_insurance" WHERE EXTRACT('month' FROM "insurance_insurance"."date_purchased_on") = 11; args=(11,); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "insurance_insurance" WHERE EXTRACT('month' FROM "insurance_insurance"."date_purchased_on") = 12; args=(12,); alias=default

use aggregation docs.djangoproject.com/en/4.0/topics/db/aggregation — Grzegorz Bokota
– Grzegorz Bokota, Commented Dec 14, 2021 at 17:47
im not sure how to replicate the same using aggregate. if you show me some way to do this it will be great to implement — Gokulaselvan
– Gokulaselvan, Commented Dec 14, 2021 at 17:49

willeM_ Van Onsem · Accepted Answer · 2024-11-24 16:45:20Z

3

You can extract the month and order by that month with an ExtractMonth expression ^[Django-doc]:

from django.db.models import Count
from django.db.models.functions import ExtractMonth

Insurance.objects.values(month=ExtractMonth('date_purchased_on')).annotate(
    count=Count('pk')
).order_by('month')

This will product a QuerySet of dictionaries that look like:

<QuerySet [
    {'month': 6, 'count': 100 },
    {'month': 7, 'count': 20 }
]>

Months for which there is no Insurance object will not be included in the QuerySet. You thus might have to post-process it and include records with 'count': 0 for these.

edited Nov 24, 2024 at 16:45

answered Dec 14, 2021 at 17:48

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Gokulaselvan Over a year ago

is there a way to get a response for region in every month how many policies have been bought? { 'east': [all month data in a list], 'west':[all month data in a list], 'north' : [all month data in a list] , 'south': [all month data in a list]}

willeM_ Van Onsem Over a year ago

Yes, you add 'customer_region' as first parameter of .values(..) and in the .order_by('customer_region', 'month').

willeM_ Van Onsem Over a year ago

@Gokulaselvan: so

Insurance.objects.values('customer_region', month=ExtractMonth('date_purchased_on')).annotate(count=Count('pk')).order_by('customer_region', 'month')

.

willeM_ Van Onsem Over a year ago

@Gokulaselvan: but the same comment applies: it will not contain records for regions and months if for that region-month combination, there are no Insurances.

Collectives™ on Stack Overflow

Write an Effective query for django for a FOR Loop?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related