Django queryset order by latest value in related field

Question

Consider the following Models in Django:

class Item(models.Model):
  name = models.CharField(max_length = 100)

class Item_Price(models.Model):
  created_on = models.DateTimeField(default = timezone.now)
  item = models.ForeignKey('Item', related_name = 'prices')
  price = models.DecimalField(decimal_places = 2, max_digits = 15)

The price of an item can vary throughout time so I want to keep a price history.

My goal is to have a single query using the Django ORM to get a list of Items with their latest prices and sort the results by this price in ascending order.

What would be the best way to achieve this?

Answer 1

You can use a Subquery to obtain the latest Item_Price object and sort on these:

from django.db.models import OuterRef, Subquery

last_price = Item_Price.objects.filter(
    item_id=OuterRef('pk')
).order_by('-created_on').values('price')[:1]

Item.objects.annotate(
    last_price=Subquery(last_price)
).order_by('last_price')

For each Item, we thus obtain the latest Item_Price and use this in the annotation.

That being said, the above modelling is perhaps not ideal, since it will require a lot of complex queries. django-simple-history [readthedocs.io] does this differently by creating an extra model and save historical records. It also has a manager that allows one to query for historical states. This perhaps makes working with historical dat simpeler.