Basically the problem I have: I need an option or alternative approach to filter on annotated fields on union queryset.
I have the following simplified models setup:
class Course(Model): groups = ManyToManyField(through=CourseAssignment) class CourseAssignment(Model): course = ForeignKey(Course) group = ForeignKey(Group) teacher = ForeignKey(Teacher) class Lesson(Model): course = ForeignKey(Course, related_name='lessons') class AssignmentProgress(Model): lesson = ForeignKey(related_name='progresses') course_assignment = ForeignKey(CourseAssignment) student = ForeignKey(Student) group = ForeignKey(Group) status = CharField(choices=( ('on_check', 'On check'), ('complete', 'Complete'), ('assigned', 'Assigned'), )) deadline = DateTimeField() checked_date = DateTimeField()
I need to display a statistics on assignment progresses grouped by lessons and groups for which courses assigned. Here is a my initial queryset, note that lessons are repeated in final result, the difference is in annotated data:
def annotated_lessons_queryset(): lessons = None for course_assignment in CourseAssignment.objects.all(): qs = Lesson.objects.filter( course=course_assignment.course ).annotate( completed_progresses=Count( 'progresses', filter=Q(group=course_assignment.group), output_field=IntegerField() ), on_check=Exists( AssignmentProgress.objects.filter( lesson=OuterRef('id'), group=course_assignment.group, status='on_check' ) ) ) lessons = qs if lessons is None else lessons.union(qs) return lessons
I canon use | OR operator here, because it returns only distinct lesson values.
So far this works until I try filter all the lessons with annotated status on_check:
qs = annotated_lessons_queryset().filter(on_check=True)
Which fails with the error:
raise NotSupportedError( django.db.utils.NotSupportedError: Calling QuerySet.filter() after union() is not supported.
Please, suggest a workaround or another approach to make this queryset filtered.