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.

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

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.

1tickers_with_latest_price = (
2    Ticker.objects.annotate(
3        latest_price=TickerPrice.objects.filter(ticker=models.OuterRef("pk"))
4        .order_by("-close_date")
5        .values("price")[:1]
6    )
7    .filter(latest_price__gte=50)
8)

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.