get count of a Subquery

Issue

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.

Solution

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,
)

Answered By – Mohit Harshan

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0