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?
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.