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.
>
>
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'))
client_id = Column(Integer, ForeignKey('client.id'))
class ImportedPartnerShare(Base):
deal_id = Column(Integer, ForeignKey('deal.id'))
partner_id = Column(Integer)
class Deal(Base):
external_id = Column(Integer)
class PartnerShare(Base):
deal_id = Column(Integer, ForeignKey('deal.id'))
partner_id = Column(Integer)
class ClientShare(Base):
deal_id = Column(Integer, ForeignKey('deal.id'))
client_id = Column(Integer, ForeignKey('client.id'))
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)
__mapper_args__ = {
'polymorphic_identity': 'personal'
}
class CorporateClient(Client):
id = Column(Integer, ForeignKey('client.id'), primary_key=True)
data_id = Column(Integer)
__mapper_args__ = {
'polymorphic_identity': 'corporate'
}
engine = create_engine("postgresql://scott:tiger@localhost/test", 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),
PersonalClient(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)
print q.all()--
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.
