SQL Alchemy Closure Table Relationship Definition

Question

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

Answer 1

After posting to the SQL Alchemy Google Group and receiving some awesome help from Michael Bayer, I arrived at the following definition of the areas relationship in the DbRoute class

areas = relationship('DbArea',
    backref = backref('routes', order_by = 'DbRoute.name'),
    secondary = area_relationship_table,
    primaryjoin = area_id == area_relationship_table.c.descendent,
    secondaryjoin = DbArea.area_id == area_relationship_table.c.ancestor,
    innerjoin = True, order_by = DbArea.name,
    foreign_keys =
       [area_relationship_table.c.ancestor,
            area_relationship_table.c.descendent]) 

The key was really in defining the joins properly. Now I can easily go from a route instance and find areas in the ancestor tree, or from an area and find all routes in the descendent tree.