Just thought I would toss in my 2-cents here, since I have lots of
hierarchical data and have
at one time or another used most of the below methods.
Choice #1 is the option that I have found that works the best.
I Use a file path-like primary key (actually I am storing data about
files), this allows me to easily do
things like:
nodes=session.query(Node).filter(Node.path.like('root/path/sub/%')).order_by(Node.path).all()
Choice #2, I don't use Oracle, but there is an implementation of CONNECT
BY in PostgreSQL's contrib module tblfunc.
Using this its somewhat easy to given a leaf node, return all of the
ancestors up to the root, the downside is I don't
believe it works in the other direction. My experience is that this is
really fast to retrieve data the first time, but since
your fetching your objects via text strings, SQLAlchemy isn't aware of
what has been fetched, in practice I found I was
duplicating fetches. I haven't used the new recursive queries in PG 8.4.
http://www.postgresql.org/docs/8.4/static/tablefunc.html
Choice #3 works well with Choice #1, especially if you are interested in
a particular node, and know ahead of time
you also want that node's grandparent.
Michael Bayer wrote:
> 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.
>
>
> >
>
>
--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgard...@creatureshop.com
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
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
-~----------~----~----~----~------~----~------~--~---
