#django #pattern #python
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
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.
If this post was enjoyable or useful for you, please share it! If you have comments, questions, or feedback, you can email my personal email. To get new posts, subscribe use the RSS feed.