[FIXED] Django model design pattern - get youngest related object

Issue

Django lets you follow relations link, but none of the filter methods let you get youngest/oldest, or max/min, afaik.

Laravel has "has one of many", and I wish that Django had something similar without requiring window functions, that have their own limitations.

Annotations despite being promising, are also a dead end.

Therefore I wonder what the best design is for the following situation:

I have "model A", whose instances will pass through several statuses during their lifecycle ("created", "processing", "complete"). I want to know what status an instance of model A currently has, but also have a record of when each status was in effect. I don't want to parse logs.

I thought a good approach was to create a status model (model B) whose foreign key is a model A instance. it becomes easy to see when each status was started and stopped.

However if I want to get the current status (an instance of model B) for all my model A instances, I need to do n+1 database queries. This seems sub-optimal.

What are some alternatives?

Solution

However if I want to get the current status (an instance of model B) for all my model A instances, I need to do n+1 database queries. This seems sub-optimal.

No, you can make use of Subquery expressions ^[Django-doc]. Indeed, if you have two models:

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


class ItemStatus(models.Model):
    item = models.ForeignKey(Item, on_delete=models.CASCADE)
    status = models.CharField(max_length=128)
    started = models.DateTimeField(auto_now_add=True)

You can annotate each Item with the last status with:

from django.db.models import OuterRef, Subquery

Item.objects.annotate(
    last_status=Subquery(
        ItemStatus.objects.filter(
            item_id=OuterRef('pk')
        ).order_by('-started').values('status')[:1]
    )
)

For each Item, there will be an extra attribute named .last_status that will contain the .status of the related ItemStatus that started last. If there is no such StatusItem, last_status will be None (NULL).

This will be determined by subqueries at the database side, hence it is done in the same query where you retrieve the Items, and thus does not suffer from the N+1 problem.

Answered By - Willem Van Onsem