the test script below fails to reproduce this issue. Can you provide a fully
reproducing test script as well as version information /DB backend ?
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base= declarative_base()
class Resource(Base):
__tablename__ = "resources"
id = Column("id", Integer, primary_key=True, key="id")
type = Column(String)
parent_id = Column(Integer, ForeignKey('parent.id'))
__mapper_args__ = {'polymorphic_on':type}
class BasePrAc(Resource):
__tablename__ = "base_products_accessories"
id = Column("id", Integer, ForeignKey("resources.id"), primary_key=True)
__mapper_args__ = {'polymorphic_identity':'bpa'}
class Parent(Base):
__tablename__ = "parent"
id = Column("id", Integer, primary_key=True)
resources = relationship(Resource)
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add_all([
Parent(
resources=[
BasePrAc(),
BasePrAc(),
]
)
])
print s.query(Parent, BasePrAc).join(BasePrAc).all()
output:
CREATE TABLE parent (
id INTEGER NOT NULL,
PRIMARY KEY (id)
)
2012-05-03 16:25:28,155 INFO sqlalchemy.engine.base.Engine ()
2012-05-03 16:25:28,155 INFO sqlalchemy.engine.base.Engine COMMIT
2012-05-03 16:25:28,155 INFO sqlalchemy.engine.base.Engine
CREATE TABLE resources (
id INTEGER NOT NULL,
type VARCHAR,
parent_id INTEGER,
PRIMARY KEY (id),
FOREIGN KEY(parent_id) REFERENCES parent (id)
)
2012-05-03 16:25:28,155 INFO sqlalchemy.engine.base.Engine ()
2012-05-03 16:25:28,156 INFO sqlalchemy.engine.base.Engine COMMIT
2012-05-03 16:25:28,156 INFO sqlalchemy.engine.base.Engine
CREATE TABLE base_products_accessories (
id INTEGER NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(id) REFERENCES resources (id)
)
2012-05-03 16:25:28,156 INFO sqlalchemy.engine.base.Engine ()
2012-05-03 16:25:28,156 INFO sqlalchemy.engine.base.Engine COMMIT
2012-05-03 16:25:28,160 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2012-05-03 16:25:28,160 INFO sqlalchemy.engine.base.Engine INSERT INTO parent
DEFAULT VALUES
2012-05-03 16:25:28,161 INFO sqlalchemy.engine.base.Engine ()
2012-05-03 16:25:28,161 INFO sqlalchemy.engine.base.Engine INSERT INTO
resources (type, parent_id) VALUES (?, ?)
2012-05-03 16:25:28,162 INFO sqlalchemy.engine.base.Engine ('bpa', 1)
2012-05-03 16:25:28,162 INFO sqlalchemy.engine.base.Engine INSERT INTO
resources (type, parent_id) VALUES (?, ?)
2012-05-03 16:25:28,162 INFO sqlalchemy.engine.base.Engine ('bpa', 1)
2012-05-03 16:25:28,162 INFO sqlalchemy.engine.base.Engine INSERT INTO
base_products_accessories (id) VALUES (?)
2012-05-03 16:25:28,162 INFO sqlalchemy.engine.base.Engine ((1,), (2,))
2012-05-03 16:25:28,163 INFO sqlalchemy.engine.base.Engine SELECT parent.id AS
parent_id, anon_1.base_products_accessories_id AS
anon_1_base_products_accessories_id, anon_1.resources_id AS
anon_1_resources_id, anon_1.resources_type AS anon_1_resources_type,
anon_1.resources_parent_id AS anon_1_resources_parent_id
FROM parent JOIN (SELECT resources.id AS resources_id, resources.type AS
resources_type, resources.parent_id AS resources_parent_id,
base_products_accessories.id AS base_products_accessories_id
FROM resources JOIN base_products_accessories ON resources.id =
base_products_accessories.id) AS anon_1 ON parent.id =
anon_1.resources_parent_id
2012-05-03 16:25:28,164 INFO sqlalchemy.engine.base.Engine ()
[(<__main__.Parent object at 0x1014fddd0>, <__main__.BasePrAc object at
0x101604390>), (<__main__.Parent object at 0x1014fddd0>, <__main__.BasePrAc
object at 0x101604490>)]
On May 3, 2012, at 4:05 PM, Wubin wrote:
> Hi, I created a class "Resource", and its subclass "BasePrAc" as
> below:
>
> class Resource(BaseClass.BaseClass):
> __tablename__ = "resources"
> id = Column("id", Integer, primary_key=True, key="id")
>
> class BasePrAc(Resource.Resource):
> __tablename__ = "base_products_accessories"
> id = Column("id", Integer, ForeignKey("resources.id"),
> primary_key=True)
>
>
> And when I try to "query.join" BasePrAc or its subquery, it always
> gives me the OperationalError(1060, "Duplicate column name 'id'"). I
> checked the generated SQL statement, and found the subquery part shown
> as below:
>
> "..... INNER JOIN (SELECT resources.id AS id,
> base_products_accessories.id AS id,
> base_products_accessories.polymorphic_identity AS
> polymorphic_identity, ...."
>
> As you see, there are two "AS id" in the statement and causing the
> error. I was wondering why it asks "resource.id" which I didn't
> specify, and it would know that BasePrAc.id is equal to Resource.id
> since BasePrAc class is the subclass of Resource class. Some suggested
> to use "alias" for joining, and I tried but it didn't work. Besides, I
> am hoping not to use alias everytime, so joining things could be more
> generic.
>
> I will appreciate if you have any advise for this situation.
>
> Thank you very much, and have a nice day.
> W
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
