如何编写SQLAlchemy查询，该查询将返回图中节点的所有后代? [英] How can I write a SQLAlchemy query that will return all descendants of a node in a graph?

问题描述

我正在开发一个应用程序，其中我的数据库对象通常具有多个父对象和多个子对象，并且希望创建一个SQLAlchemy查询，该查询将返回对象的所有后代.

I am working on an application where my database objects often have multiple parents and multiple children, and would like to create a SQLAlchemy query that will return all descendants of an object.

意识到我基本上是想在SQL数据库中存储图，所以我发现设置了 SQLA的递归CTE示例，它看起来像是正确的方法，但是在使其正常工作时遇到了麻烦.我认为我的情况与示例不同，因为在我的情况下，对Node.child(和Node.parent)的查询返回已检测到的列表，而不是ORM对象.

Realizing that I am basically trying to store a graph in a SQL database, I found that setting up a self-referential many-to-many schema got me most of the way there, but I am having trouble writing the query to return all descendants of a node. I tried to follow SQLA's recursive CTE example, which looks like the right approach, but have been running into problems getting it to work. I think my situation is different from the example because in my case, queries to Node.child (and Node.parent) return instrumented lists and not ORM objects.

无论如何，下面的代码将建立一个简单的有向无环断开图，如下图所示(其方向被推断为从上一行到下一行):

In any case, the code below will set up a simple directed acyclic disconnected graph that looks like this (where the direction is inferred to be from the higher row to the lower one):

a   b    c
 \ / \   |
  d   e  f
  |\ /
  g h     
  |
  i

我正在寻找的是一些帮助编写查询的帮助，该查询将为我提供节点的所有后代.

And what I'm looking for is some help writing a query that will give me all descendants of a node.

• get_descendants(d)应该返回g，h，i

• get_descendants(d) should return g, h, i

get_descendants(b)应该返回d，e，g，h，i

get_descendants(b) should return d, e, g, h, i

示例代码:

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:///:memory:', 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'
                            )

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)

session.commit()
session.close()

推荐答案

解决方案

以下令人惊讶的简单，自引用的多对多递归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)

收益:

b 2
d 4
e 5
g 7
h 8
i 9

b及其所有后代的正确列表.

Which is the correct list of b and all of its descendants.

此示例在Node类中添加了一个便捷函数，用于返回对象的所有后代，同时还计算从其自身到其所有后代的路径:

This example adds a convenient function to the Node class for returning all descendants of an object, while also computing the path from itself to all of its descendants:

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

"""

输出:

('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')

评论

• 查看用于自引用查询的SQL炼金术文档有帮助
• 最初几次尝试的问题是我试图使用SQL Alchemy 常用关系运算符，例如 any()， contains()和 has()自引用联接操作
• 我还发现了一个>有用的SO条目，可用于执行我在原始SQL中想要的操作.

Comments

• Reviewing the SQL Alchemy documentation for self-referential queries was helpful
• The problem with my first few attempts was that I was trying to use SQL Alchemy common relationship operators such as any(), contains(), and has() instead of a self-referential join operation
• I also found a helpful SO entry for doing what I wanted in raw SQL.

这篇关于如何编写SQLAlchemy查询，该查询将返回图中节点的所有后代?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！