Django select_related on chained foreign keys

Question

I read the documentation and all the related questions here but I haven't properly understood how select_related behaves on chained/multiple foreign keys.

Assume we have the following models:

class RecordLabel(models.Model):
   title = models.CharField(...)

class Band(models.Model):
   band_name = models.CharField(...)

class Artist(models.Model):
   record_label = models.ForeignKey(RecordLabel,...)
   belongs_to_band = models.ForeignKey(Band, ...)

class Producer(models.Model):
   customer = models.ForeignKey(Artist, ...)

A. How would we use select_related in a

Producer.objects.filter(...).select_related(?)

query so that everything is preloaded? Would it be like:

Producer.objects.filter(...).select_related(
    'customer__record_label', 'customer__belongs_to_band')

and why?

B. If class Band had 'Genre' as a foreign key,

 class Genre(models.Model):
       genre_name = models.CharField(...)        

 class Band(models.Model):
       band_name = models.CharField(...)
       music_genre = models.ForeignKey(Genres, ...)

then in order to have everything preloaded we would do something like this:

Producer.objects.filter(...).select_related(
    'customer__record_label__music_genre', 'customer__record_label__band_name',
    'customer__belongs_to_band__music_genre', 'customer__belongs_to_band__music_genre') 

or something like this:

Producer.objects.filter(...).select_related(
    'customer__record_label__music_genre', 'customer__record_label__band_name',
    'customer__belongs_to_band', 'customer__belongs_to_band') 

Answer 1

As to question B:

If I understand your question correctly, you need to specify the fields only once, no need for duplication.

Note: I display the final version of your models here again, because otherwise I get confused.

class RecordLabel(models.Model):
    title = models.CharField(...)

class Genre(models.Model):
    genre_name = models.CharField(...)

class Band(models.Model):
    band_name = models.CharField(...)
    music_genre = models.ForeignKey(Genre, ...)

class Artist(models.Model):
    record_label = models.ForeignKey(RecordLabel, ...)
    belongs_to_band = models.ForeignKey(Band, ...)

class Producer(models.Model):
    customer = models.ForeignKey(Artist, ...)

To select all related models (make one big join SQL query):

qs = Producer.objects.filter(...).select_related(
    'customer__record_label',
    'customer__belongs_to_band__music_genre')

The first part (customer) pre-fetches the related artist and the related record label (__record_label); the second part does not need to fetch the artist because it already is there, but it goes on to prefetch the related band (__belongs_to_band) and then also the related genre (__music_genre). Now you have a SQL query that accesses all 5 tables (models).

Hint: you can use qs.query to see a basic idea of the SQL statement that your query will generate; that should give you an idea of the joins it makes.

If you're having problems with the query, then you should add more information on what exactly is happening and what you are expecting.