How to JOIN three tables with Django ORM with WHERE clause

Question

I have three models:

class model_A(models.Model):
    data_1 = models.CharField(max_length=60)
    data_2 = models.SmallIntegerField()
    data_3 = models.IntegerField(blank=True, null=True)

class model_B(models.Model):
    data_a = models.ForeignKey(model_A)
    data_1 = models.CharField(max_length=5)
    data_2 = models.IntegerField()

class model_C(models.Model):
    data_a = models.ForeignKey(model_A)
    data_1 = models.CharField(max_length=5)
    data_2 = models.IntegerField()

so as you can see there is a one-to-one relationship between model_B → model_A and model_C → model_A, it's very simple.

I need to make a JOIN of these three tables with a WHERE clause, so with RAW SQL it would be:

SELECT * FROM `model_A` JOIN `model_B` ON `model_A`.`data_1` = `model_B`.`data_a` JOIN `model_C` ON `model_A`.`data_1` = `model_C`.`data_a` WHERE `model_B`.`data_1` = 1 AND `model_C`.`data_1` = 1

How can i make a JOIN of these three tables (using filter statement (WHERE clause)) by using Django ORM?

Possible duplicate? Duplicated question that someone linked has join with TWO tables, which is easy to solve using select_related(). But it doesn't work (or i don't know how to use it in this situation) with three tables.

Admin · Answer

Of course, this isn't exactly a good model definition, so let's fix that first:

from django.db import models

class Artist(models.Model):
    name = models.CharField(max_length=60)
    year_established = models.SmallIntegerField()
    votes = models.IntegerField(blank=True, null=True)


class Song(models.Model):
    artist = models.ForeignKey(Artist, related_name='songs')
    title = models.CharField(max_length=5)
    votes = models.IntegerField()


class Fan(models.Model):
    artist = models.ForeignKey(Artist, related_name='fans')
    name = models.CharField(max_length=5)
    votes_casted = models.IntegerField()

Now let's get all artists who've written a song about love and have fans who have casted at least 100 votes:

queryset = Artist.objects.select_related(
    'songs', 'fans'
).filter(songs__title__icontains='love', fans__votes_casted__gte=100)

Note that select_related doesn't play a part in the query: it's an optimization to minimize queries when iterating the set.

How to JOIN three tables with Django ORM with WHERE clause

2 Answers