python - 如何使用sqlalchemy orm实现子选择？

Question

我有以下模型:

• 相册:id，名字，...
• 轨道:id, album_id, ...

我需要使用 SQLAlchemy orm 生成下面的查询:

SELECT 
  Album.name,
  (
    SELECT COUNT(*) as total_count
    FROM Track
    WHERE Track.album_id = Album.id
  ) as tracks
FROM
  Album
WHERE
  Album.band = ‘Metallica’ AND
  tracks.total_count > 10

到目前为止的代码:

tracks = session \
  .query(func.count('*').label('total_count')) \
  .select_from(Track) \
  .filter(Track.album_id == Album.id) \
  .subquery()

query = session \
  .query(Album.name, tracks.c.total_count) \
  .filter(Album.band == 'Metallica') \
  .filter(tracks.c.total_count > 10)

生成的查询如下所示:

SELECT
  Album.name,
  anon_1.total_count
FROM
  Album,
  (
    SELECT count('*') AS total_count
    FROM Track, Album
    WHERE Track.album_id = Album.id
  ) AS anon_1
WHERE
  Album.band = ‘Metallica’ AND
  anon_1.total_count > 10

这要慢得多。有什么想法可以将子选择的结果作为根选择的一部分返回吗？谢谢!

Answer 1

您不能按照自己的意愿执行查询，因为您不能在 WHERE 子句谓词中使用选择列表项，因为 WHERE 在 SELECT 之前被求值。在这种情况下，简单的 JOIN、GROUP BY 和 HAVING 就足够了:

query = session \
  .query(Album.name, func.count()) \
  .join(Track) \
  .filter(Album.band == 'Metallica') \
  .group_by(Album.id) \
  .having(func.count() > 10)

上面的查询使用了你应该能够选择 Album.name 的事实，即使它没有在 GROUP BY 中使用，因为它在功能上依赖于 Album.id 。当然，您也可以按名称分组，前提是没有相册共享一个名称。

您最初的尝试很慢，因为它不是相关子查询，而是在专辑和轨道之间的子查询中执行预 SQL-92 样式连接，有效地计算所有轨道，并再次连接专辑和封闭查询中的派生表。