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?