Query self-referential List relationship to retrieve several level child

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.


  • p:fileDownload in p:dataTable does not work (just refreshes page) after performing search on the p:d
  • Rails AREL .where statement
  • Passing parameter through “window.location.href”
  • Is there a way to link a linux's thread TID and a pthread_t “thread ID”
  • How to get Fully qualified domain name in unix
  • BackgroundTransferRequest WP7
  • SQL Server re-calculate or not?
  • How to set an entity field that does not exist on the table but does exists in the raw SQL as an ali
  • FTS3 searches in ORMLite?
  • SQL query to group by maximal sets of a column having inner consecutive distances below a threshold
  • Strong vs Weak entities MYSQL
  • Parenthesis() and SQL Query Performance
  • Cannot page through all results using nextPageToken on YouTube search API v3
  • unexpected behaviour of Google BigQuery WHERE NOT list CONTAINS string
  • Extract All Possible Paths from Expression-Tree and evaluate them to hold TRUE
  • Cannot get text from text area
  • MySQL performance when updating row with FK
  • NHibernate manually control fetching
  • How to 'create temp table as select' in Slick?
  • How to get data from **Realm database** using **date object**?
  • Do query loads all the data in memory
  • Merging rows to columns
  • How to add a focus style to an editable ComboBox in WPF
  • Web.config system.webserver errors
  • Force show.bind execution
  • Zurb Foundation _global.scss meta styles for js?
  • ActiveRecord query for a count of new users by day
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • Disable Enter in editText android
  • Display Images one by one with next and previous functionality
  • SQL merge duplicate rows and join values that are different
  • KeystoneJS: Relationships in Admin UI not updating
  • trying to dynamically update Highchart column chart but series undefined
  • embed rChart in Markdown
  • Linking SubReports Without LinkChild/LinkMaster
  • Easiest way to encapsulate a HTML5 webpage into an android app?
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • How can I use `wmic` in a Windows PE script?
  • Unable to use reactive element in my shiny app