It's a bug but a small one... I'd be ready to jump off a bridge if this kind of
thing wasn't working in general at this point. Trying your test case, the
column_property() for the moment has to be against the actual Column, not the
mapped property (there's a difference):
class A(Base):
__tablename__ = "a"
id = Column(Integer, primary_key=True)
type = Column(String(40), nullable=False)
__mapper_args__ = {'polymorphic_on': type}
anything = column_property(id + 1000)
or:
A.anything = column_property(A.__table__.c.id + 1000)
When you access A.id, you get an InstrumentedAttribute, which produces a SQL
expression equivalent to A.__table__.c.id except for an "annotation" that tells
the ORM to treat it differently, I couldn't say exactly why it goes wrong in
the way it does since it typically adapts it more aggressively, not less
so...the problem here is "a.id" isn't getting lumped into the "adaptation" of
the "a join b" as a subquery off of "x". Nice test case, thanks for making it
easy.
Anyway, that's the workaround for now and ticket 2316
http://www.sqlalchemy.org/trac/ticket/2316 is added.
On Oct 30, 2011, at 5:30 PM, Adrian Tejn Kern wrote:
> I have a column_property on a polymorphic base class. When I
> joinedload/subqueryload a derived class the colum_property makes the query
> fail.
>
> class A(Base):
> __tablename__ = "a"
> id = Column(Integer, primary_key=True)
> type = Column(String(40), nullable=False)
> __mapper_args__ = {'polymorphic_on': type}
>
> A.anything = orm.column_property(A.id + 1000)
>
> class B(A):
> __tablename__ = "b"
> account_id = Column(Integer, ForeignKey('a.id'), primary_key=True)
> x_id = Column(Integer, ForeignKey('x.id'), nullable=False)
> __mapper_args__ = {'polymorphic_identity': 'named'}
>
> class X(Base):
> __tablename__ = "x"
> id = Column(Integer, primary_key=True)
> b = orm.relationship("B"
>
>
> Calling:
> print Session.query(X).options(joinedload("b"))
>
> produces:
>
> SELECT x.id AS x_id,
> anon_1.a_id AS anon_1_a_id,
> anon_1.a_type AS anon_1_a_type,
> a.id + %(id_1)s AS anon_2,
> anon_1.b_account_id AS anon_1_b_account_id,
> anon_1.b_x_id AS anon_1_b_x_id
> FROM a,
> x
> LEFT OUTER JOIN
> (SELECT a.id AS a_id,
> a.TYPE AS a_type,
> b.account_id AS b_account_id,
> b.x_id AS b_x_id
> FROM a
> JOIN b ON a.id = b.account_id) AS anon_1 ON x.id = anon_1.b_x_id
>
> It seems that the "a.id + %(id_1)" should changed to "anon_1.a_id" and "a"
> removed from "FROM" or better "a.id + %(id_1)s" should be moved into the sub
> select named anon_1. This is probably what you want if the column_property
> was actually a subselect itself (which is want I'm actually trying to do).
>
> Am I correct in thinking that this corner case simply isn't supported yet? Or
> is it a bug? Or am I doing something wrong?
>
> Actually the above query doesn't fail outright. Although it does create a
> unsuspecting join. But if the column_property instead was something like
>
> class subA(Base):
> __tablename__ = "subA"
> id = Column(Integer, primary_key=True)
> a_id = Column(Integer, ForeignKey('a.id'), nullable=False)
> value = Column(Integer, nullable=False)
>
> A.anything = orm.column_property(
> select([func.sum(subA.value)], subA.a_id==A.id))
>
> Then the sql would be:
>
> SELECT x.id AS x_id,
> anon_1.a_id AS anon_1_a_id,
> anon_1.a_type AS anon_1_a_type,
>
> (SELECT sum("subA".value) AS sum_1
> FROM "subA"
> WHERE "subA".a_id = a.id) AS anon_2,
> anon_1.b_account_id AS anon_1_b_account_id,
> anon_1.b_x_id AS anon_1_b_x_id
> FROM x
> LEFT OUTER JOIN
> (SELECT a.id AS a_id,
> a.TYPE AS a_type,
> b.account_id AS b_account_id,
> b.x_id AS b_x_id
> FROM a
> JOIN b ON a.id = b.account_id) AS anon_1 ON x.id = anon_1.b_x_id
>
> Which naturally doesn't work at all, since "a.id" inside the first subselect
> doesn't refer to anything.
>
>
> PS: I have no idea how this email is going to get formatted, please let me
> know if it is impossible to read.
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/sqlalchemy/-/1CSullHjqPMJ.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
