使用属性和混合转换器对遍历以下树有一点帮助；

class Category(db.Model):
    __tablename__ = 'category'
    id = db.Column(db.Integer, primary_key=True)
    parent = db.Column(db.Integer, db.ForeignKey('category.id'), nullable=True)
    name = db.Column(db.String(400), index=True, unique=True)

    children = db.relationship("Category", cascade='all, delete-orphan', backref=db.backref("child", remote_side=[id]))
    parents = db.relationship("Category", cascade='all', backref=db.backref("back", remote_side=[id]))
    entries = db.relationship("Feeds", backref='entry', lazy='dynamic')


class Feeds(db.Model):
    __tablename__ = 'feeds'
    id = db.Column(db.Integer, primary_key=True)
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
    name = (db.String(400), index=True, unique=True)

    @property
     def parents(self):
         allparents=[]
         p = self.children
         while p:
             allparents.append(p)
             p = p.children
         return allparents

我有一个简单的对象

catlist = db.session.query(Category).filter_by(id=1).all()

1. 我如何遍历所有树来获得具有可变树深度的子级 即祖先 -> parent -> child -> 子 child ？

2. 如何只获取一个子对象？

Feed模型也是一样，如何遍历祖先树，如何只得到最顶层的祖先节点？

这是我到目前为止所做的，除了生成错误的属性对象之外似乎效果不佳

catlist = db.session.query(Category).filter_by(id=1).all()
for cat in catlist:
    cat[0].children



File "/home/afidegnum/PycharmProjects/store/core/model.py", line 45, in children
    p = self.children
RuntimeError: maximum recursion depth exceeded

最佳答案

在 Postgresql 中，您可以使用递归查询。在您的情况下，您可以使用以下方法:

@staticmethod
def get_parents_list(category_id):
    beginning_getter = Session.query(Category).\
        filter(Category.id == category_id).cte(name='parent_for', recursive=True)
    with_recursive = beginning_getter.union_all(
        Session.query(Category).filter(Category.id == beginning_getter.c.parent_id)
        )
    return Session.query(with_recursive)

@staticmethod
def get_children_list(category_id):
    beginning_getter = Sesion.query(Category).\
            filter(Category.id == category_id).cte(name='children_for', recursive=True)
    with_recursive = beginning_getter.union_all(
            Session.query(Category).filter(Category.parent_id == beginning_getter.c.id)
        )
    return Session.query(with_recursive)

调用:

all_children = Category.get_children_list(1).all()

查询将是这样的:

WITH RECURSIVE children_for(id, name, parent) AS
(SELECT id, name, parent
FROM categories
WHERE category.id = 1 UNION ALL id, name, parent
FROM categories, children_for
WHERE categories.parent = children_for.id)
 SELECT children_for.id, children_for.name, children_for.parent
FROM children_for;```

https://stackoverflow.com/questions/38071683/