oh and also, make the join an "outer" by adding the option joinedload(RelationB.relation_c, innerjoin=False)
On Apr 9, 2013, at 10:16 AM, Michael Bayer <[email protected]> wrote: > > 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. > > -- 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.
