CODEWITHSUNDEEP
pythondjango
Boky
Boky

Reputation: 12054

Getting result grouped by month in Django

I want to get all members from a Member model and get total calculations that they have done per month.

My query is as follows:

members_list = Member.objects\
        .exclude(deleted=True) \
        .annotate(num_calculations=Count('user__calculations'))\
        .order_by("-user__date_joined")

With this query I get all members and total calculations that they have done.

How can I get total calculations per month(last 12 months)?

Calculation model is as follows:

class Calculations(models.Model):
    user = models.ForeignKey(to=User)
    make = models.CharField(max_length=127)
    model = models.CharField(max_length=127)        
    mileage = models.IntegerField()        
    price_date = models.DateTimeField(auto_now_add=True)
    sales_price_currency = models.CharField(max_length=10, null=True)

And member model is as follows:

class Member(models.Model):
    user = models.ForeignKey(to=User)
    number = models.CharField(max_length=10)        
    company_name = models.CharField(max_length=100)
    address = models.CharField(max_length=255)
    mobile = models.CharField(max_length=50)
    active = models.BooleanField()
    deleted = models.NullBooleanField(null=True)

Upvotes: 3

Views: 177

Answers (1)

Boky
Boky

Reputation: 12054

I solve it. In case that someone else needs it here is the solution.

First I got all calculation per month as follows:

items = list(Calculations.objects
                     .filter(user__member__number=member_number)
                     .filter(price_date__gte=datetime.datetime.now().today() - relativedelta(months=12))
                     .annotate(date=TruncMonth('price_date'))
                     .values('date')
                     .annotate(total=Count('id'))
                     .values('date', 'total')
                     .order_by('date'))

But here I get only months where the user did the calculations, but I need the last 12 months no matter if the user did the calculations in that month or not.

To get that right I did next:

result = []
for month in range(12):
     date = timezone.now() - relativedelta(months=month)
     next_month = date + relativedelta(months=1)

     month_results = list(filter(lambda i: date <= i['date'] + relativedelta(months=1) < nextMonth, items))

     month_result = 0

     if month_results:
        month_result = month_results[0]['total']

     result.append({'total_calculations': month_result})

That did the trick.

Upvotes: 2

Related Questions