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 sqlalchemy@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---