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 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.
