On 11/19/2015 02:38 PM, Bernhard Radermacher wrote: > I am stumped... > > I have the following scenario: Two classes (Project and Package) inherit > from class Container. Project is at the top-level of the hierarchy, and > can contain Packages, Package must be contained in a Project or a > different Package (i.e. a Container) and can contain other packages. The > picture shows the tables and relationships for implementing this as > Joined Table Inheritance. > > <https://lh3.googleusercontent.com/-Y-UfphSM1hg/Vk4hYRwMa5I/AAAAAAAAAOM/546cwcbzI84/s1600/Diagram.png> > > > The tables can be created by SQL without any issues: > > CREATE TABLE `Container` ( > `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, > `name` varchar(64) NOT NULL, > `description` text, > `containerType` varchar(8) NOT NULL > ); > > > CREATE TABLE `Package` ( > `id` int(11) NOT NULL PRIMARY KEY, > `container_id` int(11) NOT NULL, > FOREIGN KEY (`id`) REFERENCES `Container` (`id`), > FOREIGN KEY (`container_id`) REFERENCES `Container` (`id`) > ); > > > CREATE TABLE IF NOT EXISTS `Project` ( > `id` int(11) NOT NULL PRIMARY KEY, > FOREIGN KEY (`id`) REFERENCES `Container` (`id`) > ); > > > I am using the following definitions in Python: > > from sqlalchemy import ( > Column, > ForeignKey, > Integer, > Unicode, > UnicodeText, > ) > > from sqlalchemy.ext.declarative import ( > declarative_base, > ) > > from sqlalchemy.orm import ( > relationship, > scoped_session, > sessionmaker, > ) > > from zope.sqlalchemy import ZopeTransactionExtension > > DBSession = > scoped_session(sessionmaker(extension=ZopeTransactionExtension())) > > Base = declarative_base() > > class Container(Base): > ''' > The are two different kind of containers, projects and packages. > > Projects are the toplevel container, they cannot be at any lower > level (they can contain, but not be contained). > > Packages can be contained in a project, or in a different package. > > The Container itself is abstract. > ''' > id = Column(Integer, primary_key=True, nullable=False) > name = Column(Unicode(64), nullable=False) > description = Column(UnicodeText, nullable=True) > containerType = Column(Unicode(8), nullable=False) > > __mapper_args__ = dict( > polymorphic_on='containerType', > ) > > class Project(Container): > ''' > Projects are the toplevel container, they cannot be at any lower > level (they can contain, but not be contained. > ''' > id = Column(Integer, ForeignKey('Container.id'), primary_key=True, > nullable=False) > __mapper_args__ = dict( > polymorphic_identity='Project', > ) > > > class Package(Container): > ''' > Packages must be contained and can contain. > ''' > id = Column(Integer, ForeignKey('Container.id'), primary_key=True, > nullable=False) > container_id = Column(Integer, ForeignKey('Container.id'), > nullable=False) > ident = relationship('Container', foreign_keys=[id]) > container = relationship('Container', foreign_keys=[container_id], > backref='packages') > > __mapper_args__ = dict( > polymorphic_identity='Package', > ) > > I also tried to replace the two respective lines in Package with: > > ident = relationship('Container', > primaryjoin="Package.id==Container.id") > container = relationship('Container', > primaryjoin="Package.container_id==Container.id", backref='packages') > > Both result in the same error: > > sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between > 'Container' and 'Package'; tables have more than one foreign key > constraint relationship between them. Please specify the 'onclause' of > this join explicitly. > > Where and how can I specify the missing 'onclause'?
this refers to the inheritance condition of Package subclassing Container. You need to use the inherit_condition argument: http://docs.sqlalchemy.org/en/rel_1_0/orm/mapping_api.html?highlight=inherit_condition#sqlalchemy.orm.mapper.params.inherit_condition the docs are slight here but the format is like that of the relationship, just not a string: class Package(Container): # ... id = Column(ForeignKey('container.id')) # ... __mapper_args__ = {'polymorphic_identity': 'Package', 'inherit_condition': Container.id == id} Note the use of "id" as a local variable within the "class" block. > > -- > 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.
