I should have pointed out that I got a NoSuchColumnError because of "anon_1.anon_2". There is no column "anon_2" in any of the tables. It's just an alias name of a derived table.
Is "table_name_1.table_name_2" supposed to mean anything? On Tuesday, February 28, 2012 5:53:42 PM UTC+2, Michael Bayer wrote: > > > On Feb 28, 2012, at 9:40 AM, naktinis wrote: > > Column "anon_1.anon_2" is generated in the following scenario: > > dbsession.query(FirstThing, FirstThing.moved_by.any(User.id == > user_id)).options(joinedload_all('some_property')) > query = query.join(SecondThing, SecondThing.first_thing_id == > FirstThing.id) > query = query.order_by(OneThing.ordering_field).limit(count) > > Also, it is important that both FirstThing and SecondThing polymorphically > inherit from Thing. > > Effectively, query.all() generates a query like > > SELECT ... anon_1.anon_2 AS anon_1_anon_2 ... > FROM > (SELECT first_thing.id AS first_thing.id, EXISTS (SELECT 1 FROM > first_thing_moves, users ...) AS anon_2 > FROM thing JOIN first_thing ON ... JOIN (SELECT ... FROM thing JOIN > second_thing) AS anon_3 ON ... ORDER BY ... LIMIT ...) AS anon_1 ORDER BY > ... > > Why would "anon_1.anon_2" column be generated there - it is, I think, not > even a valid syntax? > > > it's valid, "anon_1" is the label applied to a subquery, you can see where > it has "(SELECT .... ) AS anon_1". "anon_1" becomes what we sometimes call > a "derived table" in the query and is then valid like any other alias name. > > The join is because when we have a joined inheritance class B inherits > from A, then we join to it from C, we are effectively joining: > > SELECT * FROM C JOIN (A JOIN B ON A.id=B.id) ON C.x=A.y > > That is valid SQL, however, it doesn't work on SQLite, and also doesn't > work on MySQL versions before 5. It also may or may not have issues on > some other backends. So SQLAlchemy turns "A JOIN B" into a subquery: > > SELECT * FROM C JOIN (SELECT * FROM A JOIN B ON A.id=B.id) AS anon_1 ON > C.x=anon_1.y > > as it turns out, this approach generalizes much more nicely than just > putting "A JOIN B" in there. Suppose classes B1 and B2 inherit from A in a > concrete fashion, using tables "B1" and "B2" to represent the full row. > Then you wanted to join from C to A. SQLAlchemy would have you doing a > "polymorphic union" which means you select from the UNION of B1 and B2: > > SELECT * FROM C JOIN (SELECT * FROM B1 UNION SELECT * FROM B2) AS anon_1 > ON C.x=anon_1.y > > where "anon_1.y" here would be "y" from B1 unioned to "y" from B2. > > Anyway, SQLAlchemy is very quick to wrap up a series of rows in a > subquery, applying an alias to it, since that syntax works the most > consistently across not only all backends but across a really wide range of > scenarios. > > > > > -- 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/-/g5juNMWd4moJ. 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.