1

I want to get last 12 months applicants data in months wise view. If this is March then i need to get data of [3,2,1,12,...,4] in this scenario. Here is my model:

class Applicant(models.Model):
    full_name = models.CharField(max_length=200, null=True, blank=True)
    created_at = models.DateTimeField(auto_now_add=True, auto_now=False)
    .....

While i try query: Applicant.objects.values('created_at__month')annotate(total = Count('created_at__month')) it return values like [{'created_at__month': 2, 'total': 3}, {'created_at__month': 3, 'total': 13}]. If I have 2022's month March total applicant 22 and 2021's month March total applicant 20, this query return value 42 in created_at__month: 3, but I need only past 12 months data from this month.

How can I write this query efficiently?

BikashSaud
  • 314
  • 4
  • 14

1 Answers1

2

Dates are always a bit tricky, I would use the dateutil module to help determine a year ago taking into consideration leap years.

Get the First Day of This Month (March 1st 2022)

from django.utils.timezone import now

first_day_of_this_month = now().replace(day=1)

Get the First Day of Next Month (April 1st 2022)

import datetime

first_day_of_next_month = (
    first_day_of_this_month + datetime.timedelta(days=32)
).replace(day=1)

Subtract 1 Year (April 1st 2021)

from dateutil.relativedelta import relativedelta

first_day_of_twelve_months_ago = first_day_of_next_month - relativedelta(years=1)

Get the start of that day (April 1st 2021 12:00AM)

first_second_of_first_day_twelve_months_ago = datetime.datetime.combine(
    first_day_of_twelve_months_ago, datetime.time.min
)

Filter your Applicants to only those that were created_at after (gte) April 1st 2021 12:00AM

Applicant.objects.filter(
    created_at__gte=first_second_of_first_day_twelve_months_ago
).values("created_at__month").annotate(total=Count("created_at__month"))
Jimmy Pells
  • 674
  • 1
  • 5
  • 12
  • thanks man, before this answer I did by getting all last 12 months and then use for loop to get respected months value. – BikashSaud Mar 08 '22 at 07:07
  • if no data in some months how to get the values like this way `````` – BikashSaud Mar 08 '22 at 07:09
  • @BikashSaud I don't know how to fill in missing months with zero using Django's ORM, I believe you would have to use Raw SQL similiar to https://stackoverflow.com/questions/31682737/django-postgresql-fill-missing-dates-in-a-range – Jimmy Pells Mar 08 '22 at 12:01