query to django model to find best company sale in the month-CodePudding

I have two django model one "company" and the other is "MonthlyReport" of the company I want to find out which company sale in current month had more than 20% of previous month sale

class Company(models.Model):
name = models.CharField(max_length=50)


class MonthlyReport(models.Model):
    company = models.ForeignKey(Company,on_delete=models.CASCADE)
    sale = models.IntegerField()
    date = models.DateField()

How can i figure out this issue to find a company that has more than 20% sales over the previous month

CodePudding user response：

You can certainly do it using the ORM. You will need to combine Max (or SUM depending on your use case) with a Q() expression filter and annotate the percentage increase to the queryset before filtering it.

You could do it in a single piece of code, but I have split it out because getting the dates and the query expressions are quite long. I have also put the increase value in a separate variable, rather than hardcoding it.

from datetime import datetime, timedelta
from django.db.models import Max, Q

SALES_INCREASE = 1.2

# Get the start dates of this month and last month
this_month = datetime.now().date().replace(day=1)
last_month = (this_month - timedelta(days=15)).replace(day=1)

# Get the maximum sale this month
amount_this_month = Max('monthlyreport__sale', 
    filter=Q(monthlyreport__date__gte=this_month))

# Get the maximum sale last month, but before this month
amount_last_month = Max('monthlyreport__sale', 
    filter=Q(monthlyreport__date__gte=last_month) & \
        Q(monthlyreport__date__lt=this_month))

Company.objects.annotate(
    percentage_increase=amount_this_month/amount_last_month
    ).filter(percentage_increase__gte=SALES_INCREASE)

Edit - updated answer, if we wanted to remove Company:

# use the same imports and date variables

# Get the maximum sale this month
amount_this_month = Max('sale', filter=Q(date__gte=this_month))

# Get the maximum sale last month, but before this month
amount_last_month = Max('sale', filter=Q(date__gte=last_month) & \
        Q(date__lt=this_month))

MonthlyReport.objects.annotate(
    percentage_increase=amount_this_month/amount_last_month
    ).filter(percentage_increase__gte=SALES_INCREASE)

CodePudding user response：

There is probably a way to do this using ORM, but I would just go with python way:

First add related name to MonthlyReport

class Company(models.Model):
    name = models.CharField(max_length=50)


class MonthlyReport(models.Model):
    company = models.ForeignKey(Company, related_name="monthly_reports", on_delete=models.CASCADE)
    sale = models.IntegerField()
    date = models.DateField()

Then

best_companies = []

companies = Company.objects.all()
for company in companies:
    two_last_monthly_reports = company.monthly_reports.order_by("date")[:2]
    previous_report = two_last_monthly_reports[0]
    current_report = two_last_monthly_reports[1]

    if current_report.sale / previous_report.sale > 1.2:
        best_companies.append(company)

CodePudding user response：

what if we remove company model and use only monthlReport like :

class MonthlyReport(models.Model):
    company = models.CharField(max_length=50)
    sale = models.IntegerField()
    date = models.DateField()