sqlalchemy in查询优化_SQLAlchemy：邻接表关系（Adjacency List Relationships更新中）

最新推荐文章于 2025-06-15 14:00:00 发布

最新推荐文章于 2025-06-15 14:00:00 发布 · 286 阅读

文章标签：

#sqlalchemy in查询优化

本文介绍邻接表模式及其在SQLAlchemy中的实现方法，包括一对多与多对一的关系配置，复合邻接列表的应用及自引用查询策略。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

仍然是关于自引用的文章：Adjacency List Relationships

说明：对SQLAlchemy的relationship还缺乏了解，以下译文需要优化。

邻接表模式是一种常见的关系模式，表中包含指向自身的外键。这是在平面表中表示分层数据的最常用方法。其他方法包括嵌套集（nested sets），有时称为“预排序（modified preorder）”，以及实体化路径（materialized path）。尽管在SQL查询评估中，预排序由于其流畅度而具有吸引力，但是从提高并发度、降低复杂度的角度，预排序相对于邻接表几乎没有优势，所以邻接表模型可能是大多数分层存储需求最适合的模式。采用邻接表存储结构，可以将子树完全加载到应用程序的空间中。

在这个例子中，我们将使用一个名为Node的映射类来表示树结构：

class Node(Base):
    __tablename__ = 'node'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('node.id'))
    data = Column(String(50))
    children = relationship("Node")

使用此结构，如下图：

root --+---> child1
       +---> child2 --+--> subchild1
       |              +--> subchild2
       +---> child3

将用以下数据表示：

id       parent_id     data
---      -------       ----
1        NULL          root
2        1             child1
3        1             child2
4        3             subchild1
5        3             subchild2
6        1             child3

这里的relationship（）配置与“正常”的一对多关系的工作方式相同，除了“方向”，即关系是一对多还是多对一，默认情况下假设为一对多。要建立多对一关系，需要添加一个名为remote_side的额外指令，它是Column对象或Column对象的集合，表示应该被视为“远端”的对象：

class Node(Base):
    __tablename__ = 'node'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('node.id'))
    data = Column(String(50))
    parent = relationship("Node", remote_side=[id])

上面的例子，id列被应用为parent relationship的remote_side，从而将parent_id建立为“本地”端，然后该关系表现为多对一。

与往常一样，使用backref（）函数可以将两个方向组合成双向关系：

class Node(Base):
    __tablename__ = 'node'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('node.id'))
    data = Column(String(50))
    children = relationship("Node",
                backref=backref('parent', remote_side=[id])
            )

SQLAlchemy中有几个例子说明了自我指涉策略; 这些包括Adjacency List和XML Persistence。

复合邻接列表

邻接表关系有一个比较少见的子类型，它的join条件的“本端”和“远端”都引用了同一列。一个例子是如下的Folder类；account_id和folder_id构成复合主键，account_id指向自身，指明属于同一个parent的子目录，folder_id指向该账户中的特定文件夹。【译注：没看懂account_id和parent之间的关系】

class Folder(Base):
    __tablename__ = 'folder'
    __table_args__ = (
      ForeignKeyConstraint(
          ['account_id', 'parent_id'],
          ['folder.account_id', 'folder.folder_id']),
    )

    account_id = Column(Integer, primary_key=True)
    folder_id = Column(Integer, primary_key=True)
    parent_id = Column(Integer)
    name = Column(String)

    parent_folder = relationship("Folder",
                        backref="child_folders",
                        remote_side=[account_id, folder_id]
                  )

在上面，我们将account_id传递到remote_side列表。 relationship（）识别此处的account_id列位于两侧，并将“remote”列与folder_id列对齐，后者识别为“远程”侧唯一存在的列。【译注：同上，仍然没看懂】

自引用查询策略

查询自引用结构和其他查询一样：

# get all nodes named 'child2'
session.query(Node).filter(Node.data=='child2')

但是，当尝试将外键从树的一个级别连接到另一个级别时，需要格外小心。在SQL中，从表到自身的连接要求表达式的至少一侧是“别名”，以便可以明确地引用它。

回想一下ORM教程中的使用别名，orm.aliased（）构造通常用于提供ORM实体的“别名”。使用此技术从Node关联到自身的用法：

from sqlalchemy.orm import aliased

nodealias = aliased(Node)
SQLsession.query(Node).filter(Node.data=='subchild1').
                join(nodealias, Node.parent).
                filter(nodealias.data=="child2").
                all()

Query.join（）还包括一个称为Query.join.aliased的功能，它可以缩短冗长的自引用连接，但代价是查询灵活性。此功能执行与上述类似的“别名”步骤，而无需显式实体。在别名连接之后调用Query.filter（）和类似的东西将使Node实体适应别名的实体：

session.query(Node).filter(Node.data=='subchild1').
        join(Node.parent, aliased=True).
        filter(Node.data=='child2').
        all()

要将条件添加到较长join的多个点，请将Query.join.from_joinpoint添加到其他join（）调用：

# get all nodes named 'subchild1' with a
# parent named 'child2' and a grandparent 'root'
SQLsession.query(Node).
        filter(Node.data=='subchild1').
        join(Node.parent, aliased=True).
        filter(Node.data=='child2').
        join(Node.parent, aliased=True, from_joinpoint=True).
        filter(Node.data=='root').
        all()

Query.reset_joinpoint（）还将从过滤调用中删除“别名”：

session.query(Node).
        join(Node.children, aliased=True).
        filter(Node.data == 'foo').
        reset_joinpoint().
        filter(Node.data == 'bar')

有关使用Query.join.aliased任意连接自引用节点链的示例，请参阅XML Persistence。