Django 1.10.7 ORM annotate query with conditional count over many to many field

Question

Im having a problem using the annnotate function of django to get a conditional count from a many to many field.

I have these 3 models:

class Answer(models.Model):
    """
    This model represents an answer to an open question. It just contains
    the answer text and the question it answers.
    """
    answer_description = models.CharField(max_length=1500, blank=True, null=True)
    question = models.ForeignKey(Question)
    instrument_submission = models.ForeignKey(InstrumentSubmission)

class MultipleChoiceAnswer(Answer):
    choices = models.ManyToManyField(QuestionChoice)

class QuestionChoice(models.Model):
    name = models.CharField(max_length=300)
    question = models.ForeignKey(MultipleChoiceQuestion)
    explanation_needed = models.BooleanField(default=False)
    is_correct = models.BooleanField(default=False)
    ordinal = models.IntegerField(blank=True, null=True)
    select_all = models.BooleanField(default=False)
    no_answer = models.BooleanField(default=False)

What I want to do is get all the MultipleChoiceAnswers with the total count of the choices field, and an additional count for the correct choices (the ones that have the attribute is_correct=True)

As an example, I have one MultipleChoiceAnswer that has 2 choices related. One with is_correct=True, the other one with is_correct=False.

Then I ran the following test:

In [4]: x=MultipleChoiceAnswer.objects.filter(pk=33420)

In [11]: for ans in x:
    ...:     for c in ans.choices.all():
    ...:         print c.is_correct
    ...:         
True
False

In [7]: x=x.annotate(c=Count('choices'),
   ...: 
   ...: correct=Count('choices',filter=Q(is_correct=True)))

In [8]: for a in x:
   ...:     print a.c
   ...:     print a.correct
   ...:     
2
2

I would expect to see 2 and then a 1. But the Count is not doing what I expect.

Can anyone help me spot the problem?

I'm not sure the filter argument for annotation is available before django 2.0. Either way, the filter expression should be something like this: Q(choices__is_correct). My answer is based on these docs — Yoav Gaudin
– Yoav Gaudin, Commented Apr 1, 2018 at 6:20

solarissmoke · Accepted Answer · 2018-04-01 08:09:43Z

3

The filter argument was added to aggregates in Django 2, so it will do nothing in Django 1.10. You can however achieve the same thing with a more complex query that uses conditional expressions:

from django.db.models import Case, Count, IntegerField, When

ans.annotate(correct=Sum(
    Case(
        When(choices__is_correct=True, then=1),
        default=0,
        output_field=IntegerField(),
    )
))

This basically adds up all the choices which are correct and returns a count of them for each answer.

answered Apr 1, 2018 at 8:09

solarissmoke

31.7k15 gold badges76 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

solarissmoke Over a year ago

(Side note: Django 1.10 is end of life. You should consider upgrading.)

Collectives™ on Stack Overflow

Django 1.10.7 ORM annotate query with conditional count over many to many field

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related