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.
