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 <[email protected]> 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 [email protected]. >> To unsubscribe from this group, send email to >> [email protected]. >> 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 [email protected]. > To unsubscribe from this group, send email to > [email protected]. > 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 [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
