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.
