Thank you very much for your valuable time, Michael! Your example code seems correct. The only differences I found are: * Missing FK on ImportedPartnerShare.partner_id: class ImportedPartnerShare(Base): deal_id = Column(Integer, ForeignKey('deal.id')) partner_id = Column(Integer, ForeignKey('partner.id')) # ForeignKey() is missing in yout example script * All FKs in the example should have nullable=False and ondelete='CASCADE'.
But I suppose none of this makes any difference. As you wrote and confirmed, this issue (and many others) was resolved in SA 0.7.9. I have just verified that I am using SA 0.7.8 at the moment (version from debian distro). I apologize for the err subject. I did not check it when I wrote that line, I just assumed. Thank you again, Ladislav Lenart On 28.2.2013 04:12, Michael Bayer wrote: > OK, I've reconstructed mappings which correspond directly to your Query as > given, and it produces the identical SQL. I've inserted a bunch of rows into > all the tables so that a polymorphic result comes back, so that we can in > fact verify that the ORM reads the "client_id" column correctly. > > Your issue exists from SQLAlchemy 0.7.8 and backwards, and was fixed as of > 0.7.9, (we're up to 0.7.10 as well as 0.8 betas). 0.7.9 had many bug fixes > for the CTE feature as it had only been introduced in 0.7.6. > > Script is attached. > > > On Feb 27, 2013, at 4:11 PM, Michael Bayer <mike...@zzzcomputing.com> wrote: > >> >> On Feb 27, 2013, at 3:12 PM, Ladislav Lenart <lenart...@volny.cz> wrote: >> >>> Hello. >>> >>> Thank you for your prompt answer. I will try to create a working example >>> that >>> demonstrates the issue. Though it will take me a couple of days, maybe >>> weeks (my >>> regular work followed by a vacation). >>> >>> I have another problem. I rephrased the SQL, because postgres's planner had >>> issues with EXISTS and thought it is a great idea to perform full scans of >>> several huge tables in order to return several hundreds result rows. Enter >>> CTEs... >>> >>> Short-short intro: >>> * Client is the joined table inheritance root. >>> * PersonalClient and CorporateClient are its subclasses. >>> * Partner is a salesman. >>> * Deal is a contract signed between salesman(s) and client(s). >>> * ImportedClientShare and ClientShare are M:N relationships between clients >>> and >>> deals. >>> * ImportedPartnerShare and PartnerShare are M:N relationships between >>> partners >>> and deals. >>> * We import deals from an external DB. Those are called imported. Imported >>> deal >>> has external_id and Imported*Share apply. >>> * However, a user of our system (a partner) can create a new deal locally. >>> Such >>> a deal does not have an external id (yet) and local *Share apply to it. >>> >>> The following code should return all clients of a given partner via >>> ImportedClientShare or via ClientShare: >>> >>> q1 = session.query(ImportedClientShare.client_id.label('client_id')) >>> q1 = q1.join(ImportedPartnerShare, ImportedClientShare.deal_id == >>> ImportedPartnerShare.deal_id) >>> q1 = q1.join(Deal, ImportedClientShare.deal_id == Deal.id) >>> q1 = q1.filter( >>> ImportedPartnerShare.partner_id == partner_id, # input argument >>> Deal.external_id != None, >>> ) >>> q2 = session.query(ClientShare.client_id.label('client_id')) >>> q2 = q2.join(PartnerShare, ClientShare.deal_id == >>> PartnerShare.deal_id) >>> q2 = q2.join(Deal, ClientShare.deal_id == Deal.id) >>> q2 = q2.filter( >>> PartnerShare.partner_id == partner_id, # input argument >>> Deal.external_id == None, >>> ) >>> client_ids = q1.union(q2).cte('client_ids') >>> q = session.query(Client).with_polymorphic([PersonalClient, >>> CorporateClient]) >>> q = q.join(client_ids, Client.id == client_ids.c.client_id) >>> >>> NoSuchColumnError: "Could not locate column in row for column 'client.id'" >>> >>> I also tried it without with_polymorphic() and the result is the same. >>> >>> Do you have any idea what is going on / what am I doing wrong and how I can >>> fix >>> this? >> >> Again, this is a very complex query, even more complex than the previous >> one, and it boils down to limits in what the ORM can currently handle. It's >> probably a bug, though there may be workarounds that allow it to work, >> however it's the kind of issue that typically takes me many hours to >> diagnose and fix or at least work around, given code that I can run and pdb >> in order to debug. This is not something you'd have much luck resolving on >> your own unless you wanted to become deeply familiar with SQLAlchemy >> internals. >> >> I would recommend again making sure all these issues remain in the latest >> 0.8 version and attempting to produce a rudimentary series of test classes >> which I can run in order to reproduce your results. I can reconstitute >> these models looking just at your queries, though sometimes after all that >> effort the issue isn't reproduced, due to some quirk in the mappings that's >> also required. >> >> -- >> 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 sqlalchemy+unsubscr...@googlegroups.com. >> To post to this group, send email to sqlalchemy@googlegroups.com. >> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. >> For more options, visit https://groups.google.com/groups/opt_out. -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.