Django Filter by latest related object

Question

I've setup a system to track moderation tasks for various objects in the database. These are linked via a Generic Foreign Key relation. Given a ObjectModerationState Object I must determine its state from the most recent StateTransisition object.

The solution must lend it self to be used in a SimpleListFilter for sorting on list_display using list_filter.

e.g. Given States - Queued - Completed - Verify

I should be able to filter ObjectModerationState Objects, based on the latest state value in the StateTransisition object.

I've been leaning towards some sort of annotation on the ObjectModerationState.

Here's what I have that isn't working.

models.py:

class ObjectModerationState(models.Model):
    job = models.ForeignKey(JobDescription)
    object_id = models.TextField(help_text="Primary key of the model under moderation.")
    content_type = models.ForeignKey(ContentType, help_text="Content type of the model under moderation.")
    object = generic.GenericForeignKey()

class StateTransisition(models.Model):
    state = models.ForeignKey(State)
    moderation_details = models.ForeignKey("ObjectModerationState", related_name='states')
    changed_by = models.ForeignKey("auth.User", related_name='+', null=False,
                                   help_text="If you create the task, then usually this will be you.")
    date_updated = models.DateTimeField(_("Date Set"))
    last_modified = models.DateTimeField(auto_now=True)


    def __unicode__(self):
        return "%s in state %s by %s" % (self.moderation_details.object, self.state, self.changed_by)

    class Meta:
        ordering = ['last_modified', ]
        get_latest_by = 'last_modified'

class State(Orderable):
    label = models.CharField(_("State Label"), max_length=100, unique=True)
    description = models.TextField(_("Description"), max_length=200, null=True, blank=True,
                                   help_text=_("Explination of this state in context"))
    valid_transitions = models.ManyToManyField("State", null=True, blank=True,
                                               help_text=_("You must add these mappings."))

    def __unicode__(self):
        return "%s" % (self.label)

    class Meta:
        abstract = False

admin.py

class CurrentStateFilter(SimpleListFilter):
    title = 'current state'
    parameter_name = 'current state'

    def lookups(self, request, model_admin):
        states = State.objects.all()
        return  [(c.id, c) for c in states]

    def queryset(self, request, queryset):
        if self.value():
            queryset.filter(states__state__id=self.value()) <<< ????
        else:
            return queryset

EDIT

• I believe the problem is similar to this which it seems can't be done, if the solution is correct. complex annotate on django query set

Answer 1

Caching is definitely a good way to go for this case. For the sake of completeness, however, I wanted to show that I've been able to do the same thing with the following Django ORM query:

EventGroup.objects.annotate(
    latest_event_date=Max('events__timestamp')
).filter(
    events__timestamp=F('latest_event_date'),
    events__state=EventState.STATE_PROCESSED,
)

So, for each EventGroup, latest_event_date is annotated to be the maximum value of timestamp for the events of that group. Furthermore, I filter to include only the EventGroups whose latest event has a state of EventState.STATE_PROCESSED.

I'm fairly certain something like this would work in your case.