Building hierarchy tree in reverse

[Vlad K.]

Hi.

I have a problem and am not sure where to begin. I need to construct a
hierarchy tree, something like adjacency_list but in reverse. More
precisely, I need entire branch but only the branch containing given
node ID. In practice, I need this for a product category tree menu which
shows items in the currently "selected" branch only (where for example I
don't need children of bba, or ba, or A because node ID is not in their
branches):

A
B
ba
bb
bba
bbb
bbc <- this node id is given at first
bbca
bbcb
bbcd
bc
bd
C
D

Basically, the way I see it, I need to:

1. Find node by ID
2. Find node's children
3. Find node's siblings
4. Node's parent becomes node, repeat from step 3 as long as there's a
parent

The table is constructed with parent_id foreign key reference to itself,
and I can fetch entire tree at level X using joinedload_all as given in
this example:

http://www.sqlalchemy.org/trac/browser/examples/adjacency_list/adjacency_list.py

I have an idea how to do it "manually" but I was wondering if there is a
feature of SQLAlchemy I could use. I'd google for any similar problems
or implementations since I don't think this is an uncommon problem, but
I'm not sure what to look for.

Thanks!

--

.oO V Oo.

[Gunnlaugur Briem]

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

[Vlad K.]

Yes I'm using PostgreSQL and now that you've linked to the docs, I
remember there was a possibility for recursion. Thanks for suggestion,
I'll look into it.

.oO V Oo.

> --
> 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 sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.