Django double join with multiple models?-CodePudding

I've spent quite some time on it and still haven't been able to figure out how to join these tables with django.

I have a Movie database with following models:

class Movies(models.Model):
MovieName = models.CharField(db_column='MovieName', primary_key=True, max_length=30)  
MovieRating = models.FloatField(db_column='MovieRating') 
MovieReleaseTime = models.DateTimeField(db_column='MovieReleaseTime')  

class Genre(models.Model):
GenreID = models.IntegerField(db_column='GenreID', primary_key=True, max_length=30)
GenreTitle = models.CharField(db_column='GenreTitle', unique=True, max_length=30)

class MovieGenres(models.Model):
MovieName = models.ForeignKey('Movies', models.DO_NOTHING, db_column='MovieName')
GenreID = models.ForeignKey('Genre', models.DO_NOTHING, db_column='GenreID')

In my PostgreSQL I can pair MovieName with GenreTitle using following query:

SELECT "MovieName", "GenreTitle"
FROM public."Movies"
NATURAL JOIN public."MovieGenres"
NATURAL JOIN public."Genres";

I then get something like this:

  MovieName | GenreTitle 
 -----------|------------ 
  MovieA    | GenreA     
  MovieA    | GenreB     
  MovieB    | GenreA

How can I achieve the same output with Django views?

CodePudding user response：

You can do this using annotate and F objects:

qs = MovieGenres.objects.annotate(
    movie_name=F('MovieName__MovieName'),
    genere_title=F('GenreID__GenreTitle'),
).values_list('movie_name', 'genere_title')

print(qs)

The output will be

[
    ("MovieA", "GenreA"),
    ("MovieA", "GenreB"),
    ("MovieB", "GenreA"),
    # etc... 
]

CodePudding user response：

You can slightly shorten this with:

from django.db.models import F

MovieGenres.objects.values_list(
    movie_name=F('MovieName__MovieName'),
    genere_title=F('GenreID__GenreTitle')
)