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.

If you're new to Django, forget about SQL. Start thinking in model relations. Solve all problems you can by using descriptive names for models and thinking how data should relate. Then whatever can't be solved or performs badly, you can still do in SQL. — , Sep 20 '17 at 09:18

score 6 · Accepted Answer · edited Sep 16 '21 at 14:22

6

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.

Further reading:

Edit: added select_related. This should theoretically work and reduce queries, but if it doesn't, I'll look into it tomorrow.

edited Sep 16 '21 at 14:22

Dimitrios Mistriotis

2,626
3
28
45

answered Sep 20 '17 at 09:37

You've almost there! This statement gives me next SQL query - `SELECT artist.name, artist.year_established, artist.votes FROM ...` instead of what i need `SELECT * FROM ...` (i need all of the data in my template from all three tables) – Ljubisa Livac Sep 20 '17 at 10:16
No you don't. You can simply access related models. See [here](https://stackoverflow.com/a/46319757/1600649). And select_related prefetches the foreign key data for you, but that doesn't change the fact that you can access them as if you would access related models. – Sep 20 '17 at 10:58
let's take your example in consideration: if i use `{% for author in authors %} {% for song in author.songs.all %}` loops to get related model fields, it makes, for example, **30 queries** for 30 authors! I need `{{ author.name }}`, and `{{ song.title}}` as well, in my template. There is no other way to reach `{{ song.title}}` if i don't put another songs loop inside the authors loop, as far as i know. I tried with `{{ author.songs.all.0.title }}` but that will also make separate query for every author regardless of whether im using `select_related` or `prefetch_related`. – Ljubisa Livac Sep 20 '17 at 16:37
2

I've finally managed to do it! `queryset = Artist.objects.prefetch_related('songs','fans').filter(songs__title__icontains='love', fans__votes_casted__gte=100` did the thing! I can reach every field in template now (with just one trip to database) for example `{{ artist.songs.all.0.title }}`. You've helped me a lot so i would like to accept your suggestion as correct if you edit your answer first with this corrections – Ljubisa Livac Sep 20 '17 at 17:32

NIKHIL RANE · Answer 2 · 2017-09-20T10:57:32.477

1

Try this one

query_set = model_A.objects.filter(modelb__data_1=1, modelc__data_1=1)

For model B data

query_set = model_B.objects.filter(data_1=1, data_a__modelc__data_1=1)

Hope this is helps you

edited Sep 20 '17 at 10:57

answered Sep 20 '17 at 08:35

NIKHIL RANE

4,012
2
22
45

Unfortunately not, because i need table data from model_B and model_C tables. This way i would get only data regarding model_A and it throws error anyway - 'Cannot resolve keyword 'model_b' into field' – Ljubisa Livac Sep 20 '17 at 09:09

How to JOIN three tables with Django ORM with WHERE clause

2 Answers2