[Sqlalchemy-tickets] Issue #3289: Aliased join with subselect does not look at load options (zzzeek
Brought to you by:
zzzeek
|
From: malthe <iss...@bi...> - 2015-01-14 08:42:36
|
New issue 3289: Aliased join with subselect does not look at load options https://bitbucket.org/zzzeek/sqlalchemy/issue/3289/aliased-join-with-subselect-does-not-look malthe: The following program outputs: ```sql SELECT b.b_id AS b_b_id, b.a_id AS b_a_id, c.id AS c_id, c.c_name AS c_c_name, c.b_id AS c_b_id FROM c JOIN ( SELECT a.id AS a_id, a.a_name AS a_a_name, b.id AS b_id, b.b_name AS b_b_name FROM a JOIN b ON a.id = b.id ) AS b ON b.b_id = c.b_id ``` Note how ``b.b_name`` is in the subselect, but not used outside. It's a minor issue because it will most probably be optimized out by the database, but it still makes the query longer than it needs to be. ```python from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) a_name = Column(String) class B(A): __tablename__ = 'b' id = Column(Integer, ForeignKey("a.id"), primary_key=True) b_name = Column(String) class C(Base): __tablename__ = 'c' id = Column(Integer, primary_key=True) c_name = Column(String) b_id = Column("b_id", Integer, ForeignKey("b.id")) b = relationship(B) e = create_engine("sqlite://", echo=True) Base.metadata.create_all(e) s = Session(e) b = B(id=1, a_name="foo", b_name="bar") s.add(b) c = C(id=1, b_id=b.id, c_name="boo") s.add(c) s.flush() alias = aliased(B, name="b") print s.query(C).join(alias).options( contains_eager(C.b, alias=alias).load_only(alias.id) ) ``` |