How to annotate related object with MAX by some attribute

user_14789698 0
Viewed 209
class Bill(models.Model):
    number = CharField(max_length=32)

class Service(models.Model):
    number = CharField(max_length=32)
    bill = ForeignKey(Bill, related_name="bill_services")
    price = IntegerField()

bills = Bill.objects.prefetch_related('bill_services').annotate(service_number_with_max_price=....)

Hello. How can I annotate a service number with max price for each Bill. I want to use it for something like that:

{% for bill in bills %}
    {{ bill.service_number_with_max_price }}
{% endfor %}
orm django-models django
2 Answers

You can annotate a subquery to do this:

from django.db.models import OuterRef, Subquery

subquery = Service.objects.filter(bill=OuterRef('pk')).order_by('-price').values('number')[:1]
bills = Bill.objects.prefetch_related('bill_services').annotate(service_number_with_max_price=Subquery(subquery))

The subquery does the following to achieve your use case:

  1. .filter(bill=OuterRef('pk')): Gets Service objects with foreign key to outer queries current row.
  2. .order_by('-price'): Orders the results in descending order of price.
  3. .values('number'): Select only the number field.
  4. [:1]: Limit the results to one row for the subquery.
user_149918640

You can use the aggregate method.

from django.db.models import Max

query = Bill.objects.all().prefetch_related("bill_services").aggregate(max_price=Max("bill_services__price"))

In the template, you can access the max price of the service by

{% for bill in bills %}
    {{ bill.max_price }}
{% endfor %}
user_125401830
Related