On 21 Mar 2013, at 20:56, Jose Neto <[email protected]> wrote: > Given the following statement: > > p = > db.query(Profile).options(joinedload('*')).filter_by(id=p.id).limit(1).one() > I will get a subquery + a join, instead of a "pure" join: > > 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 > but what I really need is: > > 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; > i.e. no subquery. > > SQLAlchemy==0.8.0 postgresql 9.2,
I think you want contains_eager: http://docs.sqlalchemy.org/en/rel_0_8/orm/loading.html#contains-eager ie. you explicitly join to the tables you are interested in, then tell SA that the rows already contain those relationships. Something like: p = db.query(Profile).join(City).options(contains_eager('city')).filter_by(id=p.id).limit(1).one() I think you need to spell out each join individually - I don't think there's a way to say 'join to all relationships' Hope that helps, Simon -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
