Django prefetch_related with m2m through relations

Question

I have the following models

class Film(models.Model):
    crew = models.ManyToManyField('Person', through='Role', blank=True)

class Role(models.Model):
    person = models.ForeignKey('Person')
    film = models.ForeignKey('Film')
    person_role = models.ForeignKey(RoleType)
    credit = models.CharField(max_length=200)
    credited_as = models.CharField(max_length=100)

class RoleType(models.Model):
    """Actor, director, makeup artist..."""
    name = models.CharField(max_length=50)

class Person(models.Model):
    slug = models.SlugField(max_length=30, unique=True, null=True)
    full_name = models.CharField(max_length=255)

A Film("Star Wars: The Clone Wars") has several Person("Christopher Lee"), each one of them can have one or more Role("Voice of Count Dooku") and every Role has a RoleType("Voice actor").

I'm using a DetailView to display the Film

class FilmDetail(DetailView):
    model = Film

In my template i'm showing all the Persons, so each time I show a Film 609 queries are being executed. To reduce this I want to use prefetch_related so I changed the view to:

class FilmDetail(DetailView):
    model = Film

    def get_queryset(self):
        return super(FilmDetail, self).get_queryset().prefetch_related('crew')

But this didn't reduce the number of queries(610), I tried the following parameters to prefetch related and it didn't work:

def get_queryset(self):
        return super(FilmDetail, self).get_queryset().prefetch_related('crew__person_role')

I got an Cannot find 'person_role' on Person object, 'crew__person_role' is an invalid parameter to prefetch_related()error

What can I do to prefetch the Person.full_name and slug and all Role fields from Film.crew?

Answer 1

You can construct your queryset like this:

from django.db.models import Prefetch

def get_queryset(self):
    return super(FilmDetail, self).get_queryset().prefetch_related(
        Prefetch(
            'crew',
            queryset=Role.objects.select_related(
                'person',
                'person_role',
            ),
        ),
    )

Only Film->Role is a backwards relation loadable with prefetch_related. Role->RoleType and Role->Person are forwards relations that you load with select_related.