prefetch_related for multiple Levels
Solution 1
No, you cannot use
select_related
for a reverse relation.select_related
does a SQL join, so a single record in the main queryset needs to reference exactly one in the related table (ForeignKey
orOneToOne
fields).prefetch_related
actually does a totally separate second query, caches the results, then "joins" it into the queryset in python. So it is needed forManyToMany
or reverseForeignKey
fields.Have you tried two underscores to do the multi level prefetches? Like this:
Publisher.objects.all().prefetch_related('book_set', 'book_set__page_set')
Solution 2
Since Django 1.7, instances of django.db.models.Prefetch
class can be used as an argument of .prefetch_related
. Prefetch
object constructor has a queryset
argument that allows to specify nested multiple levels prefetches like that:
Project.objects.filter(
is_main_section=True
).select_related(
'project_group'
).prefetch_related(
Prefetch(
'project_group__project_set',
queryset=Project.objects.prefetch_related(
Prefetch(
'projectmember_set',
to_attr='projectmember_list'
)
),
to_attr='project_list'
)
)
It is stored into attributes with _list
suffix because I use ListQuerySet
to process prefetch results (filter / order).
![Alex Rothberg](https://i.stack.imgur.com/Z37mC.jpg?s=256&g=1)
Comments
-
Alex Rothberg over 3 years
If my Models look like:
class Publisher(models.Model): pass class Book(models.Model): publisher = models.ForeignKey(Publisher) class Page(models.Model): book = models.ForeignKey(Book)
and I would like to get the queryset for
Publisher
I doPublisher.object.all()
. If then want to make sure to prefetch I can do:Publisher.objects.all().prefetch_related('book_set')`
My questions are:
- Is there a way to do this prefetching using
select_related
or must I useprefetch_related
? - Is there a way to prefetch the
page_set
? This does not work:
Publisher.objects.all().prefetch_related('book_set', 'book_set_page_set')
- Is there a way to do this prefetching using
-
Alex Rothberg over 9 years1. If
Page
has aOneToOne
field toTextContent
, would it be:...prefetch_related('book_set__page_set__text_contents')
or...select_related('book_set__page_set__text_contents')
-
jproffitt over 9 yearsI believe it would be the second version.
-
Mohammed Shareef C over 5 yearsThis is a really brilliant answer and lets you save a lot on db traffic. I don't understand why this answer didn't get much attention
-
Dmitriy Sintsov about 4 years
-
Manish Shah almost 3 yearsHi @DmitriySintsov Can I please get your help here:- stackoverflow.com/questions/68682472/…
-
dKen about 2 years-1. The solution here uses unknown models and an unknown structure. It would be much easier to understand if you used the models that OP provided, or explain the structure of your project example. Very happy to undo the -1 if we get that clarity.