Solution
以下出奇简单的自引用多对多递归 CTE 查询将返回查找所有后代的所需结果b:
nodealias = aliased(Node)
descendants = session.query(Node)\
.filter(Node.id == b.id) \
.cte(name="descendants", recursive=True)
descendants = descendants.union(
session.query(nodealias)\
.join(descendants, nodealias.parent)
)
测试用
for item in session.query(descendants):
print(item.property_1, item.property_2)
Yields:
b 2
d 4
e 5
g 7
h 8
i 9
下列哪个是正确的列表b及其所有后代。
完整的工作示例代码
这个例子添加了一个方便的功能Node用于返回对象的所有后代的类，同时还计算从其自身到其所有后代的路径：
from sqlalchemy.orm import aliased
from sqlalchemy import Column, ForeignKey, Integer, Table, Text
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm import sessionmaker
engine = create_engine('sqlite://', echo=True)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()
association_table = Table('association_table', Base.metadata,
Column('parent_id', Integer, ForeignKey('node.id'), primary_key=True),
Column('child_id', Integer, ForeignKey('node.id'), primary_key=True))
class Node(Base):
__tablename__ = 'node'
id = Column(Integer, primary_key=True)
property_1 = Column(Text)
property_2 = Column(Integer)
# http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#self-referential-many-to-many-relationship
child = relationship('Node',
secondary=association_table,
primaryjoin=id==association_table.c.parent_id,
secondaryjoin=id==association_table.c.child_id,
backref='parent'
)
def descendant_nodes(self):
nodealias = aliased(Node)
descendants = session.query(Node.id, Node.property_1, (self.property_1 + '/' + Node.property_1).label('path')).filter(Node.parent.contains(self))\
.cte(recursive=True)
descendants = descendants.union(
session.query(nodealias.id, nodealias.property_1, (descendants.c.path + '/' + nodealias.property_1).label('path')).join(descendants, nodealias.parent)
)
return session.query(descendants.c.property_1, descendants.c.path).all()
Base.metadata.create_all(engine)
a = Node(property_1='a', property_2=1)
b = Node(property_1='b', property_2=2)
c = Node(property_1='c', property_2=3)
d = Node(property_1='d', property_2=4)
e = Node(property_1='e', property_2=5)
f = Node(property_1='f', property_2=6)
g = Node(property_1='g', property_2=7)
h = Node(property_1='h', property_2=8)
i = Node(property_1='i', property_2=9)
session.add_all([a, b, c, d, e, f, g, h, i])
a.child.append(d)
b.child.append(d)
d.child.append(g)
d.child.append(h)
g.child.append(i)
b.child.append(e)
e.child.append(h)
c.child.append(f)
e.child.append(i)
session.commit()
for item in b.descendant_nodes():
print(item)
session.close()
"""
Graph should be setup like this:
a b c
\ / \ |
d e f
|\ /|
g h |
+---+
i
"""
Output:
('d', 'b/d')
('e', 'b/e')
('g', 'b/d/g')
('h', 'b/d/h')
('h', 'b/e/h')
('i', 'b/e/i')
('i', 'b/d/g/i')
Comments
- 审查自引用查询的 SQLAlchemy 文档 http://docs.sqlalchemy.org/en/latest/orm/self_referential.html有帮助
- 我前几次尝试的问题是我尝试使用 SQL Alchemy公共关系运算符 http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#common-relationship-operators例如any(), 包含（）, and has()而不是自引用连接操作
- 我还找到了一个有用的 SO 条目，可以在原始 SQL 中完成我想要的事情 https://dba.stackexchange.com/questions/94932/getting-all-descendants-of-a-parent.
