Django ORM - select_related 和 order_by 与外键

Question

我有一个简单的音乐模式:Artist、Release、Track 和 Song。前 3 个都是逻辑结构，而第四个 (Song) 是 (Artist, Release, Track) 作为 mp3、wav、ogg 等的特定实例。

我在生成数据库中歌曲的有序列表时遇到问题。问题在于两者 Track和 Release有一个 Artist .虽然 Song.Track.Artist始终是表演者姓名，Song.Track.Release.Artist可以是表演者姓名或“各种艺术家”以进行编辑。我希望能够按其中一个进行排序，但我无法找出使这项工作正常进行的正确方法。

这是我的架构:

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)

我的查询应该相当简单；过滤特定用户拥有的所有歌曲，然后按 Song.Track.Artist.name 对它们进行排序。或 Song.Track.Release.Artist.name .这是我在 View 中的代码，按 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')

我无法获取 order_by除非我使用 tblname.colname 才能工作.我看了一下底层查询对象的 as_sql方法，表示进行内连接时获取 Song.Track.Release.Artist临时名称 T6用于 Artist表，因为已经在同一张表上完成了内部连接以获得 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,))

当我把它作为表名放在 order_by 中时它确实有效(参见上面的示例输出)，但这似乎完全不可移植。当然有更好的方法来做到这一点!我错过了什么？

Answer 1

恐怕我真的无法理解你的问题是什么。

一些更正:select_related与排序无关(它根本不改变查询集，只是跟随连接来获取相关对象并缓存它们)；并按相关模型中的字段排序，您使用双下划线表示法，而不是虚线。例如:

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

但是在您的示例中，您使用了“player_artist”，它似乎不是您模型中任何地方的字段。我不明白你提到的可移植性。