Booking/Reservation average price query for a period

Using Django Using the ORM
1

For several days I’m trying to wrap my head around how I should build a subquery that would calculate average price for the reservation period. Currently I’ve built a working subquery that calculates only the price of the start_date.

This is current version of the subquery that calculates price for the start_date:

calculate_price_for_sauna = (
            RentableInventoryPricing.objects.filter(
                inventory=OuterRef("sauna_inventory__id")
            )
            .annotate(
                weekday=ExtractWeekDay(start_date),
                month=ExtractMonth(start_date),
                day=ExtractDay(start_date),
            )
            .filter(
                Q(weekday__lte=4, day_of_week=1)  # Monday to Thursday pricing
                | Q(weekday=5, day_of_week=2)  # Friday pricing
                | Q(weekday=6, day_of_week=3)  # Saturday pricing
                | Q(weekday=7, day_of_week=4)  # Sunday pricing
            )
            .filter(
                Q(
                    Q(season__start_month__lt=F("month"))
                    | Q(
                        season__start_month=F("month"),
                        season__start_day__lte=F("day"),
                    )
                )
                & Q(
                    Q(season__end_month__gt=F("month"))
                    | Q(
                        season__end_month=F("month"),
                        season__end_day__gte=F("day"),
                    )
                )
                | Q(
                    Q(season2__start_month__lt=F("month"))
                    | Q(
                        season2__start_month=F("month"),
                        season2__start_day__lte=F("day"),
                    )
                )
                & Q(
                    Q(season2__end_month__gt=F("month"))
                    | Q(
                        season2__end_month=F("month"),
                        season2__end_day__gte=F("day"),
                    )
                )
            )
            .values("price_per_day")
        )

I’ve tried to generate a list of reservation dates and somehow pass it into the query instead of the start_date. That didn’t go well:

        start_date = timezone.make_aware(
            datetime.fromisoformat(request.GET.get("startDate"))
        )
        end_date = timezone.make_aware(
            datetime.fromisoformat(request.GET.get("endDate"))
        )
        day_difference = (end_date.date() - start_date.date()).days
        days = []
        for x in range(day_difference):
            days.append(start_date.date() + timedelta(days=x))

In the final query I annotate a field using the Subquery:

total_price=Subquery(calculate_price_for_sauna),

I could probably calculate price for each day in a reservation in python, but I would like to avoid that, because of the performance.

Any ideas?

2

Welcome @RimJur !

I think for us to be able to offer suggestions, we’d need to see the models involved in the query.

And, for clarification, you’re looking for a query to calculate the sum of the prices between the start date and end date (inclusive) divided by the number of days. Is that correct?

3

Hey, Ken,

This is correct, although the last day (end date) should not be included, since it’s the checkout date and the price should not be calculated for it.

What goes for the models, the most important are:

  1. RentableInventoryPricing
  2. RentableInventory
  3. PricingSeason
  4. Sauna
class RentableInventoryPricing(models.Model):
    class PricingDays(models.IntegerChoices):
        MONDAY_TO_THURSDAY = 1, "Monday-Thursday"
        FRIDAY = 2, "Friday"
        SATURDAY = 3, "Saturday"
        SUNDAY = 4, "Sunday"

    inventory = models.ForeignKey(
        RentableInventory,
        on_delete=models.CASCADE,
        related_name="inventory_pricing",
    )
    season = models.ForeignKey(
        ("rentals.PricingSeason"),
        on_delete=models.RESTRICT,
    )
    season2 = models.ForeignKey(
        ("rentals.PricingSeason"),
        on_delete=models.RESTRICT,
        null=True,
        blank=True,
    )
    day_of_week = models.PositiveSmallIntegerField(
        choices=PricingDays
    )
    price_per_day = models.DecimalField(
        max_digits=5, decimal_places=2
    )

class RentableInventory(models.Model):
    rental = models.ForeignKey(
        ("rentals.Rental"),
        on_delete=models.CASCADE,
        related_name="rentable_inventory",
    )
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

class PricingSeason(models.Model):
    class Months(models.IntegerChoices):
        JANUARY = 1, "January"
        FEBRUARY = 2, "February"
        MARCH = 3, "March"
        APRIL = 4, "April"
        MAY = 5, "May"
        JUNE = 6, "June"
        JULY = 7, "July"
        AUGUST = 8, "August"
        SEPTEMBER = 9, "September"
        OCTOBER = 10, "October"
        NOVEMBER = 11, "November"
        DECEMBER = 12, "December"

    work_season = models.ForeignKey(WorkSeason, on_delete=models.CASCADE)
    start_month = models.PositiveSmallIntegerField(
        choices=Months
    )
    start_day = models.PositiveIntegerField(
        choices=[(i, i) for i in range(1, 29)]
    )
    end_month = models.PositiveSmallIntegerField(
        choices=Months
    )
    end_day = models.PositiveIntegerField(
        choices=[(i, i) for i in range(1, 29)]
    )

class Sauna(models.Model):
    sauna_inventory = models.OneToOneField(
        RentableInventory,
        on_delete=models.CASCADE,
    )
    rental_alone = models.BooleanField(
        default=False
    )
    description = models.TextField()
    amount = models.PositiveSmallIntegerField(
        default=1,
        validators=[MaxValueValidator(10)],
    )
    free_transportation_distance = models.PositiveSmallIntegerField(
        default=50,
        validators=[MaxValueValidator(200)],
    )