Get max Value with Distinct Foreign key Django ORM-CodePudding

So this is my Model.

class OrderLine(models.Model):
    product = models.ForeignKey(Product, on_delete=models.PROTECT, verbose_name="Product",      null=False)
    unit_price = models.DecimalField(null=True, max_digits=12, decimal_places=4, blank=True, verbose_name="Unit price")

Im trying to filter with Multiple Product Ids, One product can have multiple OrderLine with different Unit price.So how can i fetch one record of Max Unit price for Each product.

I tried

to_lines = (OrderLine.objects.filter(transfer_id__in=to_ids,product_id__in=part_ids).values("unit_price").order_by("product_id").aggregate(Max("unit_price")))

But it returns one over all the products.

CodePudding user response：

You can use a GROUP BY expression as

from django.db.models import Max

result = OrderLine.objects.values("product").annotate(max_per_prod=Max("unit_price"))

This is almost similar to the SQL expression

SELECT product_id, MAX(unit_price) FROM table_name GROUP BY product_id