On Feb 18, 2011, at 11:09 AM, Randall Nortman wrote: > I have working code -- I just want to make sure I'm doing this the > best way. If I have a graph of related objects, spanning numerous > tables and relationships, including many-to-many, and I want to > eagerly load the entire graph (rooted in some selected row or rows of > one of the tables) in a single query, I can do something like this: > > (In this example, B and C have a many-to-many relationship containing > extra fields on the relationship table, with BC as the association > object. The other relationships are either one-to-many or many-to-one > -- both seem to work the same way for this purpose.). > > session.query(A, B, BC, C, D, E).join(B, BC, C, D, > E).filter(A.id==someId).options(contains_eager(A.b, B.bc, BC.c, C.d, > D.e)) > > That seems to work.
are you watching the SQL emitted when you load everything ? in 0.7, we've made a change to contains_eager() such that what you have above will work as expected. in 0.6, contains_eager(x, y, z, q) is only going to mark "q" as the targeted eager load - you actually need to (and I know you're going to hate this) emit five separate contains_eager() calls for each path, "contains_eager(A.b), contains_eager(A.b, B.bc), contains_eager(A.b, B.bc, BC.c), ..." the join() call there is also amazing, I guess that works doesn't it. > It gives me a big honkin' query with inner joins, > returning a number of rows equal to the number of E's in the graph > rooted at A.id==someId. Each row contains all columns of all the > tables. In my particular application at least, it is faster to do it > this way than to load each relation lazily, in the case where it's all > going to be loaded anyway. My question is simply -- is there a better > way? there's a detailed discussion about eager loads at http://www.sqlalchemy.org/docs/orm/loading.html#what-kind-of-loading-to-use which makes the points I'd make here. Collections tend to be better with subqueryload. Many-to-ones can be optimized by pre-loading all the possible m2o targets ahead of time, then each "A.b" call just retrieves "B" from the identity map. Though above that's a pretty deep traversal and this might not be that straightforward. > I chose contains_eager so that I could make the joins all inner > joins, because in my case I don't need to worry about rows higher up > in the chain that don't have corresponding rows further down. OK here, you could also use joinedload_all(A.b, B.bc, ..., innerjoin=True). It just would mean you'd only be able to query(A), the eagerload rows are only routed to collections. -- 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.
