Complex filtering in Django, how to filter elements by nested outermost elements?

Question

Django

There are three models: Job, WorkItem and DocumentPackage.
The Job has a ForeignKey on the WorkItem and the WorkItem has a ForeignKey on the DocumentPackage.
The goal is to filter out only those DocumentPackages in which all WorkItems have the Job with the highest id state=Job.STATE_COMPLETE

def finished(qs):
    active_jobs = Job.objects.filter(item=OuterRef('pk')).order_by('-id')

    active_items = (
        WorkItem.objects.annotate(
            active_job_state=Subquery(active_jobs.values('state')[:1])
        ).filter(
            active_job_state=Job.STATE_COMPLETE
        )
    )

    qs = qs.prefetch_related(
        Prefetch(
            'work_items',
            queryset=active_items,
            to_attr='active_items',
        )
    ).annotate(
        _active=Count('active_items'),
    ).annotate(
        _total=Count('work_items')
    )

    return qs.filter(_total=F('_active'))

The error occurs when calculating Count('active_items'):

FieldError: Cannot resolve keyword 'active_items' into field.

Models (extra fields removed):

class Job(models.Model):
    STATE_PENDING = 0
    STATE_TODO = 1
    STATE_PAUSED = 2
    STATE_REQUESTED = 3
    STATE_IN_PROGRESS = 4
    STATE_READY = 5
    STATE_COMPLETE = 6
    STATE_RETURNED = 7
    STATE_ABORTED = 8
    STATE_HISTORY = 9

    STATE_CHOICES = Choices(
        (STATE_PENDING, 'Pending'),
        (STATE_TODO, 'ToDo'),
        (STATE_PAUSED, 'Paused'),
        (STATE_REQUESTED, 'Requested'),
        (STATE_IN_PROGRESS, 'InProgress'),
        (STATE_READY, 'Ready'),
        (STATE_COMPLETE, 'Complete'),
        (STATE_RETURNED, 'Returned'),
        (STATE_ABORTED, 'Aborted'),
        (STATE_HISTORY, 'History'),
    )
    # Идентификатор документа
    item = models.ForeignKey('WorkItem', on_delete=models.CASCADE, related_name='jobs')
    # Состояние:
    state = models.IntegerField(default=STATE_IN_PROGRESS, choices=STATE_CHOICES)

class WorkItem(ModelDiffMixin, models.Model):
    # Идентификатор пакета, в который входит данный документ
    package = models.ForeignKey('DocumentPackage', related_name='work_items', on_delete=models.CASCADE)

class DocumentPackage(models.Model):
    # Наименование пакета
    name = models.TextField(null=True, blank=True, default='')

Answer 1

You need to group the WorkItemJob by DocumentPackage, and then get the value of the argmax(id) function - the string with the maximum ID. It is possible to do this through window functions, DjangoORM supports it.

queryset.annotate(
            last_job_status=Window(
                expression=LastValue("workitem__job__status"),
                partition_by=F('workitem__id'),
                order_by=F('workitem__job__id')
            )

).filter(last_job_status=Job.STATE_COMPLETED)

Something like this.