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.