这个 邻接表 模式是一种常见的关系模式,其中表包含对其自身的外键引用。这是在平面表中表示分层数据的最常见方法。其他方法包括 嵌套集合 有时被称为“修改预订单”,以及 或原料路径 . 尽管修改后的预订单在评估其在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
列应用为 remote_side
的 parent
relationship()
从而建立 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中有几个例子说明了自引用策略;这些例子包括 邻接表 和 XML持久化 .
邻接列表关系的子类别是连接条件的“本地”和“远程”端都存在特定列的罕见情况。一个例子是 Folder
类;使用组合主键 account_id
列引用自身,以指示子文件夹与父文件夹在同一帐户内;而 folder_id
引用该帐户中的特定文件夹:
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
这里的列位于两侧,并将“远程”列与 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()
SELECT node.id AS node_id,
node.parent_id AS node_parent_id,
node.data AS node_data
FROM node JOIN node AS node_1
ON node.parent_id = node_1.id
WHERE node.data = ?
AND node_1.data = ?
['subchild1', 'child2']
Query.join()
还包括一个称为 Query.join.aliased
这样可以缩短冗长的自引用联接,而牺牲了查询的灵活性。此功能执行与上面类似的“别名”步骤,而不需要显式实体。呼吁 Query.filter()
类似于别名连接之后的 适应 这个 Node
要成为别名的实体:
sqlsession.query(Node).filter(Node.data=='subchild1').\
join(Node.parent, aliased=True).\
filter(Node.data=='child2').\
all()
SELECT node.id AS node_id,
node.parent_id AS node_parent_id,
node.data AS node_data
FROM node
JOIN node AS node_1 ON node_1.id = node.parent_id
WHERE node.data = ? AND node_1.data = ?
['subchild1', 'child2']
要沿较长的连接向多个点添加条件,请添加 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()
SELECT node.id AS node_id,
node.parent_id AS node_parent_id,
node.data AS node_data
FROM node
JOIN node AS node_1 ON node_1.id = node.parent_id
JOIN node AS node_2 ON node_2.id = node_1.parent_id
WHERE node.data = ?
AND node_1.data = ?
AND node_2.data = ?
['subchild1', 'child2', 'root']
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持久化 .
在常规查询操作期间,使用从父表到子表的联接或outerjoin,可以从单个SQL语句或所有直接子集合的第二个语句填充父集合及其直接子集合或引用,从而实现对关系的预加载。当连接到相关项时,sqlAlchemy的joined和subquery eached loading在所有情况下都使用别名表,因此与自引用连接兼容。但是,要使用带有自引用关系的热切加载,需要告诉sqlAlchemy它应该连接和/或查询多少层深度;否则,热切加载根本不会发生。此深度设置通过配置 join_depth
:
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",
lazy="joined",
join_depth=2)
sqlsession.query(Node).all()
SELECT node_1.id AS node_1_id,
node_1.parent_id AS node_1_parent_id,
node_1.data AS node_1_data,
node_2.id AS node_2_id,
node_2.parent_id AS node_2_parent_id,
node_2.data AS node_2_data,
node.id AS node_id,
node.parent_id AS node_parent_id,
node.data AS node_data
FROM node
LEFT OUTER JOIN node AS node_2
ON node.id = node_2.parent_id
LEFT OUTER JOIN node AS node_1
ON node_2.id = node_1.parent_id
[]