Optimize django query with Exists, Subquery and OuterRef

user_9312327 0
Viewed 985

I need help optimize this django query . The code and use case is as follows:

My code:

  user_enrolments = UserEnrolment.objects.filter(
            enrolment__enrollable__id__in=course_ids_taught_by_teacher,
            course_progresses__module_progresses__module=instance).only("id").annotate(
            submitted_attempt=Exists(
                AssignmentModuleProgress.objects
                .annotate(user=OuterRef('id'))
                .filter(
                    module_progress=Subquery(
                    ModuleProgress.objects.get(module=instance, user=OuterRef('user'))),
                    is_submitted=True)
            ))
        return user_enrolments.filter(submitted_attempt=True).count()

course_progresses - > Many to Many to UserEnrolment, module_progress - > Many to Many to CourseProgress and instance is current AssignmentModule in serializer iteration.

I want to get the submitted users count in the serializer , so im using a serializer method field.

To get the submitted users count, I want to check if an entry exists in AssignmentModuleProgress table with module_progress = ModuleProgress.objects.get(module=instance, user=OuterRef('user')) User is a field in UserEnrolments Table and user should refer to current user in the user_enrolments annotation iteration. Im returning the count after filtering True condition of is_submitted.

As of now im getting the following error:

ValueError: This queryset contains a reference to an outer query and may only be used in a subquery.
django-rest-framework django-queryset django-models django
1 Answers

This might be a solution without using a Subquery:

  user_enrolments = UserEnrolment.objects.filter(
            enrolment__enrollable__id__in=course_ids_taught_by_teacher,
            course_progresses__module_progresses__module=instance).only("id").annotate(
            submitted_attempt=Exists(
                AssignmentModuleProgress.objects
                .annotate(user=OuterRef('pk'))
                .filter(
                    Q(module_progress__module=instance) &
                    Q(module_progress__user=OuterRef('user')),
                    is_submitted=True)
            ))
        return user_enrolments.filter(submitted_attempt=True).count()
user_93123270
Related