On Apr 14, 2012, at 5:38 PM, Jeff Dairiki wrote: > I have a one-to-one relationship to a table in a different database > which I'd like to, effectively, eager-load. Lazy='joined' and > lazy='subquery' won't work, since they rely an impossible JOIN between > tables in two different databases. > > Lazy='immediate' works, but results in one query per instance. If I > have a one-to-one relationship between A and B, and execute a query > which loads 100 A's, there will be 100 more queries which each load > one B. What I'd like is to have two queries total: one to load the As > and a second to load the Bs. > > I've been able to do this manually by doing something like this: > > # Do some query > parents = session.query(Parent).filter(some_condition).all() > > # "Eager load" > ids = [ parent.id for parent in parents > if 'child' in instance_state(parent).unloaded ] > get_parent = session.query(Parent).get > for child in session.query(Child).filter(Child.parent_id.in_(ids)): > parent = get_parent(child.parent_id) > set_committed_value(child, 'parent', parent) > set_committed_value(parent, 'child', child) > > Is there a good way to do this more automatically?
I don't have a spectacular suggestion at the moment, if you've looked at how the subquery/immediate loading schemes work, the first row we get for the "parent" triggers the load of all the "child" objects, which is a product of the loader strategy system for that particular attribute. The mechanics here would need to do everything entirely after the full parent collection is loaded. I'd probably keep it simple and just do what you're doing, just either inside of a helper function like iterate_with_related(myquery, "children") or perhaps do a subclass of Query. As far as the actual load operation, there's a recipe for this kind of operation at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisjointEagerLoading which will do things a little more directly, without the need for query.get(). There's a "generalized" recipe there too which you can probably adapt to what you're doing here. > > -- > 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. > -- 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.
