On Apr 9, 2013, at 8:12 AM, Etienne Rouxel <[email protected]> wrote:
> Hello
>
> I am wondering why the outputs q1 and q2 below are not the same. Is it a bug?
its not a bug. You have "lazy='joined'" and "innerjoin=true" on
RelationB.relation_c, and automatic joined loading isn't automated to the
degree that it detects that "RelationB" is actually the target of an explicit
outer join. If you try to confuse Query like that it will gladly go along :).
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base
>
> Base = declarative_base()
>
> _relation_a_table = Table('relation_a', Base.metadata,
> Column('id', Integer, primary_key=True),
> Column('refno', Integer)
> )
>
> _relation_b_table = Table('relation_b', Base.metadata,
> Column('id', Integer, primary_key=True),
> ForeignKeyConstraint(['id'], ['relation_a.id'])
> )
>
> _relation_c_table = Table('relation_c', Base.metadata,
> Column('id', Integer, primary_key=True),
> ForeignKeyConstraint(['id'], ['relation_b.id'])
> )
>
> class RelationA(Base):
> __table__ = _relation_a_table
>
> class RelationB(Base):
> __table__ = _relation_b_table
>
> relation_a = relationship('RelationA',
> innerjoin=True,
> backref=backref('relation_b'))
>
> class RelationC(Base):
> __table__ = _relation_c_table
>
> relation_b = relationship('RelationB',
> innerjoin=True,
> backref=backref('relation_c',
> innerjoin=True,
> lazy='joined'))
>
> if __name__ == '__main__':
>
> # Initialize database models
> engine = create_engine('postgresql://xxx@localhost:5432/xxx')
> Session = sessionmaker(bind=engine)
> session = Session()
>
> q1 = session.query(RelationA).\
> options(joinedload(RelationA.relation_b))
>
> q2 = session.query(RelationA).\
> outerjoin(RelationA.relation_b).\
> options(contains_eager(RelationA.relation_b))
>
> print q1
> print q2
>
> # Actual and expected SQL output for q1:
>
> # SELECT
> # relation_a.id AS relation_a_id,
> # relation_a.refno AS relation_a_refno,
> # relation_b_1.id AS relation_b_1_id,
> # relation_c_1.id AS relation_c_1_id
> # FROM relation_a
> # LEFT OUTER JOIN relation_b AS relation_b_1 ON relation_a.id =
> relation_b_1.id
> # LEFT OUTER JOIN relation_c AS relation_c_1 ON relation_b_1.id =
> relation_c_1.id
>
> # Actual SQL output for q2 (with JOIN):
>
> # SELECT
> # relation_a.id AS relation_a_id,
> # relation_a.refno AS relation_a_refno,
> # relation_b.id AS relation_b_id,
> # relation_c_1.id AS relation_c_1_id
> # FROM relation_a
> # LEFT OUTER JOIN relation_b ON relation_a.id = relation_b.id
> # JOIN relation_c AS relation_c_1 ON relation_b.id = relation_c_1.id
>
> # Expected SQL output for q2 (with LEFT OUTER JOIN):
>
> # SELECT
> # relation_a.id AS relation_a_id,
> # relation_a.refno AS relation_a_refno,
> # relation_b.id AS relation_b_id,
> # relation_c_1.id AS relation_c_1_id
> # FROM relation_a
> # LEFT OUTER JOIN relation_b ON relation_a.id = relation_b.id
> # LEFT OUTER JOIN relation_c AS relation_c_1 ON relation_b.id =
> relation_c_1.id
>
>
> --
> 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 [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
