Perform subqueries for each day in range

Question

I building a 'airbnb clone' app on Django. The app has a flexible price system. Each landlord-user can save arbitrary number of prices which differ by priority and date range.
Here is how it looks like (most of the fields skipped for the sake of simplicity):

class PriceBlock(models.Model):
    price = models.DecimalField()
    priority = models.CharField(choices=PRIORITY_CHOICES)
    start_date = models.DateField()
    end_date = models.DateField()

class Flat(models.Model):
    prices = models.ManyToManyField(PriceBlock, related_name='flats')

For example User created 3 PriceBlock instances with date values in some random month.

p1 - has priority 1, price 100$ and dates from 1 to 3
p2 - has priority 2, price 200$ and dates from 2 to 5
p3 - has priority 3, price 300$ and dates from 4 to 6
To calculate price for flat from 1 to 6 days on this month we need to calculate price of PriceBlock with higher priority on each day.
The problem is - I need calculate price for each flat in the ListView of all flats.
Here's how I do it:

class FlatQueryset(models.QuerySet):
    ...
    def with_block_full_price(self, start, end):
        days = get_days(start, end) # function returns list of days nums
        prices = {}

        for num, day in enumerate(days):
            prices[f'price_{num}'] = Subquery(
                PriceBlock.objects.filter(flats=OuterRef('pk'))
                    .filter(start_date__lte=day, end_date__gte=day)
                    .order_by('-priority')
                    .values('price')[:1],
                output_field=models.IntegerField(null=True))
        return self.annotate(**prices).annotate(full_sum=sum([F(key) for key in prices.keys()]))

I create subqueries in loop, each subquery returns price value most prioritize PriceBlock or null, in case of null I don't show the Flat. Then I add all price values to full_sum value.

As far as I know I can't do queries like this in raw SQL without repeating all subqueries for each date. I also have a task on calculating rating of each flat and I did it in similar way.
I curios maybe there is much better solution on task like this, because my solution looks messy.

Perform subqueries for each day in range

0 Answers