查询自引用列表关系以获取多个层级子项

Question

我有一个自引用的邻接列表关系，这是我按照SqlAlchemy的文档创建的。这个模型看起来是这样的：

class Menu(db.Model):
    id          = db.Column(db.Integer, primary_key = True)
    title       = db.Column(db.String(255))
    ordering    = db.Column(db.SmallInteger, default = '1')
    parent_id   = db.Column(db.Integer, db.ForeignKey('menu.id'))
    children    = db.relationship("Menu",
                    cascade="all, delete-orphan",  
                    backref = db.backref('parent', remote_side=[id]),
                    collection_class = attribute_mapped_collection('id'))

我实际上想要的是对这个模型进行查询，获取像这样的数据：

root --+---> child1
       +---> child2 --+--> subchild1
       |              +--> subchild2--+--> and so on,
       |                               +--> and so on, if exists
       +---> child3 --+--> subchild1
                      +--> ...
                      +--> ...
                              +--> ...

这些数据可以表示成：

id       parent_id     title
---      -------       ----
1        NULL          root
2        1             child1
3        1             child2
4        3             subchild1
5        3             subchild2
7        5             so_on1
8        5             so_on2
6        1             child3
9        6             subchild1

我该如何查询才能得到上面那样的数据呢？

Answer 1

你可以通过设置 resucrsive=True 来获取一个菜单项的树形结构，这个设置对应于 SQL 中的 WITH RECURSIVE（不过有些数据库管理系统不支持这个功能）。如果你的树很大，这样做可以节省一些时间。

# db - SQLA session

# pick a root of the menu tree
root = Menu.query.filter(Menu.parent_id == None).first()

# get ids of all menu items in tree with recursive query
included = db.query(
    Menu.id
).filter(
    Menu.parent_id == root.id
).cte(name="included", recursive=True)

included_alias = aliased(included, name="parent")
menu_alias = aliased(Menu, name="child")

included = included.union_all(
    db.query(
        menu_alias.id
    ).filter(
        menu_alias.parent_id == included_alias.c.id
    )
)

# include the root's id and extract ids from tuples
menu_ids = map(
    lambda _tuple: _tuple[0],
    [(root.id,)] + db.query(included.c.id).distinct().all(),
)

# fetch SQLA models
menus = Menu.query.filter(Menu.id.in_(menu_ids)).all()

如果你不太在意速度，可以直接使用深度优先搜索（DFS）或广度优先搜索（BFS）来处理菜单项，利用它们之间的 children 关系。