Group django queryset by foreign key / related field

Viewed 4302

Consider two models:

class Question(models.Model):
    ...

class Answer(models.Model):
    question = models.ForeignKey(Question)
    ...

The goal is to group answers by questions. I could think of some ways of spanning the model relatioship from the Question side - retrieve all the questions which have any answers:

Question.objects.annotate(answer_count = Count('answer')) \
                .filter(answer_count__gte=1)

Then we can iterate over this QuerySet and use answer_set to get particular answers.

The problem here is that if the Question table is big enough, this query is very slow. It's even more ugly in a situation when most of the questions don't have any answers and you end up annotating a huge table with zeros.

It would be great to go from the other side of the relationship and somehow group Answer instances by question__id field. The way I'm handling it now is:

answered_questions = [row['question'] for row in 
    Answer.objects.values('question').distinct('question')]
for question in answered_questions:
    answers = Answer.objects.filter(question__id=question)

This is obviously faster but also clumsy. Grouping objects by foreign key should be a pretty common task. Is there a better way to do it?

1 Answers
Related