Case/When, F arithmetic, ExpressionWrapper, Coalesce

# ======================================================================
# `Case`/`When` is SQL CASE. `F()` references a column so the arithmetic happens
# in the database, not Python. `ExpressionWrapper` declares the output type for
# mixed-type math (Decimal × Integer). `Coalesce` returns the first non-NULL
# argument.
# ======================================================================

# ----------------------------------------------------------------------
# price tier + price*pages
# ----------------------------------------------------------------------

# Django:
Book.objects.annotate(
    tier=Case(
        When(price__gte=25, then=Value("premium")),
        When(price__gte=10, then=Value("standard")),
        default=Value("free"),
    ),
    value=ExpressionWrapper(
        F("price") * F("pages"),
        output_field=DecimalField(max_digits=12, decimal_places=2),
    ),
).values_list("title", "tier", "value")

# SQL:
#   SELECT "examples_book"."title" AS "title", CASE WHEN "examples_book"."price" >= 25 THEN 'premium' WHEN "examples_book"."price" >= 10 THEN 'standard' ELSE 'free' END AS "tier", ("examples_book"."price" * "examples_book"."pages") AS "value"
#   FROM "examples_book"

# Result:
#   Harry Potter: standard  (price*pages=6396.80)
#   The Hobbit: standard  (price*pages=4495.00)
#   The Lord of the Rings: premium  (price*pages=34162.00)
#   Postgres for Authors: free  (price*pages=0.00)

# ----------------------------------------------------------------------
# Coalesce(bio, '(no bio)')
# ----------------------------------------------------------------------

# Django:
Author.objects.annotate(
    b=Coalesce("bio", Value("(no bio)"), output_field=TextField())
).values_list("name", "b")

# SQL:
#   SELECT "examples_author"."name" AS "name", COALESCE("examples_author"."bio", '(no bio)') AS "b"
#   FROM "examples_author"

# Result:
#   J. K. Rowling: British author
#   J. R. R. Tolkien: (no bio)
#   Karl Müller: Schriftsteller