2

My question is regarding best practices for querying a database in a View using Django.

I want a daily count of reservations for the past 7 days. Is it okay to have something like this? (below)

count_day_1 = len(Reservations.objects.filter(date=TODAY_DATE).filter(is_reserved=True))
count_day_2 = len(Reservations.objects.filter(date=DATE_TODAY_MINUS_1).filter(is_reserved=True))
count_day_3 = len(Reservations.objects.filter(date=DATE_TODAY_MINUS_2).filter(is_reserved=True))
count_day_4 = len(Reservations.objects.filter(date=DATE_TODAY_MINUS_3).filter(is_reserved=True))
count_day_5 = len(Reservations.objects.filter(date=DATE_TODAY_MINUS_4).filter(is_reserved=True))
count_day_6 = len(Reservations.objects.filter(date=DATE_TODAY_MINUS_5).filter(is_reserved=True))
count_day_7 = len(Reservations.objects.filter(date=DATE_TODAY_MINUS_6).filter(is_reserved=True))

Or does having that Reservations.objects.filter() in there 7 times ping the database 7 times?

If the above way isn't recommended, should I set it up something like this instead? (below)

data = Reservations.objects.filter(is_reserved=True)

for item in data:
    if item.date == TODAY_DATE:
        print(item.date)
    if item.date == DATE_TODAY_MINUS_1:
        print(item.date)

(...so on and so forth)

Any advice would be great.

Thanks!

Shawn
  • 67
  • 1
  • 5
  • It would be valuable for you to work out how to log the SQL queries that a given Django feature makes, then you'll know if this is 7 queries or 1. If it's 7, and it's genuinely a performance issue that you are sure can be solved with 1 query, then issue a single query for the entire 7 days, and filter the results into 7 buckets in your code. – jarmod Aug 19 '21 at 18:53

1 Answers1

3

firstly we will get the datetime object of the date 7 days ago

from datetime import datetime, timedelta
previous_date = datetime.now() - timedelta(days=7)

Then we will use the TruncDate and get the counts by date

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

data = Reservations.objects.filter(date__gte=previous_date, is_reserved=True)\
.annotate(day=TruncDate('date'))\
.values('day').annotate(count=Count('id'))\
.values('day', 'count')

This will give you the result something like this

<QuerySet [{'day': datetime.date(2021, 8, 10), 'count': 5}, 
{'day': datetime.date(2021, 8, 11), 'count': 4}, 
{'day': datetime.date(2021, 8, 12), 'count': 4}, 
{'day': datetime.date(2021, 8, 13), 'count': 6}]>

Got the idea from this answer please see more in the official documentation about annotate, TruncDate, TruncMonth, TruncYear

shourav
  • 946
  • 9
  • 15