从 SQLalchemy 中的自引用表创建树 [英] Creating a tree from self referential tables in SQLalchemy

问题描述

我正在为面向 iPhone 的网站在 Flask 中构建一个基本的 CMS，但我遇到了一些问题.我有一个非常小的数据库，只有 1 个表(页).这是模型:

I'm building a basic CMS in flask for an iPhone oriented site and I'm having a little trouble with something. I have a very small database with just 1 table (pages). Here's the model:

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 字段中引用另一个页面对象.我试图在管理面板中做的是有一个嵌套的无序列表，所有页面都嵌套在其父页面中.我对如何做到这一点知之甚少.我能想到的只有以下内容(它只能工作(也许 - 我还没有测试过)2 个级别):

As you can see, for sub pages, they just reference another page object in the parent_id field. What I'm trying to do in the admin panel is have a nested unordered list with all the pages nested in their parent pages. I have very little idea on how to do this. All i can think of is the following (which will only work (maybe—I haven't tested it) 2 levels down):

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 个嵌套页面的情况下进行这项工作?

I would then just format it into a list in the template. How would I make this work with potentially over 10 nested pages?

提前致谢！

我环顾四周，发现 http://www.sqlalchemy.org/docs/orm/relationships.html#adjacency-list-relationships，所以我添加了

I've looked around a bit and found http://www.sqlalchemy.org/docs/orm/relationships.html#adjacency-list-relationships, so I added

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

到我的 Page 模型的底部.我正在考虑递归地遍历所有内容并将其添加到对象树中.我可能没有任何意义，但这是我能描述它的最好方式

to the bottom of my Page model. and I'm looking at recursively going through everything and adding it to a tree of objects. I've probably made no sense, but that's the best way I can describe it

编辑 2: 我尝试制作一个递归函数来遍历所有页面并生成一个包含所有页面及其子项的大嵌套字典，但它不断使 python 崩溃，所以我认为这只是一个无限循环......这是函数

EDIT 2: I had a go at making a recursive function to run through all the pages and generate a big nested dictionary with all the pages and their children, but it keeps crashing python so i think it's just an infinite loop... here's the function

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

以及我正在测试的页面:

and the page i'm testing it with:

@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)

有人有什么想法吗?

推荐答案

查看 http://sqlamp.angri.ru/index.html

或 http://www.sqlalchemy.org/trac/browser/examples/adjacency_list/adjacency_list.py

UPD:对于 adjacency_list.py 声明性示例

UPD: For adjacency_list.py declarative example

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

在示例中使用查询从 db 递归获取数据:

Use query in example for recursive fetch data from db:

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

这篇关于从 SQLalchemy 中的自引用表创建树的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！