I have a simple django project which displays previous reservations dates based on id’s. However currently there are 2 requests being made. (N+1 sql requests, where N is the reservation’s count) Do you have any idea how i would be able to optimize this code to only 1 query?
This is the model.py file
from django.db import models class Rental(models.Model): name = models.CharField(max_length=100) def __str__(self): return self.name class Reservation(models.Model): rental = models.ForeignKey( Rental, on_delete=models.CASCADE, related_name="reservations") checkin = models.DateField() checkout = models.DateField() def get_previous_reservation(self): latest_reservation = Reservation.objects.order_by('-checkout').filter( rental=self.rental, checkout__lt=self.checkout).first() if latest_reservation is not None: return latest_reservation.id return '-' def __str__(self): return f"({self.id}, {self.checkin}, {self.checkout})"
This is the view.py file -> Where the queries are being made
from django.views.generic import CreateView from .models import Reservation from .forms import ReservationForm class HomeView(CreateView): template_name = "index.html" form_class = ReservationForm success_url = '/' def get_context_data(self, **kwargs): context = super().get_context_data(**kwargs) context['reservations'] = Reservation.objects.all( ).select_related('rental') return context
The get_reservation function in the model is being called in the template file here:
{% extends 'base.html' %} {% block content %} <h2>Reservations</h2> <table border="1" style="border-collapse: collapse; margin-top: 15px;"> <thead> <tr> <th>Rental_name</th> <th>ID</th> <th>Checkin</th> <th>Checkout</th> <th>Previous Reservation ID</th> </tr> </thead> <tbody> {% for reservation in reservations %} <tr> <td>{{ reservation.rental.name }}</td> <td>{{ reservation.id }}</td> <td>{{ reservation.checkin|date:'Y-m-d' }}</td> <td>{{ reservation.checkout|date:'Y-m-d' }}</td> <td>{{ reservation.get_previous_reservation }}</td> </tr> {% endfor %} </tbody> </table> <h2>Add new reservation</h2> <form action="." method="post"> {% csrf_token %} {{ form.as_p }} <button type="submit">Submit</button> </form> {% endblock content %}
Advertisement
Answer
My only thought is to add it as part of the first request and remove get_previous_reservation
from being called. Pseudocode-ish:
def get_context_data(self, **kwargs): context = super().get_context_data(**kwargs) # You have all reservations here. reservations = Reservation.objects.all( ).select_related('rental') # Since you have all reservations, work out the previous reservation # for each reservation. reservations_response = {} for r in reservations: related = reservations.order_by('-checkout').filter( rental=self.rental, checkout__lt=self.checkout).first() related_reservations[r.id] = related. context['reservations'] = reservations_response return context
My point is you need to do it after you make the first query (my code above is only approximate, you may want to use a custom class instead of a dict like I did. Hope it helps.