Hello again, Michael. Just a follow-up to my previous post. The following code does not work as I would like:
> q = session.query(Client, PersonalClient) > q = q.join(PersonalClient, Client.id == PersonalClient.id) > if eager: > q = q.options( > joinedload_all(PersonalClient.data, PersonalData.address), > ) It generates inner subqueries for joins which I don't want. The following does not work either: q = session.query(Client).with_polymorphic([PersonalClient]) It generates outer joins instead of inner joins. BUT the following DOES the right thing and is by far the simplest and most elegant: q = session.query(PersonalClient) # I can reference Client columns via PersonalClient (e.g. PersonalClient.inherited_column) in filter and such. I wasted almost all day to figure this. I have no idea why I haven't tried this (much) sooner. I guess I was stuck on with_polymorphic() and haven't expected that PersonalClient would magically join on Client as well. Thank you, Ladislav Lenart On 28.2.2013 11:48, Ladislav Lenart wrote: > Hello. > > I have new insigths / new bug to report. > > Even when I reformat the query like this: > > q = session.query(Client, PersonalClient, CorporateClient) > q = q.outerjoin(PersonalClient, Client.id == PersonalClient.id) > q = q.outerjoin(CorporateClient, Client.id == PersonalClient.id) > if eager: > q = q.options( > joinedload_all(PersonalClient.data, PersonalData.address), > joinedload_all(CorporateClient.data, CorporateData.address), > ) > > joinedload_all / subqueryload_all still do not work. > > The only thing that works is to separate the query into two completely > independet ones: > > q = session.query(Client, PersonalClient) > q = q.join(PersonalClient, Client.id == PersonalClient.id) > if eager: > q = q.options( > joinedload_all(PersonalClient.data, PersonalData.address), > ) > > This will also be my solution for the foreseeable future. > > > Thank you, > > Ladislav Lenart > > > On 28.2.2013 11:29, Ladislav Lenart wrote: >> Hello again. >> >> I have successfully installed SA 0.7.10 and the query as-is works. However >> jonedload_all / subqueryload_all options on the query do NOT work. I have >> slightly extended your cte_example which now demonstrates the issues. >> joinedload_all does not crash but there is undesired sql activity after the >> main >> query which renders the joinedload useless. subqueryload_all crashes. >> >> I would be glad for any words of advice or idea(s) for possible >> workaround(s), >> >> Ladislav Lenart >> >> >> On 28.2.2013 10:26, Ladislav Lenart wrote: >>> 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.