Django prefetch_related nested models

Question

I want to do a 3 lvl prefetch_related but I can't make it work.

views.py:

queryset = obj.answer.all().prefetch_related(Prefetch('question ', queryset=Question.objects.prefetch_related(Prefetch('orderedquestion', queryset=OrderedQuestion.objects.prefetch_related('select_set')))))

return AnswerSerializer(queryset, many=True).data

And on my Serializers.py I call it like that:

json['name'] = answer.question.orderedquestion.select_set.get(id=i).name

I don't know if this metters but my OrderedQuestion class inherits from Question.

My Models.py

class Answer(models.Model):
    updated_datetime = models.DateTimeField(default=timezone.now)
    profile = models.ForeignKey(Profile, related_name='answer', on_delete=models.CASCADE)
    question = models.ForeignKey(Question, related_name='answer', on_delete=models.CASCADE)
    order = JSONField(blank=True, null=True)

class Question(models.Model):
    objects = InheritanceManager()
    title_question = models.CharField(max_length=256, unique=True)
    title_interest = models.CharField(max_length=256)
    description_question = models.CharField(max_length=2048, null=True, blank=True)
    description_interest = models.CharField(max_length=2048, null=True, blank=True)
    category = models.ForeignKey(Category, on_delete=models.SET_NULL, null=True, blank=True)
    
class OrderedQuestion(Question):
    multiple_choice = models.BooleanField(default=False)
    dont_care = models.BooleanField(default=False)

class Select(models.Model):
    name = models.CharField(max_length=1024)
    choice_question = models.ForeignKey(ChoiceQuestion, on_delete=models.CASCADE, null=True, blank=True)
    ordered_question = models.ForeignKey(OrderedQuestion, on_delete=models.CASCADE, null=True, blank=True)

    def __str__(self):
        return str(self.id) + ' - ' + self.name

How can I fetch select_set on the first query so my serializer stop doing N queries for each object?

Thank you so much for the help.

Answer 1

I think your code should work. I wrote year ago similar solution for real state agency and it looks like that:

OfferType.objects.prefetch_related(
    Prefetch('offer_type_biurowin', queryset=OfferTypeBiurowin.objects.prefetch_related(
        Prefetch('offers', queryset=Offer.objects.select_related('locality').prefetch_related(
            Prefetch('gallery', to_attr='gallery_list')), to_attr='offer_list')
), to_attr='biurowin_list')

Debugging with django-debug-toolbar package is showing that this code does only 3 queries. The 3 queries are must, because this is way how SQL works - it contains 3 different tables, it can't fetch data from multiple tables using one query, but it's probably better than 100+ queries without using .prefetch_related() or .select_related().

You can check if your code works using mentioned django-debug-toolbar or by using django-silk.

@EDIT: Sorry, I was dumb - just noticed that you are using this line:

json['name'] = answer.question.orderedquestion.select_set.get(id=i).name

You can't fetch question by using direct-attribute call, you must do something like this:

answer.question_set.first().orderedquestion_set.first().select_set_set.get(id=i).name

The many-to-many and reverse-fk relations contains MULTIPLE objects. You can't fetch just single question without using .first() or calling first element by:

answer.question_list[0]

(If you use to_attr attribute in prefetch).

Also, if you use direct models.ForeignKey call, you should use select_related(), not prefetch_related().