Subquery / OuterRef / Exists

# ======================================================================
# `OuterRef('pk')` refers to the outer row from inside a subquery.
# `Subquery(qs[:1])` pulls a single correlated value per row, while `Exists()`
# compiles to SQL EXISTS — far cheaper than counting when you only need a
# yes/no.
# ======================================================================

# ----------------------------------------------------------------------
# per author
# ----------------------------------------------------------------------

# Django:
newest = Book.objects.filter(author=OuterRef("pk")).order_by("-published")
Author.objects.annotate(
    newest_title=Subquery(newest.values("title")[:1]),
    has_free=Exists(Book.objects.filter(author=OuterRef("pk"), price=0)),
)

# SQL:
#   SELECT "examples_author"."id", "examples_author"."name", "examples_author"."bio", "examples_author"."born", "examples_author"."rating", "examples_author"."nickname", (SELECT U0."title" AS "title"
#   FROM "examples_book" U0
#   WHERE U0."author_id" = ("examples_author"."id")
#   ORDER BY U0."published" DESC
#   LIMIT 1) AS "newest_title", EXISTS(SELECT 1 AS "a"
#   FROM "examples_book" U0
#   WHERE (U0."author_id" = ("examples_author"."id") AND U0."price" = 0)
#   LIMIT 1) AS "has_free"
#   FROM "examples_author"

# Result:
#   J. K. Rowling: newest='Harry Potter'  has_free_book=False
#   J. R. R. Tolkien: newest='The Lord of the Rings'  has_free_book=False
#   Karl Müller: newest='Postgres for Authors'  has_free_book=True