Django reverse query by the last created object

问题:

I have two models:

class SomeActivity(models.Model):
    name = models.ChartField(max_length=100)

class SomeStatus(models.Model):
    name = models.CharField(max_length=100)
    status = models.IntegerField(choises=STATUS_CHOISES)
    some_activity = models.ForeignKey(SomeActivity, related_name='statuses')

The last created status for the activity is the current one. To get it I use this code:

try:
    last_status = some_activity.statuses.latest('id')
except:
    last_status = None

But the problem is when I want to make a query that returns all Activities that have a last_status matching status__in=[1, 2].

回答1:

This solution is a little twisted, but I think it will work:

from django.db.models import Max

max_status_ids = SomeActivity.objects.filter(statuses__isnull=False).annotate(
              last_status_id=Max('statuses__id')
              ).values_list('last_status_id', flat=True)
status_satisfied_ids = SomeStatus.objects.filter(id__in=list(max_status_ids),
              status__in=[1, 2]).values_list('id', flat=True)
activities = SomeActivity.objects.filter(statuses__id__in=list(
              status_satisfied_ids))

I hope there is a better solution.

---

update

Try

max_status_ids = SomeActivity.objects.annotate(last_status_id=Max('statuses')
                                    ).values('last_status_id')
activities = SomeActivity.objects.filter(statuses__in=max_status_ids,
                                         statuses__status__in=(1,2))

1. Django automatically generates sub-query when qs, qs.values() or qs.values_list() is used after __in lookup. Thus there is no need to wrap the querysets w/ list() (which also introduces unnecessary evaluation and thus unnecessary intermediate SQLs) or to write flat=True inside qs.values_list()
2. statuses__id__in lookup inside activities already introduces a table join, thus it's bette to move status__in from status_satisfied_ids into activities to take advantage of the join. Or else, status_satisfied_ids would introduce extra select.

Or you could use Window functions of PostgreSQL to filter by ranked position directly.