SQLAlchemy 闭包表关系定义

我最近开始使用 SQL Alchemy 开展一个涉及攀岩区域和路线的项目。区域是分层的，因为单个区域可以包含多个区域，而多个区域又可以包含其他区域。路线直接与单个区域关联，但也与该区域的父区域关联，等等。

为了实现这一点，我选择使用Bill Karwin 的闭合表 http://karwin.blogspot.com/2010/03/rendering-trees-with-closure-tables.html。在闭包表实现中，创建第二个表来存储祖先/后代信息。添加节点时会创建自引用行，并为树中的每个祖先创建一行。

表结构如下（简化）：

-- area --
area_id
name

-- area_relationship --
ancestor
descendent

-- route --
route_id
area_id
name

样本数据：

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

要查询给定路线（树上）的所有区域，我可以执行：

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

同样，我可以使用以下命令查询特定区域（包括后代区域）中的所有路线：

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

在 SQL Alchemy 中，我创建了一个关系和两个表来处理这些关系：

area_relationship_table = Table('area_relationship', Base.metadata,
  Column('ancestor', Integer, ForeignKey('area.area_id')),
  Column('descendent', Integer, ForeignKey('area.area_id'))
)

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 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])

目前，我可以使用 DbRoute 中的区域关系来确定各个路线的区域。但是，当我尝试在 DbArea 中使用 backref 'routes' 时，出现以下错误：

sqlalchemy.exc.StatementError: 在映射器Mapper|DbArea|area... 上没有配置列route.area_id... （原始原因：UnmappedColumnError: 在映射器Mapper|DbArea|area... 上没有配置列route.area_id...） 'SELECT 路由.route_id AS 路由_路由_id、路由.area_id AS 路由_区域_id、路由.name AS 路由_名称、route.created AS 路由_创建 \nFROM 路由、area_relationship \nWHERE %s = area_relationship.descendent AND route.area_id = area_relationship.ancestor' [immutabledict({}) ]

我猜测我可能需要向 DbArea 添加一些内容来建立关系，但在尝试了一些不同的选项后无法确定解决方案。

在发布到 SQL Alchemy Google Group 并收到一些信息后迈克尔·拜尔的大力帮助 https://groups.google.com/group/sqlalchemy/browse_thread/thread/53ac06a4eead97d3/6a010eedb7a76aca#6a010eedb7a76aca，我在 DbRoute 类中得到了以下区域关系的定义

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]) 

关键在于正确定义连接。现在，我可以轻松地从路线实例中查找祖先树中的区域，或者从某个区域中查找后代树中的所有路线。