Django ORM - select_related and order_by with foreign keys

Question

I have a simple music schema: Artist, Release, Track, and Song. The first 3 are all logical constructs while the fourth (Song) is a specific instance of an (Artist, Release, Track) as an mp3, wav, ogg, whatever.

I am having trouble generating an ordered list of the Songs in the database. The catch is that both Track and Release have an Artist. While Song.Track.Artist is always the performer name, Song.Track.Release.Artist may either be a performer name or "Various Artists" for compilations. I want to be able to sort by one or the other, and I can't figure out the correct way to make this work.

Here's my schema:

class Artist(models.Model):
    name = models.CharField(max_length=512)

class Release(models.Model):
    name = models.CharField(max_length=512)
    artist = models.ForeignKey(Artist)

class Track(models.Model):
    name = models.CharField(max_length=512)
    track_number = models.IntegerField('Position of the track on its release')
    length = models.IntegerField('Length of the song in seconds')
    artist = models.ForeignKey(Artist)
    release = models.ForeignKey(Release)

class Song(models.Model):
    bitrate = models.IntegerField('Bitrate of the song in kbps')
    location = models.CharField('Permanent storage location of the file', max_length=1024)
    owner = models.ForeignKey(User)
    track = models.ForeignKey(Track)

My query should be fairly simple; filter for all songs owned by a specific user, and then sort them by either Song.Track.Artist.name or Song.Track.Release.Artist.name. Here's my code inside a view, which is sorting by Song.Track.Artist.name:

songs = Song.objects.filter(owner=request.user).select_related('track__artist', 'track__release', 'track__release__artist').order_by('player_artist.name')

I can't get order_by to work unless I use tblname.colname. I took a look at the underlying query object's as_sql method, which indicates that when the inner join is made to get Song.Track.Release.Artist the temporary name T6 is used for the Artist table since an inner join was already done on this same table to get Song.Track.Artist:

>>> songs = Song.objects.filter(owner=request.user).select_related('track__artist', 'track__release', 'track__release__artist').order_by('T6.name')
>>> print songs.query.as_sql()
('SELECT "player_song"."id", "player_song"."bitrate", "player_song"."location", 
  "player_song"."owner_id", "player_song"."track_id", "player_track"."id", 
  "player_track"."name", "player_track"."track_number", "player_track"."length", 
  "player_track"."artist_id", "player_track"."release_id", "player_artist"."id", 
  "player_artist"."name", "player_release"."id", "player_release"."name", 
  "player_release"."artist_id", T6."id", T6."name" FROM "player_song" INNER JOIN 
  "player_track" ON ("player_song"."track_id" = "player_track"."id") INNER JOIN 
  "player_artist" ON ("player_track"."artist_id" = "player_artist"."id") INNER JOIN 
  "player_release" ON ("player_track"."release_id" = "player_release"."id") INNER JOIN 
  "player_artist" T6 ON ("player_release"."artist_id" = T6."id") WHERE 
  "player_song"."owner_id" = %s  ORDER BY T6.name ASC', (1,))

When I put this as the table name in order_by it does work (see example output above), but this seems entirely non-portable. Surely there's a better way to do this! What am I missing?

Deerdre

Answer 1

I'm afraid I really can't understand what your question is.

A couple of corrections: select_related has nothing to do with ordering (it doesn't change the queryset at all, just follows joins to get related objects and cache them); and to order by a field in a related model you use the double-underscore notation, not dotted. For example:

Song.objects.filter(owner=request.user).order_by('track__artist__name')

But in your example, you use 'player_artist', which doesn't seem to be a field anywhere in your model. And I don't understand your reference to portability.

Defaulter