Query self-referential List relationship to retrieve several level child

python, sqlalchemy

Solution

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.

Problem

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?

Original source