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

What do you mean by `If class Band had 2 foreign keys A, B ` ? Please clarify — Ralf, Sep 19 '18 at 16:35

Ralf · Accepted Answer · 2018-09-20T11:47:42.557

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.

Thanks. I have 1 question though. In the documentation, if I got it correctly, it says that select_related performs a sql join under the hood. Having that in mind I would expect "customer__record_label__music_genre" to be different from "customer__record_label__band_name". Or is it that they are indeed different, but it doesn't matter since what we want is just to preload the tables? — Fotis Sk, Sep 20 '18 at 08:44
@FotisSk sorry, I got confused by the models and made a mistake. I updated my answer with (what I think is) the correct version. — Ralf, Sep 20 '18 at 11:48

Ralf · Answer 2 · 2018-09-19T16:58:39.807

1

For question A: pass the arguments as strings

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

EDIT: seems like the missing quotes were just a typo of the asker, not the real question.

edited Sep 19 '18 at 16:58

answered Sep 19 '18 at 16:34

Ralf

16,086
4
44
68

A. Oh my bad, I forgot to include the quotes in my post. Can you explain a bit why this is the correct answer? B. I will fix it. – Fotis Sk Sep 19 '18 at 16:43
I'm not sure what is unclear: the docs show to use quotes for the parameters to `select_related()`. – Ralf Sep 19 '18 at 16:47
The quotes were not my problem, sorry If my answer was unclear. I just forgot to include them in my post. In the examples I run in Django I included them properly. I just want to know how the select_related behaves in this chained ForeignKey situation. – Fotis Sk Sep 19 '18 at 16:57

Django select_related on chained foreign keys

2 Answers2