select special items from foregin key in django-CodePudding

I have two models

class Product(models.Model):
    name = models.CharField(max_length=50)
    averageRating = models.IntegerField(null=True, blank=True)
    ratingCount = models.IntegerField(null=True, blank=True)
    cover = models.ImageField(upload_to='images/', default=None, blank=True, null=True)
    ...

and

class VariantProduct(models.Model):
    product = models.ForeignKey(Product, on_delete=models.CASCADE)
    mainPrice = models.IntegerField()
    discountPrice = models.IntegerField(null=True, blank=True)

this means different variant of products have different prices.

I need the variant of each product that have minimum mainPrice

in SQL i think this code works for my purpose

        SELECT *, min(discountPrice) as minPrice
        FROM variant GROUP BY product

but I don't know how can I get this result in Django

CodePudding user response：

You can use an annotation to attach the minimum price of VariantProducts attached to Product objects:

from django.db.models import Min

Product.objects.annotate(minimum_main_price=Min('variantproduct__mainPrice'))