I am working on a simple note taking application. There are Items which are just short pieces of text with a creation date. And there are Connections that link two Items together.
The twist is that Connections are themselves Items, so, they have a text and creation date and can be connected with each other as well as with plain Items. This functionality is modelled by defining the class Connection as a subclass of Item. Connection has (among other things) two attributes of type Item: left and right. Unfortunately, it turns out that mapping these classes on the database is too tricky for me. I hope that someone can help me with this. I am using sqlalchemy version 1.3.1 and an SQLight back end, version 3.26.0. First, I tried Joined Table Inheritance: class Item(Base): __tablename__ = 'items' id = Column(Integer, primary_key=True) type = Column(String(20)) creation_date = Column(Date, nullable=False) contents = Column(String(500), nullable=False) __mapper_args__ = { 'polymorphic_identity':'items', 'polymorphic_on':type } class Connection(Item): __tablename__ = 'connections' id = Column(Integer, ForeignKey('items.id'), primary_key=True) left_id = Column(Integer, ForeignKey('items.id')) left = relationship('Item', foreign_keys=[left_id]) right_id = Column(Integer, ForeignKey('items.id')) right = relationship('Item', foreign_keys=[right_id]) __mapper_args__ = {'polymorphic_identity':'connections'} There are multiple foreign keys from Connection to Item. I resolved the conflicts for left and right by specifying foreign_keys in the relationship but there is no way to do that for the id which is used for the class hierarchy join. Running this code yields the error: Can't determine join between 'items' and 'connections'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly. I have no idea how to do that. I also tried Single Table Inheritance, to avoid the hierarchy foreign key: class Item(Base): __tablename__ = 'items' id = Column(Integer, primary_key=True) type = Column(String(20)) creation_date = Column(Date, nullable=False) contents = Column(String(500), nullable=False) __mapper_args__ = { 'polymorphic_identity':'items', 'polymorphic_on':type } class Connection(Item): left_id = Column(Integer, ForeignKey('items.id')) left = relationship('Item', foreign_keys=[left_id]) right_id = Column(Integer, ForeignKey('items.id')) right = relationship('Item', foreign_keys=[right_id]) __mapper_args__ = {'polymorphic_identity':'connections'} Alas, this also results in an error: Incompatible collection type: Item is not list-like. I do not understand this at all, as there are no back_populates from Connection to Item, so why should it be list like? Please, help me! Thank you very much, Huub -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.