AF wrote: > > Hello, > > Given hierarchical data similar to: > http://www.sqlalchemy.org/docs/05/mappers.html?#adjacency-list-relationships > > With out resorting to brute force recursive queries in my objects: > > 1) Is there any way to retrieve all a node's children / sub-children? > > 2) Is there a way to retrieve the list of a nodes parents? >
choice 1. Assign all nodes some common identifier that identifies the whole sub-tree, and load all nodes of that subtree into memory on that identifier. This is IMHO the most pragmatic approach for most cases assuming your tree isnt huge. I'll note that even high volume websites like Reddit use this approach to load all comments for a story (I checked). choice 2. Use recursive operators, like in oracle CONNECT BY. Not sure if this is what you meant by "brute force". SQLAlchemy doesn't have native support for these as of yet but you can use literal text expressions. choice 3. Use joins. SQLA's eager loading operators can automatically construct the joins to load parent/children along relation(), but you need to pre-determine the "depth" ahead of time. Using joins with recursive trees can easily lead to overly large results and excessive joins so I'd be cautious/sparing with this approach. choice 4. use nested sets. this schema is the standard way to represent trees in SQL when you want in-SQL navigability of descendants and parents, but its a beast to persist. there is an example of this in the SQLA distribution and I think I also saw a 3rd party implementation on Pypi at some point. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
