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
> <[email protected]<javascript:>>
> wrote:
>
>
> On Apr 9, 2013, at 8:12 AM, Etienne Rouxel
> <[email protected]<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 [email protected] <javascript:>.
> To post to this group, send email to [email protected]<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 [email protected] <javascript:>.
> To post to this group, send email to [email protected]<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 [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.
