在SQLalchemy中从自引用表创建树结构

Question

我正在用Flask为一个面向iPhone的网站构建一个基本的内容管理系统（CMS），遇到了一些小问题。我有一个非常简单的数据库，里面只有一个表（页面）。这是我的模型：

class Page(db.Model):
    __tablename__ = 'pages'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    content = db.Column(db.Text, nullable=False)
    parent_id = db.Column(db.Integer, db.ForeignKey("pages.id"), nullable=True)

如你所见，子页面只是通过parent_id字段引用另一个页面对象。我想在管理面板中做一个嵌套的无序列表，把所有页面按照父页面嵌套起来。但我对怎么实现这个几乎没有头绪。我能想到的只是以下这种方法（可能只适用于两层嵌套，我还没测试过）：

pages = Page.query.filter_by(parent_id=None)
for page in pages:
    if Page.query.filter_by(parent_id=page.id):
        page.sub_pages = Page.query.filter_by(parent_id=page.id)

然后我会把它格式化成一个列表放在模板里。那如果有超过10个嵌套页面，我该怎么做呢？

非常感谢你的帮助！

---

编辑：我查了一下，发现了这个链接 http://www.sqlalchemy.org/docs/orm/relationships.html#adjacency-list-relationships，所以我在我的Page模型底部添加了：

children = db.relationship("Page", backref=db.backref("parent", remote_side=id))

我打算递归地遍历所有内容，把它们添加到一个对象树中。我可能说得不太清楚，但这是我能描述的最好方式。

---

编辑 2：我尝试写一个递归函数，遍历所有页面并生成一个大的嵌套字典，包含所有页面及其子页面，但它一直让Python崩溃，我觉得可能是无限循环……这是我写的函数：

def get_tree(base_page, dest_dict):
    dest_dict = { 'title': base_page.title, 'content': base_page.content }
    children = base_page.children
    if children:
        dest_dict['children'] = {}
        for child in children:
            get_tree(base_page, dest_dict)
    else:
        return

还有我用来测试的页面：

@app.route('/test/')
def test():
    pages = Page.query.filter_by(parent_id=None)
    pages_dict = {}
    for page in pages:
        get_tree(page, pages_dict)
    return str(pages_dict)

有没有人有什么想法？

Answer 1

你可以看看这个链接：http://sqlamp.angri.ru/index.html

或者这个链接：http://www.sqlalchemy.org/trac/browser/examples/adjacency_list/adjacency_list.py

更新：关于 adjacency_list.py 的声明式示例

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base(metadata=metadata)

class TreeNode(Base):

    __tablename__ = 'tree'

    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('tree.id'))
    name = Column(String(50), nullable=False)

    children = relationship('TreeNode',

                        # cascade deletions
                        cascade="all",

                        # many to one + adjacency list - remote_side
                        # is required to reference the 'remote' 
                        # column in the join condition.
                        backref=backref("parent", remote_side='TreeNode.id'),

                        # children will be represented as a dictionary
                        # on the "name" attribute.
                        collection_class=attribute_mapped_collection('name'),
                    ) 

    def __init__(self, name, parent=None):
        self.name = name
        self.parent = parent

    def append(self, nodename):
        self.children[nodename] = TreeNode(nodename, parent=self)

    def __repr__(self):
        return "TreeNode(name=%r, id=%r, parent_id=%r)" % (
                    self.name,
                    self.id,
                    self.parent_id
                )    

修复递归问题

def get_tree(base_page, dest_dict):
    dest_dict = { 'title': base_page.title, 'content': base_page.content }
    children = base_page.children
    if children:
        dest_dict['children'] = {}
        for child in children:
            get_tree(child, dest_dict)
    else:
        return

在示例中使用查询来递归获取数据库中的数据：

 # 4 level deep
 node = session.query(TreeNode).\
                        options(joinedload_all("children", "children", 
                                                "children", "children")).\
                        filter(TreeNode.name=="rootnode").\
                        first()