On 7/20/15 10:49 AM, Douglas Russell wrote:
Apologies for cross-posting this, but I realised that this might be a
better place to get an answer than this Stackoverflow question
<http://stackoverflow.com/questions/31393824/polymorphic-self-referential-foreign-key-in-sqlalchemy>
that I asked a week ago.
I am trying to resolve a problem with a self-referential table with
Joined Table Inheritance where there is a foreign key linking the
inheritance relationships, but then also a case where a class has an
additional reference to an instance of its parent. Best to go with a
simplified example:
Class B inherits from Class A. Class B is linked to Class A by the id
column through the Foreign Key in Class B. Class B also has a column
(|a_id|) which references Class A which has nothing to do with the
inheritance.
|fromsqlalchemy importColumn,Integer,ForeignKey,create_engine
fromsqlalchemy.ext.declarative importdeclarative_base
fromsqlalchemy.orm importsessionmaker,relationship,backref
Base=declarative_base()classA(Base):__tablename__ ='a'satype
=Column(String(50))__mapper_args__
={'polymorphic_identity':'a','polymorphic_on':satype }id
=Column(Integer,primary_key=True)classB(A):__tablename__ ='b'id
=Column(Integer,ForeignKey('a.id'),primary_key=True)__mapper_args__
={'polymorphic_identity':'b'}a_id =Column(Integer,ForeignKey('a.id'))a
=relationship('A',backref='b')engine
=create_engine('sqlite:///:memory:',echo=True)Base.metadata.create_all(engine)Session=sessionmaker(bind=engine)session
=Session()|
As per the documentation
<http://docs.sqlalchemy.org/en/latest/orm/relationship_persistence.html#rows-that-point-to-themselves-mutually-dependent-rows>,
I would resolve the case where there are multiple ForeignKeys between
tables by explicitly specifying in the relationship which was to be used.
the "mutually dependent foreign keys" document doesn't really apply to
this case. What happens here is that B(A) requires a join from B to
A, and then B.a requires a different one. Even though the conventions
here make it clear which foreign key constraint is which, the mapper
still needs them to be explicitly spelled out, that's like this:
class B(A):
__tablename__ = 'b'
id = Column(Integer, ForeignKey('a.id'), primary_key=True)
__mapper_args__ = {
'polymorphic_identity': 'b',
'inherit_condition': id == A.id
}
a_id = Column(Integer, ForeignKey('a.id'))
a = relationship(
'A',
backref='b', primaryjoin=A.id == a_id, remote_side=A.id)
|classB(A):__tablename__ ='b'id
=Column(Integer,ForeignKey('a.id'),primary_key=True)__mapper_args__
={'polymorphic_identity':'b'}a_id =Column(Integer,ForeignKey('a.id'))#
I know the primaryjoin is no longer needed in SA >= 0.8a
=relationship('A',backref='b',foreign_keys=[a_id],primaryjoin=a_id==A.id)|
I think the problem is that I don't seem to be able to figure out how
to do the same for the polymorphic column |id| as I am not explicitly
defining that relationship.
Cheers,
Douglas
--
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
For more options, visit https://groups.google.com/d/optout.
