Skip to content
Advertisement

How can I optimze Django Code to only one query request

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.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement