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 <[email protected]>
>>>> wrote:
>>>>
>>>>>
>>>>> On Feb 27, 2013, at 3:12 PM, Ladislav Lenart <[email protected]> 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 [email protected].
>>>>> To post to this group, send email to [email protected].
>>>>> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
