I recently started working with SQL Alchemy for a project that includes climbing zones and routes. Regions are hierarchical in that one region may contain several regions, which, in turn, may contain other regions. The route is directly connected to one area, but also connected to the parent of this area, etc.
To implement this, I decided to use the ala Bill Karwin closure table. In the implementation of the closure table, a second table is created to store information about the ancestors / descendants. A self-regulatory string is created when a node is added, as well as a string for each ancestor in the tree.
The structure of the table 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 all areas for a given route (up the tree), I can do:
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 specific area (including descendant 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 created a relationship and two tables to handle this relationship:
area_relationship_table = Table('area_relationship', Base.metadata, Column('ancestor', Integer, ForeignKey('area.area_id')), Column('descendent', Integer, ForeignKey('area.area_id')) )
Class DbArea -
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 can define areas from an individual route using area relationships in DbRoute. However, when I try to use backref 'routes' in DbArea, I get the following error:
sqlalchemy.exc.StatementError: No route.area_id column configured on mapper Mapper | DbArea | area ... (original reason: UnmappedColumnError: no route.area_id column configured for 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 suppose I probably need to add something to DbArea in order to establish a relationship, but after experimenting with some different options, I could not determine the solution.