Adjacency List Relationship in a Child Class

[Michael Naber]

Whenever I try to establish an adjacency list relationship within a
child class (Department --> Parent Department in this case) the orm
complains about foreign key columns present in both the parent and
child class, and won’t construct the mapping. Below is an example
illustrating the problem. I'd appreciate any insight.

Thanks,
Michael Naber


from sqlalchemy.orm import scoped_session, sessionmaker, relationship,
backref
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String,
ForeignKey, Text, Date

Session = scoped_session(sessionmaker())
Base = declarative_base()

engine = create_engine('sqlite:///data.db')
Session.configure(bind=engine)

class Node(Base):
__tablename__ = 'node'
id = Column(Integer, primary_key=True)
name = Column(String(100))
discriminator = Column('discriminator', String(50))


class Department(Node):
__tablename__ = 'department'
__mapper_args__ = {'polymorphic_identity': 'department'}
id = Column(Integer, ForeignKey('node.id'), primary_key=True)
description = Column(Text)
parent_department_id = Column(Integer,
ForeignKey('department.id'))
parent_department = relationship("Department",

primaryjoin="Department.parent_department_id==Department.id",

foreign_keys="Department.parent_department_id",

backref=backref("subdepartments"), remote_side="Department.id")

Base.metadata.drop_all(checkfirst=True, bind=Session.bind)
Base.metadata.create_all(bind=Session.bind)

s = Session()

d = Department(name='Great Department', description='some text')
s.add(d)
s.commit()

for dept in s.query(Department).all():
print dept.id
print dept.name

[Michael Naber]

I'm still having trouble getting an adjacency list relationship to
work within a child class (Department class in this example), and am
hoping someone might offer some insight:

class Node(Base):
__tablename__ = 'node'
id = Column(Integer, primary_key=True)
name = Column(String(100))
discriminator = Column('discriminator', String(50))

class Department(Node):
__tablename__ = 'department'
__mapper_args__ = {'polymorphic_identity': 'department'}
id = Column(Integer, ForeignKey('node.id'), primary_key=True)
description = Column(Text)
parent_department_id = Column(Integer,
ForeignKey('department.id'))
parent_department = relationship("Department",

backref=backref("subdepartments"),
remote_side=id)

Thanks,
Michael Naber

[Michael Bayer]

yeah always send us the error message with these since otherwise we have to replicate it , as this mapping looks entirely fine. it asks for a primaryjoin, and additionally the "id" column, until 0.7.0 is released where this has been fixed, is in terms of Node not Department so you have to rename it:

class Department(Node):
__tablename__ = 'department'
__mapper_args__ = {'polymorphic_identity': 'department'}

dept_id = Column("id", Integer, ForeignKey('node.id'), primary_key=True)
description = Column(Text)
parent_department_id = Column(Integer,ForeignKey('department.id'))

parent_department = relationship("Department", backref=backref("subdepartments"),

primaryjoin=dept_id==parent_department_id,
remote_side=dept_id)

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>