a join() or a joinedload to a polymorphic selectable always wraps the right
side in a subquery as the ORM doesn't directly support rendering of an
expression like "a JOIN (b JOIN c ON b.id=c.id) ON a.id=b.id". The expression
language does, but within the ORM it uses the subquery approach when its asked
to join automatically 's that the query works on all platforms. Supporting
having it nest the JOIN directly is http://www.sqlalchemy.org/trac/ticket/2587 .
On Feb 28, 2013, at 1:08 PM, Ladislav Lenart <lenart...@volny.cz> wrote:
> 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.
>
>
--
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.
