Django Models and SELECT with joined two or more tables?

Question

How can I make an select with two (or more) joined tables using Django Models?

For example:

class Album(models.Model):
    artist = models.ForeignKey(Musician)
    name = models.CharField(max_length=100)
    release_date = models.DateField()
    num_stars = models.IntegerField()

class Song(models.Model):
    album = models.ForeignKey(Album)
    name = models.CharField(max_length=100)
    num_stars = models.IntegerField()

SELECT * from album, song where (album.id = song.album_id) and (album.artist_id = X)

Answer 1

Django querysets want to return lists of model instances, which doesn't quite map to the the SQL notion of returning data from multiple tables joined into a single result table.

To achieve the effect of the SQL query you provide as an example, you could do one of the following:

1) If you want to iterate over your data by Songs, you can limit the songs you query by the artist id, like so:

songs = Song.objects.filter(album__artist__id=123)
for song in songs:
    print song.name
    print song.album.name
    ... do what ever you need to do with song here ...

If you are concerned about performance, you can add select_related() to your queryset.

# the depth argument tells django to only follow foreign key relationships
# from Song one level deep
songs = Song.objects.select_related(depth=1).filter(album__artist__id=123)

for song in songs:
    # django has now already fetched the album data, so accessing it
    # from the song instance does not cause an additional database hit
    print song.album.name

2) If you want to iterate over your data by Album, you can do things like so:

albums = Album.objects.filter(artist__id = 123)
for album in albums:
    for song in album.song_set.all():
        print song.name
        print album.name
        ... etc ...