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.