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.

Please add models to the question and also tell us about the end goal you want to achieve. May there is another better way to achieve it. — Umar Hayat
– Umar Hayat, Commented Jul 21, 2022 at 18:54
Thanks guys , I've just added the Models.py on the question. My end goal is to prefetch all data on the views.py - get_queryset() to the serializers.py. The simple ones I manage to do but this nested one is hard. — Lucas Weber
– Lucas Weber, Commented Jul 21, 2022 at 19:23

Patryk Szczepański · Accepted Answer · 2022-07-22 08:14:10Z

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().

edited Jul 22, 2022 at 8:14

answered Jul 22, 2022 at 8:06

Patryk Szczepański

7941 gold badge4 silver badges13 bronze badges

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

1 Comment

Lucas Weber Over a year ago

Thank you @Patryk Acctually the problem was related with the inherited classes This just dont work

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

Because my OrderedQuestion inherits from Question But this works

prefetch_related(Prefetch('question__interest', queryset=Interest.objects.filter(profile=me).prefetch_related(Prefetch('selects', queryset=Select.objects.all(), to_attr='interestselects')), to_attr='myinterest'))

Collectives™ on Stack Overflow

Django prefetch_related nested models

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