Django QuerySet：如何基于价格字段标注并返回对应书籍名称？

Hey there! I get that you want to attach the name of the highest-priced book (or books, if there's a tie) to each Store object using Django's annotate, so you can pass the QuerySet straight to your template. Here's how to do it:

Single Book Name (Ignoring Ties) #

If you don't need to handle multiple books with the same max price, this approach will grab one of them (the first one in the query results):

from django.db.models import Max, Subquery, OuterRef

# First, create a subquery to get the maximum price for each store
max_price_subquery = Book.objects.filter(
    store=OuterRef('pk')  # Reference the current Store's ID
).values('store').annotate(max_price=Max('price')).values('max_price')

# Then, get the name of a book that matches that max price for the store
top_book_name_subquery = Book.objects.filter(
    store=OuterRef('pk'),
    price=Subquery(max_price_subquery[:1])  # Use the max price from the first subquery
).values('name')[:1]

# Annotate each Store with both the max price and the top book name
stores = Store.objects.annotate(
    max_price=Max('books__price'),
    top_book_name=Subquery(top_book_name_subquery)
)

In your template, you can loop through the stores like this:

{% for store in stores %}
    <div class="store">
        <h3>{{ store.name }}</h3>
        <p>Most Expensive Book: {{ store.top_book_name }} (${{ store.max_price }})</p>
    </div>
{% endfor %}

Handling Multiple Books with the Same Max Price #

If you need to show all books that share the highest price (and you're using PostgreSQL, which supports array aggregates), use ArrayAgg to collect all their names:

from django.db.models import Max, Subquery, OuterRef
from django.contrib.postgres.aggregates import ArrayAgg

# Reuse the max price subquery from above
max_price_subquery = Book.objects.filter(
    store=OuterRef('pk')
).values('store').annotate(max_price=Max('price')).values('max_price')

# Collect all book names with the max price using ArrayAgg
top_book_names_subquery = Book.objects.filter(
    store=OuterRef('pk'),
    price=Subquery(max_price_subquery[:1])
).values('store').annotate(names=ArrayAgg('name')).values('names')

# Annotate the Store queryset with the array of book names
stores = Store.objects.annotate(
    max_price=Max('books__price'),
    top_book_names=Subquery(top_book_names_subquery)
)

Then in your template, loop through the array of names:

{% for store in stores %}
    <div class="store">
        <h3>{{ store.name }}</h3>
        <p>Most Expensive Books (${{ store.max_price }}):</p>
        <ul>
            {% for book_name in store.top_book_names %}
                <li>{{ book_name }}</li>
            {% endfor %}
        </ul>
    </div>
{% endfor %}

How This Works #

• OuterRef lets us reference the ID of the current Store object we're annotating, so each store gets its own max price calculation.
• Subquery runs the inner query once per Store, ensuring we get the correct max price and corresponding books for each.
• Annotating these values directly onto the Store QuerySet means you can pass it straight to your template without extra processing.

内容的提问来源于stack exchange，提问作者Johnny