SQL Alchemy 闭包表关系定义

Question

我最近开始使用SQL Alchemy来做一个关于攀岩区域和路线的项目。区域是有层次结构的，一个区域可以包含多个子区域，而这些子区域又可以包含其他区域。每条路线直接关联到一个特定的区域，同时也和这个区域的上级区域有关。

为了实现这个功能，我选择使用一种叫做闭包表的方式，这个方法是Bill Karwin提出的。在闭包表的实现中，会创建一个第二个表来存储祖先和后代的信息。当添加一个节点时，会创建一行自引用的记录，同时也会为树中的每个祖先创建一行记录。

表的结构大致如下（简化版）：

-- 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中使用反向引用'routes'时，出现了以下错误：

sqlalchemy.exc.StatementError: 没有配置列route.area_id在映射器Mapper|DbArea|area...（原始原因：UnmappedColumnError: 没有配置列route.area_id在映射器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({})]

我猜我可能需要在DbArea中添加一些东西来建立这个关系，但尝试了几种不同的选项后，还是没能找到解决办法。

错误处理 sqlalchemy 数据模型数据查询关系数据库自引用层次结构闭包表

SQL Alchemy 闭包表关系定义

1 个回答

撰写回答