eager load many-to-many without generating cycles

[Christopher Lee]

Hey folks, I am looking for a bit of advice.  I have a many-to-many relationship which goes through a middle class:

class Foo(Base):

    id = Column(Integer, primary_key=True)

class Bar(Base):

    id = Column(Integer, primary_key=True)

class FooToBar(Base):

    foo_id = Column(Integer, primary_key=True, ForeignKey("foo.id"))

    bar_id = Column(Integer, primary_key=True, ForeignKey("bar.id")) 

I want to be able to eagerly-load a tree from a Foo to all its Bars, or a tree from a Bar to all its Foos, but I do not want to eagerly-load the entire graph of related Foo and Bar objects.  If I naively set up the relationship, that is what happens:

FooToBar.foo = relationship(Foo, backref="bars", lazy="joined")

FooToBar.bar = relationship(Bar, backref="foos", lazy="joined") 

Is there a straightforward way to accomplish tree-like loading?  (My actual code masks the existence of FooToBar with two association_proxy attributes, but I don't think that is relevant for the example.)  Could I generate two different models (in place of FooToBar), each with a one-way eager-loading relationship?  Or could I use join_depth on the relationship to cap how far the recursion goes?

Thanks,

Chris

[Michael Bayer]

Eager loading should be guarding against any kind of endless recursion from occurring here. You should be able to set up the relationships like you have, and it will normally halt as soon as it sees the same relationship more than once in the path. join_depth would set a number of how many times its acceptable to see a particular relationship in the path. If there isn't any join_depth set, it might be defaulting to not doing it at all, I'd have to try it out to confirm.

Have you tried just running it ?

[Christopher Lee]

There is no endless recursion going on; each object is only loaded
once. Sorry I was not specific. But, given the following object
graph:

Foo 1 -> [Bar 1, Bar 2, Bar 3]
Foo 2 -> [Bar 4, Bar 5, Bar 6]
Bar 1 -> [Foo 1, Foo 2]

When I query for Foo1, I want to eagerly load Foo1, Bar 1, Bar 2, and
Bar3. I do not want to eagerly load Bar 1, Foo 2, Bar 4, Bar 5, Bar
6.
Similarly, if I query for Bar 1, I want to eagerly load Foo1 and Foo2.
I do not want to eagerly load Bar 2-6.

Is that any clearer?

[Michael Bayer]

On Aug 29, 2012, at 7:00 PM, Christopher Lee wrote:

>
>
> There is no endless recursion going on; each object is only loaded
> once. Sorry I was not specific. But, given the following object
> graph:
>
> Foo 1 -> [Bar 1, Bar 2, Bar 3]
> Foo 2 -> [Bar 4, Bar 5, Bar 6]
> Bar 1 -> [Foo 1, Foo 2]
>
> When I query for Foo1, I want to eagerly load Foo1, Bar 1, Bar 2, and
> Bar3. I do not want to eagerly load Bar 1, Foo 2, Bar 4, Bar 5, Bar
> 6.
> Similarly, if I query for Bar 1, I want to eagerly load Foo1 and Foo2.
> I do not want to eagerly load Bar 2-6.
>
> Is that any clearer?

sure, join_depth will control how deep a particular joined/subquery eagerload chain will go. Current behavior of join_depth is a simple check of how deep a particular relationship is located in an eager load chain, and this is not specific to the target relationship in contradiction to my earlier post where I was remembering incorrectly what it does. Setting it to one on both of these relationships will cause the eager chain to only go one mapper deep.

This raises the issue of join_depth preventing eager loads entirely when the parent object is itself already the subject of unrelated eager load, which isn't quite what it was intended for (it was intended for recursive loops), so I wonder if a new feature might someday be warranted which allows the "depth" question to be answered in a more comprehensive way.