I probably haven’t phrased this question correctly so apologies. Also probably why I cannot find much information when I google it. Anyway I have a Django project that I am using to create an API using Django Rest Framework. For this problem I have 2 models that are relevant. A Product model and an Offer model (slightly pseudocode so ignore any missing fields).

class Offer(models.Model):
    image = models.ImageField(upload_to=upload_to, validators=(validate_image_file_extension,))
    discount = models.IntegerField(validators=(MinValueValidator(0), MaxValueValidator(100)))
    start_date = models.DateField()
    expiration_date = models.DateField()
    products = models.ManyToManyField(Product, related_name='offers')
    customers = models.ManyToManyField(Customer)

So as you can see in this model. There’s a many to many link to the Product model. Inside the product model is just some standard stuff:

class Product(models.Model):
    name = models.CharField(max_length=256)
    price = models.FloatField()
    stock = models.IntegerField()
    product_photo = models.ImageField(null=True, upload_to=product_photo_path,
                                      validators=[validate_image_file_extension])

Okay so now for the actual question. I am trying to add a new feature where it gets the discounted price for a product. If there is multiple offers, it needs to return the highest discount price. It also needs to take into account the customer who sends the request because the offers are not relevant for every customer. This needs to be done on the list endpoint from the API so it has quite a few entries. My question is, what is the simplest way that is efficient to do this?

Some of the solutions I tried from the top of my head were to add a function to the product model as follows:

def discounted_price(self, customer):
        highest_discounted_offer = self.offers.filter(start_date__lte=datetime.now().date(),
                                                      expiration_date__gte=datetime.now().date(),
                                                      customers__in=[customer]).order_by(
            'discount').first()
  
        return self.price - (self.price * highest_discounted_offer.discount / 100)

This solution works, but the issue is when I use it on the serializer it runs for every single product and I have an N+1 query. Also I am using prefetch_related but since I need to get the highest value I don’t think this really helps.

Then I tried to do it with a custom manager, but I didn’t like how complex it was along with me not fully understanding the code:

class ProductManager(models.Manager):
    def with_discounted_prices(self, customer):
        Offer = apps.get_model('offers', 'Offer') # avoid circular import.
         highest_discount = Offer.objects.filter(
                products=OuterRef('pk'),
                start_date__lte=timezone.now().date(),
                expiration_date__gte=timezone.now().date(),          
                customers__in=[customer]
            ).order_by('-discount')

            products = self.annotate(highest_discount=Subquery(highest_discount.values('discount')[:1]))

            products = products.annotate(
                discounted_price=ExpressionWrapper(F('pricer') - (F('pricer') * F('highest_discount') / 100),
                                                   output_field=FloatField()
                                                   ))

            return products

This works and is way more efficient, but I don’t really know if it’s the best way to do this? It also doesn’t seem very scalable if there ends up being more fields like this needed in the future. Is there a simpler way that I am not thinking of?