On Jun 9, 2013, at 7:41 PM, Gerald Thibault <dieselmach...@gmail.com> wrote:
> I have a polymorphic class with a self-referential parent->children
> relationship. In an ideal world, I'd be able to do:
>
> session.query(Item) \
> .options(joinedload('children')) \
> .all()
>
> and get the info I need, but I'm on mysql, so this isn't doable due to
> sqlalchemy insisting on using performance-destroying unbounded subqueries
> even when the db cannot possibly support them. This one issue has caused me
> more headaches and man-hours than I could possibly describe, as everything we
> do has to be doublechecked to make sure we're not accidentally asking
> sqlalchemy to do a join that it can't handle in an efficient manner.
well then I have good news for you, this issue has been resolved in SQLAlchemy
0.9, just as of the last few days. You will find a full description of the
improvement at
http://docs.sqlalchemy.org/en/latest/changelog/migration_09.html#many-join-and-left-outer-join-expressions-will-no-longer-be-wrapped-in-select-from-as-anon-1
and the code is available for your download and beta testing right now.
When your query is run on 0.9 against any database *other* than SQLite (whos
non-support of parenthesized joins is the entire reason this right-subquery
thing), you get this query:
SELECT items.id AS items_id, items.item_type AS items_item_type,
items.parent_id AS items_parent_id, items_ext.id AS items_ext_id,
items_ext.custom_name AS items_ext_custom_name, items_1.id AS items_1_id,
items_1.item_type AS items_1_item_type, items_1.parent_id AS items_1_parent_id,
items_ext_1.id AS items_ext_1_id, items_ext_1.custom_name AS
items_ext_1_custom_name
FROM items LEFT OUTER JOIN items_ext ON items.id = items_ext.id LEFT OUTER JOIN
(items AS items_1 LEFT OUTER JOIN items_ext AS items_ext_1 ON items_1.id =
items_ext_1.id) ON items.id = items_1.parent_id
the word "SELECT" appears only once.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
