Django QuerySets - how to annotate off one field but return a different field?

Question

Looking at the example given in the docs: https://docs.djangoproject.com/en/3.0/topics/db/aggregation/

Relevant Code to my question (from docs):

from django.db import models

class Author(models.Model):
    name = models.CharField(max_length=100)
    age = models.IntegerField()

class Publisher(models.Model):
    name = models.CharField(max_length=300)

class Book(models.Model):
    name = models.CharField(max_length=300)
    pages = models.IntegerField()
    price = models.DecimalField(max_digits=10, decimal_places=2)
    rating = models.FloatField()
    authors = models.ManyToManyField(Author)
    publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)
    pubdate = models.DateField()

class Store(models.Model):
    name = models.CharField(max_length=300)
    books = models.ManyToManyField(Book)

The examples show how I could get the max price of a book at the store:

>>> from django.db.models import Max, Min
>>> Store.objects.annotate(max_price=Max('books__price'))

However, what I want in my situation is the NAME of the max price book. How can I go about this? I recognize there could me multiple books with max price, but for my use case the tie breaking criteria doesn't matter.

In my use case I will also be passing the QuerySet to a template so I would prefer to use the annotation framework.

Answer 1

You can make use of a Subquery expression [Django-doc]:

from django.db.models import OuterRef, Subquery

Store.objects.annotate(
    most_expensive_book_name=Subquery(
        Book.objects.filter(
            store=OuterRef('pk')
        ).values('name').order_by('-price')[:1]
    )
)