issue with joineload and multiple relationships to the same table

[Jonathan Vanasco]

i have roughly the following model: Widget > Widget2Foo > Foo >  Foo2Bar > Bar wherein `Foo` has multiple relationships to `Bar` (via a filter )

the setup works for lazyloading and subqueryloading, but I can't do multiple joinedloads:

eg:

lazyloading and this subquery works:

    query = s.query(Widget)\

        .options(joinedload('to_foos').joinedload('foo').joinedload('to_bars').joinedload('bar'))\

        .options(subqueryload('to_foos.foo.to_bars_special.bar'))

but this won't work, because sqlalchemy has issues with the final segment

    query = s.query(Widget)\

        .options(joinedload('to_foos').joinedload('foo').joinedload('to_bars').joinedload('bar'))\

        .options(joinedload('to_foos').joinedload('foo').joinedload('to_bars_special').joinedload('bar'))

i included the relevant bits below and can make a repeatable if needed .

i've traced the issue to the orm having issues with the the multiple joins to the same table -- so I figure i'm either pushing the limits or implemented that last relationship wrong and this will be obvious to someone with more experience.

---

class Foo(Base):

    __tablename__ = 'foo'

    id = Column(Integer, primary_key=True)

    to_bars = relationship("Foo2Bar",

                           primaryjoin="""Foo.id==Foo2Bar.foo_id""",

                           )

    to_bars_special = relationship("Foo2Bar",

                                   primaryjoin="""and_(Foo.id==Foo2Bar.foo_id,

                                                  Foo2Bar.bar_id==Bar.id,

                                                  Bar.is_special==True,

                                                  )""",

                                   )

class Bar(Base):

    __tablename__ = 'bar'

    id = Column(Integer, primary_key=True)

    is_special = Column(Boolean, nullable=True)

class Foo2Bar(Base):

    __tablename__ = 'foo2bar'

    __primarykey__ = ['foo_id', 'bar_id']

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

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

[mike bayer]

oh, wait. how is that supposed to work w/ "Bar"? that will definitely
fail. the biggest reason would be that "Bar" is not part of the FROMs
it expects so you are likely getting something like "FROM foo JOIN
foo2bar ON <onclause>, bar".

Take a look at very simple query(Foo).join(Foo.to_bars_special) to see
if it's relying on that. if you see a comma in the FROM clause, you're
out.

to get that into a joinedload you'd need to encapsulate that join a
little better, this is discussed at
http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#relationship-to-non-primary-mapper



"when we seek to join from A to B, making use of any number of C, D,
etc. in between, however there are also join conditions between A and B
directly."

seems like it describes this case !

> )
>
> class Bar(Base):
> __tablename__ = 'bar'
> id = Column(Integer, primary_key=True)
> is_special = Column(Boolean, nullable=True)
>
> class Foo2Bar(Base):
> __tablename__ = 'foo2bar'
> __primarykey__ = ['foo_id', 'bar_id']
> foo_id = Column(Integer, ForeignKey("Foo.id"), primary_key=True)
> bar_id = Column(Integer, ForeignKey("Bar.id"), primary_key=True)
>

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Jonathan Vanasco]

Thanks. That's exactly it.  It makes perfect sense why it just happens to work when I join one bar onto this, but not two.

FYI, I'm not seeing a comma on the simple join - but it's still a bad query that illustrates the problem as Bar doesn't get joined in:

   SELECT foo.id AS foo_id  FROM foo JOIN foo2bar ON foo.id = foo2bar.foo_id AND foo2bar.bar_id = bar.id AND bar.is_special = true LIMIT %(param_1)s