get count of a Subquery

Question

I want to annotate a count field in my queryset with a subquery filter:

My code:

module_attempts = Subquery(
    ModuleProgressAttempt.objects.filter(
        module_progress__module__id=OuterRef("pk")
    ).only("pk")
)
real_instances = VirtualClassRoomModule.objects.filter(
    id__in=[vc.id for vc in vc_classrooms]
).annotate(
    attendees_count=Count(module_attempts),
)

Here module_progress__module__id is the id of the current VirtualClassRoomModule of the annotation iteration. The count is basically the length of the ModuleProgressAttempt filtered queryset. Currently the count is always 1 , eventhough the

ModuleProgressAttempt.objects.filter(
     module_progress__module__id=<Current-module-id>
)

returns more than one object.

score 1 · Accepted Answer · edited Sep 27 '21 at 10:32

This solution worked for me:

module_attempts_count = Subquery(
    ModuleProgressAttempt.objects.filter(module_progress__module__id=OuterRef("id"))
    .values("module_progress__module__id")
    .annotate(cnt=Count("id"))
    .values("cnt")
)
real_instances = VirtualClassRoomModule.objects.filter(
    id__in=[vc.id for vc in vc_classrooms]
).annotate(
    attendees_count=module_attempts_count,
)

get count of a Subquery

1 Answers1