use inherit_condition: import sqlalchemy from sqlalchemy import create_engine from sqlalchemy import String, Column, Integer, ForeignKey from sqlalchemy.orm import relation, backref from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('mysql://scott:ti...@localhost/test', echo=True) Base = declarative_base() # B is the superclas of S class B(Base): __tablename__ = 'b' id = Column(Integer, primary_key=True) type = Column(String(1)) __mapper_args__ = {'polymorphic_on': type, 'polymorphic_identity':'b'} __table_args__ = {'mysql_engine':'InnoDB'} # S is a subclass of B and also references an object of the B hierarchy via a foreign key class S(B): __tablename__ = 's' id = Column(Integer, ForeignKey('b.id'), primary_key=True) b_id = Column(Integer, ForeignKey('b.id')) b = relation('B', backref='s_collection', primaryjoin='S.b_id==B.id') __table_args__ = {'mysql_engine':'InnoDB'} __mapper_args__ = {'polymorphic_identity': 's', 'inherit_condition':id==B.id} Base.metadata.create_all(engine) On May 23, 2009, at 9:51 AM, sven-eric wrote: > > Dear Group, > > I have a problem with setting up self referential relations within a > joined table inheritance scheme and declarative mapping. Let's say I > have a base class B with a derived class S. S has a self-referential > many-to-one relationship to B (and with that also to all of B's > derived classes). The declarative definition seems to compile fine if > a database system without native support for foreign keys is used > (like sqlite), but breaks down with systems like InnoDB on mysql where > foreign keys are supported natively (in the latter case I get a > "tables have more than one foreign key constraint relationship between > them. Please specify the 'onclause' of this join explicitly" error). > > So, the following testcase works on sqlite but fails if I move to SQL > and InnoDB (after setting SQL server in the engine declaration and the > InnoDB table in the __table_args__ of each class). Could anyone give > me advice for setting up this kind of relation on InnoDB? Thanks a > lot. > > -sven-eric > > ---------------- > > import sqlalchemy > from sqlalchemy import create_engine > from sqlalchemy import String, Column, Integer, ForeignKey, > ForeignKeyConstraint > from sqlalchemy.orm import relation, backref > from sqlalchemy.ext.declarative import declarative_base > > engine = create_engine('sqlite:///:memory:', echo=False) > Base = declarative_base() > > # B is the superclas of S > class B(Base): > __tablename__ = 'b' > id = Column(Integer, primary_key=True) > type = Column(String(1)) > __mapper_args__ = {'polymorphic_on': type, 'polymorphic_identity': > 'b'} > > # S is a subclass of B and also references an object of the B > hierarchy via a foreign key > class S(B): > __tablename__ = 's' > __table_args__ = (ForeignKeyConstraint(['b_id'], ['b.id'])) > __mapper_args__ = {'polymorphic_identity': 's'} > id = Column(Integer, ForeignKey('b.id'), primary_key=True) > b_id = Column(Integer) > b = relation('B', backref='s_collection', primaryjoin='S.b_id==B.id') > > Base.metadata.create_all(engine) > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---