I am trying to create an annotation in a django queryset. The annotation is a Count of reverse foreign key based on a condition. The issue I am having is that when I do the count for one reverse foreign key with a condition I get the right data, but when I do two annotations, one for each reverse foreign key.
Here is the queryset with Count annotation for one reverse foreign key:
ExamResponse.objects.filter(
course_class__course=course,
exam__exam_type=Exam.PRACTICE,
user__is_demo=False,
ended__isnull=False,
id=125752
).order_by(
'user_id',
'started'
).annotate(
total_ecq_count=Sum(
Case(
When(
choice_questions__response_time__gte=ENGAGEMENT_THRESHOLD,
choice_questions__id__isnull=False,
then=1
),
default=0,
output_field=IntegerField()
),
distinct=True
),
).values('total_ecq_count')
Results (Correct Result):
<QuerySet [{'total_ecq_count': 1}]>
The query with two Count annotations
ExamResponse.objects.filter(
course_class__course=course,
exam__exam_type=Exam.PRACTICE,
user__is_demo=False,
ended__isnull=False,
id=125752
).order_by(
'user_id',
'started'
).annotate(
total_ecq_count=Sum(
Case(
When(
choice_questions__response_time__gte=ENGAGEMENT_THRESHOLD,
choice_questions__id__isnull=False,
then=1
),
default=0,
output_field=IntegerField()
),
distinct=True
),
total_etq_count=Sum(
Case(
When(
text_questions__response_time__gte=ENGAGEMENT_THRESHOLD,
text_questions__id__isnull=False,
then=1
),
default=0,
output_field=IntegerField()
),
distinct=True
),
).values('total_ecq_count', 'total_etq_count')
Results: (The total_ecq_count went from 1 to 3!!!)
<QuerySet [{'total_ecq_count': 3, 'total_etq_count': 4}]>
