Hi Diana & Michael Bayer, you both have exactly the same results/sql queries, but unfortunately my results are different. This is really strange, here is my complete python script with my output: http://pastebin.com/CyCs90cW
In my case the lazy='subquery' parameter have no effect and the sql queries differs from yours. Am Donnerstag, 22. November 2012 16:00:41 UTC+1 schrieb Michael Bayer: > > what is awkward about this mapping is that the "myChildren" relationship > is established twice, specific to the Men and Woman subclasses. SQLAlchemy > doesn't have any with_polymorphic instruction here when it loads Human > objects, so when you say Human->(some attribute that is only on Men/Woman) > it has no choice but to load the additional columns local to Men and Woman, > then loads the myChildren relationship. The instruction to load "Human" > objects with an eagerload to "myChildren" is a meaningless instruction - > "Human" *does not have* a "myChildren" relationship. When you load Human > objects, the fact that some may be Men and some may be Woman is not known > at query time - it is only known after the rows are loaded, and it's too > late to make decisions about eager loads on attributes specific to those > subclasses. > > As far we query(Woman).one(), if the Woman object is already present in > the Session then its attributes are not refreshed and the subqueryload for > "myChildren" won't emit. > > If I load "Woman" with a clean session, the SQL is as expected: > > session.close() > woman = session.query(Woman).one() > print woman.myChildren > > > > SELECT human.type AS human_type, human.id AS human_id, woman.id_human AS > woman_id_human > FROM human JOIN woman ON human.id = woman.id_human > 2012-11-22 09:58:46,474 INFO sqlalchemy.engine.base.Engine () > 2012-11-22 09:58:46,475 INFO sqlalchemy.engine.base.Engine SELECT > anon_1.human_type AS anon_1_human_type, anon_1.human_id AS anon_1_human_id, > anon_1.child_id_human AS anon_1_child_id_human, anon_1.child_age AS > anon_1_child_age, anon_2.woman_id_human AS anon_2_woman_id_human > FROM (SELECT woman.id_human AS woman_id_human > FROM human JOIN woman ON human.id = woman.id_human) AS anon_2 JOIN > woman_child AS woman_child_1 ON anon_2.woman_id_human = > woman_child_1.woman_id JOIN (SELECT human.type AS human_type, human.id AS > human_id, child.id_human AS child_id_human, child.age AS child_age > FROM human JOIN child ON human.id = child.id_human) AS anon_1 ON > anon_1.child_id_human = woman_child_1.child_id ORDER BY > anon_2.woman_id_human > > > > > On Nov 22, 2012, at 9:49 AM, Diana Clarke wrote: > > > Hmm.... maybe I'm missing something. Perhaps someone else can jump in, > > and show me what I'm missing? > > > > When I take your code, and execute the following 5 cases: > > > > CASE A: no 'subquery' on relationship, 'subqueryload' on query > > CASE B: 'subquery' on relationship, no 'subqueryload' on query > > CASE C: no 'joined' on relationship, 'joinedload' on query > > CASE D: 'joined' on relationship, no 'joinedload' on query > > CASE E: no 'joined' or 'subquery' on relationship, no 'joinedload' > > or 'subqueryload' on query > > > > 1) The queries in cases A & B are equal (exactly 2 queries each) > > 2) The queries in cases C & D are equal (exactly 1 query each) > > 3) Case E is completely lazy, there are exactly 1 + N queries > > > > Here are the notes I kept, as I tested those cases: > > > > http://pastebin.com/hx0Kj4An > > > > Martin: perhaps create a new pastebin that shows exactly what you're > > doing (including table/engine/session creation, data population, etc), > > so that I have a better chance of seeing what you're seeing? > > > > http://pastebin.com/mcum0c7Q > > > > --diana > > > > On Thu, Nov 22, 2012 at 6:09 AM, Martin84 > > <stek...@googlemail.com<javascript:>> > wrote: > >> Hi Diana, > >> > >> thank you for your help, but unfortunately my problem still exists. > >> In my case the lazy attribute for the myChildren relationship has > absolutely > >> no effect! > >> At this point one more information is important: I build and populate > the > >> database with one session, and then open a new session and call > >> showDatabase. > >> If you populate the database with one session, and then call > showDatabase > >> with the same (now not empty) session, > >> then the instances are already in the session and sqlalchemy don't fire > new > >> queries for access to the myChildren attribute of human. > >> So, it is important to call showDatabase with an empty session to > reproduce > >> my issue. > >> > >> One more information could be important: > >> If I load a woman instance and access myChildren with: > >> > >> woman = session.query(Woman).one() > >> print woman.myChildren > >> > >> then sqlalchemy emits an extra sql query on the access to myChildren > and > >> ignore my lazy='subquery' or lazy='joined' parameter for the mychildren > >> relationship configuration. > >> But if I modify the query like this: > >> > >> woman = session.query(Woman).options(subqueryload('myChildren')).one() > >> print woman.myChildren > >> > >> then sqlalchemy load the woman and all children at once! Exactly what I > >> need. > >> But this makes completely no sense to me, i thought that subqueryload() > just > >> overload the load strategie for a relationship. > >> So myChildren = relationship('Child', secondary=link_table, > lazy='subquery') > >> and subqueryload('myChildren') should be equivalent. > >> Why is there a difference? > >> > >> > >> > >> > >> > >> > >> > >> -- > >> You received this message because you are subscribed to the Google > Groups > >> "sqlalchemy" group. > >> To view this discussion on the web visit > >> https://groups.google.com/d/msg/sqlalchemy/-/GponYdm2PLsJ. > >> > >> To post to this group, send email to > >> sqlal...@googlegroups.com<javascript:>. > > >> To unsubscribe from this group, send email to > >> sqlalchemy+...@googlegroups.com <javascript:>. > >> For more options, visit this group at > >> http://groups.google.com/group/sqlalchemy?hl=en. > > > > -- > > You received this message because you are subscribed to the Google > Groups "sqlalchemy" group. > > To post to this group, send email to > > sqlal...@googlegroups.com<javascript:>. > > > To unsubscribe from this group, send email to > sqlalchemy+...@googlegroups.com <javascript:>. > > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > > > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/ftxSziH3hD4J. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
