Re: [sqlalchemy] eager load polymorphic relation

[Michael Bayer]

Is there a reason you can't use subquery eager loading?  That will at least use an inner join, but its true when sqla does an auto join from one entity to another, and the other is itself a join, it needs to parenthesize the target.   And not every database historically has supported that so at the moment it needs to be a subquery.   

Sent from my iPhone

On May 15, 2013, at 6:51 PM, me <m...@aitmp.com> wrote:

using python 2.7, sqla 0.7.9, postgresql 9.1

i'm trying to eager load a relationship to a polymorphic target (the target
uses joined table inheritance).

here is example:

    http://pastebin.com/xSiHS4QW

and this is output:

    SELECT
    ...
    FROM container
    LEFT OUTER JOIN root AS root_1 ON root_1.id = container.root_id

no type1, not type2?

looks like with_polymorphic mappig arg controls that:

    http://pastebin.com/FfmJSZWc

and this is output:

    SELECT ...
    FROM container
    LEFT OUTER JOIN (
            SELECT ...
            FROM root
            LEFT OUTER JOIN type1 ON root.id = type1.id
            LEFT OUTER JOIN type2 ON root.id = type2.id
        ) AS anon_1 ON anon_1.root_id = container.root_id

but i thought i'd get:

    SELECT ...
    FROM container
    LEFT OUTER JOIN root AS root_1 ON root_1.id = container.root_id
    LEFT OUTER JOIN type1 ON root.id = type1.id
    LEFT OUTER JOIN type2 ON root.id = type2.id

this is not problem for small sets but for bulk ops either lazy loading or
subselect are much slower.

it looks like many have run into this. i tried using contains_eager but though
that joins how i want it still does not load the entire target object (just base):

    c = Container
        .query
        .join(Container.root)
        .outerjoin(type1, root.c.id == type1.c.id)
        .outerjoin(type2, root.c.id == type2.c.id)
        .options(contains_eager(Container.root))
        .first()
    c.root.field2 # field2 was not mapped so will do SELECT

is there a way to build a query that eagerly loads the whole entity?

    q = Session.query ...
    c = q.first()
    c.root.field2 # already loaded so not SELECT

thanks

--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@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.