[Sqlalchemy-users] self join with parent and children attributes

[Randall Smith]

I have a table with a foreign key to itself. The foreign key references 
it's parent record.  I would like the table mapped to an object that has 
both a parent and children attribute.  I've been going by the treenode 
example in the docs.  I can get either children to work or parent to 
work, but not both.  In other words, if I say parent.append(child), 
child.parent is NULL.  If I say child.parent = parent, parent.children 
is an empty list.

My table def and mapper:

projects = Table('projects', metadata,
     Column('id', INTEGER, primary_key=True),
     Column('title', VARCHAR(200), nullable=False),
     Column('county_code', INTEGER, nullable=False),
     Column('pws_id', INTEGER),
     Column('project_type', VARCHAR(100), PassiveDefault('Standard'),
         nullable=False),
     Column('description', TEXT),
     Column('reviewer_id', INTEGER, nullable=False),
     Column('reviewer_name', VARCHAR(100), nullable=False),
     Column('pro_engineer_id', INTEGER),
     Column('pro_engineer_name', VARCHAR(100)),
     Column('notes', TEXT),
     Column('parent_project_id', INTEGER,
         ForeignKey("%s.projects.id" % schemaname)),
     Column('input_time', TIMESTAMP, timestamp_default, nullable=False),
     schema=schemaname
)

mapper(Project, projects,
     properties={
         'parent_project':relation(Project,
         primaryjoin=projects.c.id==projects.c.parent_project_id,
         foreignkey=projects.c.id),
         'child_projects':relation(Project,
         primaryjoin=projects.c.id==projects.c.parent_project_id,
         foreignkey=projects.c.parent_project_id)
     }
)

Any pointers appreciated.

Randall



_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users