[sqlalchemy] Inheritance and self.reference

[Bernhard Radermacher]

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'?

-- 
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.