52

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 do Publisher.object.all(). If then want to make sure to prefetch I can do:

Publisher.objects.all().prefetch_related('book_set')`

My questions are:

  1. Is there a way to do this prefetching using select_related or must I use prefetch_related?
  2. Is there a way to prefetch the page_set? This does not work:

Publisher.objects.all().prefetch_related('book_set', 'book_set_page_set')

Alex Rothberg
  • 10,243
  • 13
  • 60
  • 120

2 Answers2

72

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

Dmitriy Sintsov
  • 3,821
  • 32
  • 20
  • 7
    This 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 – Mohammed Shareef C Dec 10 '18 at 21:05
  • See https://github.com/Dmitri-Sintsov/django-jinja-knockout/search?q=ListQuerySet&unscoped_q=ListQuerySet – Dmitriy Sintsov Jun 28 '20 at 14:34
  • Hi @DmitriySintsov Can I please get your help here:- https://stackoverflow.com/questions/68682472/api-endpoint-for-django-group-model-generating-excess-number-of-queries – Manish Shah Aug 06 '21 at 16:11
  • 7
    -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. – dKen Apr 21 '22 at 07:20
62

  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 or OneToOne 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 for ManyToMany or reverse ForeignKey fields.

  2. Have you tried two underscores to do the multi level prefetches? Like this: Publisher.objects.all().prefetch_related('book_set', 'book_set__page_set')

jproffitt
  • 6,225
  • 30
  • 42
  • 1
    1. If `Page` has a `OneToOne` field to `TextContent`, would it be: `...prefetch_related('book_set__page_set__text_contents')` or `...select_related('book_set__page_set__text_contents')` – Alex Rothberg Nov 25 '14 at 02:20
  • 1
    I believe it would be the second version. – jproffitt Nov 25 '14 at 02:25