How to optimally filter on an optional related table?

Question

Django

There are 2 tables:

class Sale(models.Model):
  percent = models.FloatField()

class Product(models.Model):
  sale = models.ForeignKey(Sale, null=True, blank=True)
  price = models.FloatField()

You need to filter products by price range. At the same time, the discount can affect the price, i.e. if we filter by price from $10 to $80, then a product with a price of $120 with a 50% discount should be included in the selection, because with a discount it will cost $60.

products = Product.objects.filter(price__range=(10, 80))

not suitable, because does not include discount.

products = Product.objects.annotate(price_with_sale=F('price')*(1-F('sale__percent')))
products = products.filter(price_with_sale__range=(10, 80))

not suitable because Not all items are discounted.
The option to create a zero discount for products by default is not considered.
There is an option to split the sample into two: discounted products and non-discounted products. Filter the first one by one, and the second one by another. And finally combine them. These are two additional requests. It doesn't seem optimal to me.

def filter_prices(self, products, price_from, price_to):
  products1 = Product.objects.filter(sale__isnull=True)
  products1 = products1.filter(price__range=(price_from, price_to))

  products2 = Product.objects.filter(sale__isnull=False)
  products2 = products2.annotate(new_price=F('price')*(1-F('sale__percent')))
  products2 = products2.filter(new_price__range=(price_from, price_to))

  ids = list(chain(products1.values_list('id', flat=True), products2.values_list('id', flat=True)))
  return products.filter(id__in=ids)

Frankly, I can’t even imagine how this can be done with an sql query, therefore, orm will not do anything here either.
Am I wanting the impossible, or am I missing some elementary way out of sight?

Answer 1

Product.objects.annotate(
    price_with_sale=F('price')*(1-F('sale__percent'))
).filter(
    Q(price__range=(10, 80)) | Q(price_with_sale__range=(10, 80))
)

everything should work out fine