SQLAlchemy question: Differing behaviours in chained join vs multiple on-clause

join_multiple_vs_chain_pm.py

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session, relationship, backref, sessionmaker
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.schema import ForeignKey

Base = declarative_base()


class Object(Base):
    """ Object ORM """
    __tablename__ = 'object'

    type = Column(String(30))
    __mapper_args__ = {
        'polymorphic_identity': 'object',
        'polymorphic_on': type
    }

    id = Column(Integer, primary_key=True)
    name = Column(String(256))

    def __repr__(self):
        return "<Object(id='%s', name='%s')>" % (self.id, self.name)


class A(Object):
    __tablename__ = 'a'

    __mapper_args__ = {
        'polymorphic_identity': 'a',
    }

    id = Column(Integer, ForeignKey('object.id'), primary_key=True)

    b_list = relationship(
        'B',
        secondary='a_b_association',
        backref='a_list'
    )


class B(Object):
    __tablename__ = 'b'

    __mapper_args__ = {
        'polymorphic_identity': 'b',
    }

    id = Column(Integer, ForeignKey('object.id'), primary_key=True)


class ABAssociation(Base):
    __tablename__ = 'a_b_association'

    a_id = Column(Integer, ForeignKey('a.id'), primary_key=True)
    b_id = Column(Integer, ForeignKey('b.id'), primary_key=True)


class X(Base):
    __tablename__ = 'x'

    id = Column(Integer, primary_key=True)
    name = Column(String(30))

    obj_id = Column(Integer, ForeignKey('object.id'))
    obj = relationship('Object', backref='x_list')

    def __repr__(self):
        return "<X(id='%s', name='%s')>" % (self.id, self.name)


engine = create_engine("postgresql://questions_user:questions_pass@localhost:5432/questions_db", echo=True)

Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

a = A(name='a1')
session.add(a)
b = B(name='b1', a_list=[a])
session.add(b)

x = X(name='x1', obj=a)
session.add(x)

session.commit()

# JOIN chain
q = session.query(
        B
    ).join(
        B.a_list
    ).join(
        A.x_list
    ).filter(
        X.name == 'x1'
    )

for b in q.all():
    print b

# JOIN multiple 'on' clause
q = session.query(B).join('a_list', 'x_list').filter(X.name == 'x1')

for b in q.all():
    print b

https://groups.google.com/forum/#!topic/sqlalchemy/G1ZUJU_xXM0