You could look for recursive CTE (Common Table Expressions), if your database engine supports such queries. See e.g. http://www.postgresql.org/docs/8.4/static/queries-with.html for PostgreSQL. That allows arbitrary-depth queries, as opposed to join chains that have to assume a fixed depth. You could probably apply two recursive queries, one downward and one upward from the given node, to avoid querying the whole tree.
SQLAlchemy has no support for CTEs directly, though of course you can construct the query manually and execute and fetch results through SQLAlchemy. You *can* get some support for recursive queries under SQLAlchemy in https://github.com/marplatense/sqla_hierarchy/ but be warned, that project is ... "youthful" :) Regards, - Gulli -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/g7-7S4mBC3wJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.