How to make a condition in subquery based on outer query?

Viewed 1374

I work on Airbnb like app. I have a Flat model and Price model, prices have different types of priority which allows customers create a flexible price range.

I stuck with one complicated query. This query should return Flats by date range and calculate prices for this range.


Here the part of my models:

class Flat(models.Model):
    prices = models.ManyToManyField(
        'Price'
        related_name='flats'
    )
    price = models.PositiveIntegerField()
    ...

class Price(models.Model):
    PRIORITY_CHOICES = ((i, i) for i in range(1, 6))

    priority = PositiveIntegerField(choices=PRIORITY_CHOICES)
    start_date = models.DateField()
    end_date = models.DateField()
    price = models.PositiveIntegerField()

So far I figure it out how to annotate prices with higher priority by each day. I wrote a custom manager for Flat:

class FlatManager(models.Manager):

    def with_prices(self, start, end):
        days = get_days(start, end) # utils function return list of days
        prices = {}
        for day in days:
            prices[str(day)] = models.Subquery(
            Price.objects.filter(
                flats=models.OuterRef('pk')).
                filter(start_date__lte=day, end_date__gte=day).
                order_by('-priority').
                values('price')[:1]
        )
        return self.annotate(**price_dict)

Here is my problem:

Some dates in the Flat can be without price blocks, so Flat have its own price field for cases when customer don't won't to use flexible prices. I don't know where I need to add a condition operator in my query. If I add it in the Price subquery then I don't be able to use Outref('price') because of nesting. When I solve it, calculate the sum of aggregated values will not be so complicated for me I think.

Please give some hints at least, I really stuck with it.

1 Answers
Related