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

Question

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

score 2 · Answer 1 · answered Jul 28 '22 at 11:00

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)

wisely answer but i think there most be a ORM solution like Django Func — kazem qanati, Jul 28 '22 at 11:04

0sVoid · Accepted Answer · 2022-07-29T07:50:24.737

2

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 - removed incorrect code addition

edited Jul 29 '22 at 07:50

answered Jul 28 '22 at 11:27

0sVoid

2,547
1
10
25

what dose 'Max' do can we use 'Value' insted? – kazem qanati Jul 28 '22 at 12:25
1

Value is just used to insert a value into the expression such as 0, 1, True, False, etc. and doesn't actually *get* the object value. You can use `F()` to just get the value, but it doesn't allow you to include a filter - the filter is needed to get the date range. Max does allow a filter and will just return the highest value within that filtered set, essentially. – 0sVoid Jul 28 '22 at 12:42
ok,so you use Max only for include filter – kazem qanati Jul 28 '22 at 13:08
1

Exactly, you can use filter with `Sum`, `Count`, and the other functions too – 0sVoid Jul 28 '22 at 13:13
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() – kazem qanati Jul 28 '22 at 13:55
1

You can do something very similar but with different expressions to remove the relation - I will update my answer – 0sVoid Jul 28 '22 at 15:14
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/246870/discussion-between-kazem-qanati-and-0svoid). – kazem qanati Jul 28 '22 at 20:26

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

2 Answers2