查询自引用List关系来检索几个级别的子级 [英] Query self-referential List relationship to retrieve several level child

问题描述

我有一个自我指涉的相邻列表关系，我创建了以下的SqlAlchemy文档。这个模型看起来像：

pre $ class $ Menu $ $ $ $ $ $ id $ db.Column（db.Integer ，primary_key = True）
title = db.Column（db.String（255））
ordering = db.Column（db.SmallInteger，default ='1'）
parent_id = db。列（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  -  +  - >等等，
 | +  - >等等，如果存在
 + ---> 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 
  

如何查询检索上述数据？

resucrsive = True 得到一个具有常量查询数的菜单项目树，它转换为SQL的 WITH RECURSIVE （在某些RDBMS上不可用）。这可能是有用的，如果树是大的，你想节省一些时间。

 ＃db  -  SQLA会话
 
＃选择菜单树的根目录$ b $ root = Menu.query.filter（Menu.parent_id == None）.first（）
 
＃获取所有菜单项的ID在包含递归查询的树中
 included = db.query（
 Menu.id 
）.filter（
 Menu.parent_id == root.id 
）.cte name =included，recursive = True）
 
 included_alias = aliased（included，name =parent）
 menu_alias =别名（Menu，name =child）
 
 included = included.union_all（
 db.query（
 menu_alias.id 
）.filter（
 menu_alias.parent_id == included_alias.c.id 
）
）
 
＃包含root的id并从元组中提取id 
 menu_ids = map（
 lambda _tuple：_tuple [0]，
 [ （root.id）] + db.query（included.c.id）.distinct（）。all（），
）
 
＃取SQLA模型
 menus = Menu.query.filter（Menu.id.in_（menu_ids））。所有（） 
  

如果您不需要速度，只需使用<$ c $在菜单项上运行DFS或BFS c>小孩的关系。

I have a self referential Adjacency List Relationship, that I created following SqlAlchemy documentation. The model looks like:

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

what I actually want is to have query on that model and get data like:

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

Would be represented with data such as:

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

How can I query to retrieve data represented as above?

解决方案

You can get a tree of Menu items with constant number of queries using resucrsive=True, which translates to SQL's WITH RECURSIVE (not available on some RDBMS). That may be useful if the tree is big and you want to save some time.

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

If you don't need speed, just run DFS or BFS on Menu items using children relationship.

这篇关于查询自引用List关系来检索几个级别的子级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！