On Aug 20, 2007, at 5:35 AM, stephen emslie wrote:
>
> Hi. I am using a self-referential mapper to represent a multi-level
> tree of parent-child relationships. Typically I've been querying each
> parent for children that I am interested in. Up till now I have made a
> new query for each child that I am looking for, which is doesn't seem
> like the most efficient way to approach this.
>
> I've found that 0.4's aliased joins are great for selecting parents
> based on their children, and can be eager-loaded to grab entire
> subtrees with a single query. However each parent in my table can have
> many children (and trees can many many levels deep) so eager loading
> root nodes can be a bit slow too.
>
> Ideally I would like to be able to eager load only the children that
> were involved in the join as those are the only ones that I am
> interested in, so that something like:
>
> parent_instance.children
>
> or equivalent, only loads children that were part of the original join
> rather than all of parent_instance's children. Is something like this
> possible? The closet that I've found is using add_entity after each
> join in my query.
so for me to get this straight, say you are searching for node N.
you then are searching for an N such that a child node, two levels
deep, has an attribute "foo" which equals "bar":
N.children.children.foo=bar
Then when youre done loading, you have N. and youd like to load
exactly 1 child node on N, but then the full set of children on
*that* child.
The only thing we have which is like this (without writing out a
query), is the "eagerload" option:
query.options(eagerload('children.children'))...
what that means is, the "children" attribute is lazy loading on the
parent, but the "children" attribute on each of that parent's nodes
are *eager* loading. but I dont think this is exactly what you want.
The other way, is to construct your own eager-loading statement and
set it up. But youd have to set up "contains_eager" options all the
way down the chain as you'd like nodes to be assembled. So *maybe*
combining contains_eager() with eagerload() can do it, such as:
my_eager_alias = mynodes.alias('mynodes')
query = query.filter(...wahtever criterion you're using...)
query = query.from_statement(select([mynodes, my_eager_alias],
query._criterion)).\
.options(contains_eager('children', alias=my_eager_alias))
.options(eagerload('children.children'))
That will set the "children" attribute to eagerly load against your
my_eager_alias criterion and also set "chilrdren.children" to eager
load. but the above is not something ive tested, its pretty exotic.
I think theres probably some relatively simple options we can be
adding to Query here to support what you're trying to do....such as
contains_eager('children', alias=myalias, add_columns=True) which
would automatically add the columns to the Query's SELECT statement
without needing to construct your from_statement() like we're doing
above. I'd be curious though if you could play around a little with
the approach I just suggested to see if its at all workable ?
- mike
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
