with_polymorphic() changes the query generated by column_property()

[Former user]

See the attached test case. Tested with 0.6.6 and 0.7b4.

[Former user]

Test case

[Mike Bayer]

with_polymorphic is a very ham-handed feature and I usually don't use it - it needs to adapt every occurrence of a particular FROM clause to the new one. There's the potential to make special exceptions for certain kinds of properties but this is an intricate job - if for example the with_polymorhic were placing an aliased subquery, instead of a plain LEFT OUTER JOIN, into the FROM clause, then the outermost "select" from "entity" would in fact need to be adapted since "entity" is no longer available at the top of the query. So if I ever get the time I'd like to look at this issue a little more and see what refinements might be available.

A quick way to keep your subquery from being hit by the aliasing is to join against an "alias" of the "Alias" table:

alias_table = Alias.__table__.alias()
Entry.alias_cnt = column_property(
    select([func.count()](func.count()), Entry.name_id == alias_table.c.target_id)
    .label("alias_cnt"))

also your query at the bottom isn't correct since specifying just "Name" is not specific enough, it ends up linking Alias and Name together based on foreign keys. Changing it to "Entity.name" gets the right result:

q = session.query(Entry).with_polymorphic('*').join(Entry.name).filter_by(name="Entry name")
assert q.one().alias_cnt == 1

[Mike Bayer]

sorry, everywhere I said "entity" above I meant "entry"....

[Former user]

The workaround using alias() seems to work, thanks!

[Mike Bayer]

yeah poking around with some alternatives, this is painful but really the solution is to use Alias.__table__.alias(). The column property begins as "select something from alias, entry". The with polymorphic, or also using select_from(), does this:

• replace "entry" in the FROM clause to "entry join alias".
• finds "entry" in the FROM list of the column_property subquery and replaces it with "entry join alias" as well. If I make it not do this in the column clause, it breaks for sure - if you said query.select_from(some subquery) for example, "entry" is not in the FROM clause anymore - it has to replace all of "entry" with "subquery".
• the column_property subquery now masks the individual "entry" and "alias" FROM objects, with the join of those two tables. This is how the select() construct works. It figures out a FROM list like "FROM a, b, a join b" means to read "FROM a join b". That's a really fundamental behavior I can't really change.
• at render time, normally the column_property subquery would see "entry" in the enclosing query, and auto-correlate, leaving just the "alias" table in its FROM clause. But now, the only FROM we're rendering is "entry join alias" - and auto-correlation has a rule that if there is only one FROM clause, we render it, instead of assuming correlation.
• If we made the column_property subquery use .correlate(Entry.__table__), which is what we really want, now we get "(SELECT count(*) AS count_1 WHERE entry.name_id = alias.target_id)" with no FROM clause at all in the subquery - again because "entry" was replaced with "entry join alias", we're "correlating" to that which means don't render, it doesn't render. This is actually almost what we want, and then
• we target at Alias.__table__.alias() instead, so we get "alias" as an FROM clause that's not conflated with the one with_polymorphic is adding. The "entry join alias" is actually in the FROM list of the subquery too, but it doesn't render since it is "correlated" with the outside.

That's sort of how the rules work and this case is a really weird one, in that you're joining twice to "Alias" in different ways.