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.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base, declared_attr
import re
class Base(object):
@declared_attr
def __tablename__(cls):
name = cls.__name__
return (
name[0].lower() +
re.sub(r'([A-Z])', lambda m: "_" + m.group(0).lower(), name[1:])
)
id = Column(Integer, primary_key=True)
Base = declarative_base(cls=Base)
class ImportedClientShare(Base):
deal_id = Column(Integer, ForeignKey('deal.id'), nullable=False)
client_id = Column(Integer, ForeignKey('client.id'), nullable=False)
class ImportedPartnerShare(Base):
deal_id = Column(Integer, ForeignKey('deal.id'), nullable=False)
partner_id = Column(Integer)
class Deal(Base):
external_id = Column(Integer)
class PartnerShare(Base):
deal_id = Column(Integer, ForeignKey('deal.id'), nullable=False)
partner_id = Column(Integer)
class ClientShare(Base):
deal_id = Column(Integer, ForeignKey('deal.id'), nullable=False)
client_id = Column(Integer, ForeignKey('client.id'), nullable=False)
class Client(Base):
type = Column(String(50))
external_id = Column(Integer)
__mapper_args__ = {
'polymorphic_on': type,
'polymorphic_identity': 'client'
}
class PersonalClient(Client):
id = Column(Integer, ForeignKey('client.id'), primary_key=True)
data_id = Column(Integer, ForeignKey('personal_data.id'), nullable=False)
data = relationship('PersonalData', uselist=False)
__mapper_args__ = {
'polymorphic_identity': 'personal'
}
class CorporateClient(Client):
id = Column(Integer, ForeignKey('client.id'), primary_key=True)
data_id = Column(Integer, ForeignKey('corporate_data.id'), nullable=False)
data = relationship('CorporateData', uselist=False)
__mapper_args__ = {
'polymorphic_identity': 'corporate'
}
class PersonalData(Base):
address_id = Column(Integer, ForeignKey('address.id'), nullable=False)
address = relationship('Address', uselist=False)
class CorporateData(Base):
address_id = Column(Integer, ForeignKey('address.id'), nullable=False)
address = relationship('Address', uselist=False)
class Address(Base):
pass
engine = create_engine("postgresql://lada:heslo@localhost:5433/sandbox", echo='debug')
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
session = Session(engine)
partner_id = 610566
session.add_all([
Deal(id=1),
Deal(id=2, external_id=10),
Address(id=1),
Address(id=2),
PersonalData(id=1, address_id=1),
CorporateData(id=1, address_id=2),
PersonalClient(id=1, data_id=1),
CorporateClient(id=2, data_id=1),
])
session.flush()
session.add_all([
ClientShare(id=1, deal_id=1, client_id=1),
ImportedClientShare(id=1, deal_id=2, client_id=1),
PartnerShare(id=1, deal_id=1, partner_id=partner_id),
ImportedPartnerShare(id=1, deal_id=2, partner_id=partner_id),
])
session.commit()
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,
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,
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)
#q = q.options(
# joinedload_all(PersonalClient.data, PersonalData.address),
# joinedload_all(CorporateClient.data, CorporateData.address),
#)
q = q.options(
subqueryload_all(PersonalClient.data, PersonalData.address),
subqueryload_all(CorporateClient.data, CorporateData.address),
)
r = q.all()
print "XXXXXXXXXXX"
print r[0].data.address
