Annotate Querysets to Fetch Specific Values

Annotating a queryset enables us to add attributes to each object in the queryset. Annotations can be a reference to a value on the model or related model or an expression such as a sum or count.

tickers_with_latest_price = Ticker.objects.annotate(
    latest_price=TickerPrice.objects.filter(
        ticker=models.OuterRef("pk")
    )
    .order_by("-close_date")
    .values("price")[:1]
)

This queryset fetches all the tickers and annotates each ticker object with a latest_price attribute. The latest price comes from the most recent related ticker price. The OuterRef allows us to reference the primary key of the ticker object. We use order_by to get the most recent price and use values to select only the price. Finally, the [:1] ensures we retrieve only one TickerPrice object.

We could also query against our annotation.

tickers_with_latest_price = (
    Ticker.objects.annotate(
        latest_price=TickerPrice.objects.filter(ticker=models.OuterRef("pk"))
        .order_by("-close_date")
        .values("price")[:1]
    )
    .filter(latest_price__gte=50)
)

We added an extra filter statement after our annotation. In this query, we fetch all tickers where the latest price is greater than or equal to fifty.