Sqlalchemy: joinedload + limit

Question

Sqlalchemy: joinedload + limit

6

以下是需要翻译的内容：

针对以下语句：

p = db.query(Profile).options(joinedload('*')).filter_by(id=p.id).limit(1).one()

我会得到一个子查询 + 一个连接，而不是一个“纯粹”的连接：

SELECT [...] 
FROM (SELECT profile.id AS profile_id, ...
FROM profile 
WHERE profile.id = %(id_1)s 
LIMIT %(param_1)s) AS anon_1 LEFT OUTER JOIN city AS city_1 ON city_1.id = anon_1.profile_city LEFT OUTER JOIN country AS country_1 ON country_1.id = city_1.country LEFT OUTER JOIN state AS state_1 ON country_1.id = state_1.country LEFT OUTER JOIN state AS state_2 ON state_2.id = city_1.state LEFT OUTER JOIN country AS country_2 ON country_2.id = state_2.country LEFT OUTER JOIN state AS state_3 ON state_3.id = city_1.state LEFT OUTER JOIN country AS country_3 ON country_3.id = state_3.country LEFT OUTER JOIN starred AS starred_1 ON anon_1.profile_id = starred_1.star LEFT OUTER JOIN profiletext AS profiletext_1 ON anon_1.profile_id = profiletext_1.profile LEFT OUTER JOIN starred AS starred_2 ON anon_1.profile_id = starred_2.idprofile LEFT OUTER JOIN photo AS photo_1 ON anon_1.profile_id = photo_1.profile LEFT OUTER JOIN gps AS gps_1 ON anon_1.profile_id = gps_1.idprofile

但我真正需要的是：

SELECT ...
FROM profile LEFT OUTER JOIN city AS city_1 ON city_1.id = profile.city LEFT OUTER JOIN country AS country_1 ON country_1.id = city_1.country LEFT OUTER JOIN state AS state_1 ON country_1.id = state_1.country LEFT OUTER JOIN state AS state_2 ON state_2.id = city_1.state     
LEFT OUTER JOIN country AS country_2 ON country_2.id = state_2.country LEFT OUTER JOIN state AS state_3 ON state_3.id = city_1.state LEFT OUTER JOIN country AS country_3 ON country_3.id = state_3.country LEFT OUTER JOIN starred AS starred_1 ON profile.id = starred_1.star LEFT OUTER JOIN profiletext AS profiletext_1 ON profile.id = profiletext_1.profile LEFT OUTER JOIN starred AS starred_2 ON profile.id = starred_2.idprofile LEFT OUTER JOIN photo AS photo_1 ON profile.id = photo_1.profile LEFT OUTER JOIN gps AS gps_1 
ON profile.id = gps_1.idprofile                                                                                                                                                                                                    
WHERE profile.id = 4 
limit 1;

即不使用子查询。

数据库：PostgreSQL 9.2

- jbastos

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Ben · Accepted Answer

根据The Zen of Eager Loading，这似乎是预期的行为。

使用连接式的急加载时，如果查询包含影响到外部连接返回行数的修饰符（例如DISTINCT、LIMIT、OFFSET或等效语句），则完成的语句首先被包装在子查询中，并且专门用于连接式急加载的连接应用于子查询。SQLAlchemy的连接式急加载不仅做到了这一点，而且还走了十英里远，以确保它绝对不会影响查询结果，只会影响集合和相关对象的加载方式，无论查询的格式如何。

我知道这是一个旧问题，但生成的SQL不起作用，有原因吗？