Django ORM - select_related and order_by with foreign keys
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.
Related videos on Youtube
Rob Crowell
Updated on April 17, 2022Comments
-
Rob Crowell about 2 years
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
andRelease
have anArtist
. WhileSong.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
orSong.Track.Release.Artist.name
. Here's my code inside a view, which is sorting bySong.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 usetblname.colname
. I took a look at the underlying query object'sas_sql
method, which indicates that when the inner join is made to getSong.Track.Release.Artist
the temporary nameT6
is used for theArtist
table since an inner join was already done on this same table to getSong.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?