CODEWITHSUNDEEP
pythondjango
Akamaru
Akamaru

Reputation: 63

Django queryset order by latest value in related field

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?

Upvotes: 6

Views: 1138

Answers (2)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476557

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.

Upvotes: 1

Rfroes87
Rfroes87

Reputation: 678

You could prefetch them in order to do the nested ordering inline like the following:

from django.db.models import Prefetch

prefetched_prices = Prefetch("prices", queryset=Item_Price.objects.order_by("price"))

for i in Item.objects.prefetch_related(prefetched_prices): i.name, i.prices.all()

Upvotes: 0

Related Questions