Hello Michael, thank you for your answer. It is written in the documentation (http://docs.sqlalchemy.org/en/rel_0_8/orm/relationships.html#sqlalchemy.orm.relationship) :
> *innerjoin=False* – > > when True, joined eager loads will use an inner join to join against > related tables instead of an outer join. The purpose of this option is > generally one of performance, as inner joins generally perform better than > outer joins. Another reason can be the use of with_lockmode, which does > not support outer joins. > > This flag can be set to True when the relationship references an object > via many-to-one using local foreign keys that are not nullable, *or when > the reference is one-to-one or a collection that is guaranteed to have one > or at least one entry*. > So here I am not trying to confuse the query, it is just that there is at least one entry for RelationB.relation_c. So I thought that SQLAlchemy would have inferred that the only solution here was to use a LEFT OUTER JOIN. I have the same result if I remove the lazy="joined" and if I use the query : q3 = session.query(RelationA).\ outerjoin(RelationA.relation_b).\ options(contains_eager(RelationA.relation_b)).\ options(joinedload(RelationA.relation_b, RelationB.relation_c)) So, if I understand well, there is nothing wrong with my mapping, right? The wrong part is just the query and I should fix it as you previously mentioned, right? Le mardi 9 avril 2013 16:25:52 UTC+2, Michael Bayer a écrit : > > 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 > <mik...@zzzcomputing.com<javascript:>> > wrote: > > > On Apr 9, 2013, at 8:12 AM, Etienne Rouxel > <rouxel....@gmail.com<javascript:>> > 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 sqlalchemy+...@googlegroups.com <javascript:>. > To post to this group, send email to sqlal...@googlegroups.com<javascript:> > . > 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 sqlalchemy+...@googlegroups.com <javascript:>. > To post to this group, send email to sqlal...@googlegroups.com<javascript:> > . > 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.