I recently started working with SQL Alchemy for a project that involves climbing areas and routes. Areas are hierarchical in that a single area may contain multiple areas, which in turn may contain other areas. A route is directly associated with a single area, but is also associated with that area's parent, etc.
To implement this I chose to use a closure table ala Bill Karwin (http://karwin.blogspot.com/2010/03/rendering-trees-with-closure- tables.html). In the closure table implementation, a second table is created to store the ancestor/descendent information. A self- referencing row is created when a node is added, as well as a row for each ancestor in the tree. The table structure is as follows (simplified): -- area -- area_id name -- area_relationship -- ancestor descendent -- route -- route_id area_id name Sample data: -- area -- 1, New River Gorge 2, Kaymoor 3, South Nuttall 4, Meadow River Gorge -- area_relationship (ancestor, descendent) -- 1, 1 (self-referencing) 2, 2 (self-referencing) 1, 2 (Kaymoor is w/i New River Gorge) 3, 3 (self-referencing) 1, 3 (South Nutall is w/i New River Gorge) 4, 4 (self-referencing) -- route (route_id, area_id, name) 1, 2, Leave it to Jesus 2, 2, Green Piece 3, 4, Fancy Pants To query for all areas for a given route (up the tree), I can execute: SELECT area.area_id, area.name FROM route INNER JOIN area_relationship ON route.area_id = area_relationship.descendent INNER JOIN area ON area.area_id = area_relationship.ancestor WHERE route.route_id = 1 Similarly, I can query for all routes in a particular area (including descendent areas) with: SELECT route.route_id, route.name FROM area INNER JOIN area_relationship ON area.area_id = area_relationship.ancestor INNER JOIN route ON route.area_id = area_relationship.descendent WHERE area.area_id = 1 In SQL Alchemy I've created a relationship and two tables to handle these relationships: area_relationship_table = Table('area_relationship', Base.metadata, Column('ancestor', Integer, ForeignKey('area.area_id')), Column('descendent', Integer, ForeignKey('area.area_id')) ) DbArea class - class DbArea(Base): __tablename__ = 'area' area_id = Column(Integer, primary_key = True) name = Column(VARCHAR(50)) created = Column(DATETIME) area_relationship_table.c.ancestor]) descendents = relationship('DbArea', backref = 'ancestors', secondary = area_relationship_table, primaryjoin = area_id == area_relationship_table.c.ancestor, secondaryjoin = area_id == area_relationship_table.c.descendent) DbRoute class - class DbRoute(Base): __tablename__ = 'route' route_id = Column(Integer, primary_key = True) area_id = Column(Integer, ForeignKey('area.area_id')) name = Column(VARCHAR(50)) created = Column(DATETIME) area = relationship("DbArea") areas = relationship('DbArea', backref = 'routes', secondary = area_relationship_table, primaryjoin = area_id == area_relationship_table.c.ancestor, secondaryjoin = area_id == area_relationship_table.c.descendent, foreign_keys=[area_relationship_table.c.ancestor, area_relationship_table.c.descendent]) Currently, I am able to determine the areas from the individual route, using the areas relationship in DbRoute. However, when I try to use the backref 'routes' in DbArea, I get the following error: sqlalchemy.exc.StatementError: No column route.area_id is configured on mapper Mapper|DbArea|area... (original cause: UnmappedColumnError: No column route.area_id is configured on mapper Mapper|DbArea|area...) 'SELECT route.route_id AS route_route_id, route.area_id AS route_area_id, route.name AS route_name, route.created AS route_created \nFROM route, area_relationship \nWHERE %s = area_relationship.descendent AND route.area_id = area_relationship.ancestor' [immutabledict({})] I'm guessing that I likely need to add something to DbArea to establish the relationship, but after experimenting with some different options was unable to determine the solution. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
