Eager Loading of AssociationProxy (Generic Association with Discriminator on Association)

[Victor Reichert]

Hi,

I am following the "Generic Association with Discriminator on Association" example at:

http://docs.sqlalchemy.org/en/latest/_modules/examples/generic_associations/discriminator_on_association.html

However, I would like to eager load the customer.addresses in a query like

eager_sales_persons = session.query(SalesPerson).options(joinedload(SalesPerson.customers).joinedload(Customer.addresses))

#with SalesPerson being a class I added with a relationship to customers.

However, the above statement raises:  'AssociationProxy' object has no attribute 'property'

I tried eager_sales_persons = session.query(SalesPerson).options(joinedload(SalesPerson.customers).joinedload(Customer.address_association).joinedload(AddressAssociation.addresses)).all()

however, it would emit SQL for customer.addresses.

I have made a pastie with my code at: http://pastie.org/9676017

Is there a loader strategy that would work for my situation or a work around for the AssociationProxy error?

Thank you so much for your help :)

~Victor

[Michael Bayer]

the second query is the right one, as currently there isn’t integration between an association proxy attribute and loader options, meaning, you have to state the joinedload() in terms of the actual relationship() as you are doing.

However I’m not seeing the problem:

if we load as :

session.query(SalesPerson).options(joinedload(SalesPerson.customers).joinedload(Customer.address_association).joinedload(AddressAssociation.addresses)).all()

which you can also state like this:

session.query(SalesPerson).options(joinedload(SalesPerson.customers).joinedload("address_association").joinedload(“addresses")).all()

setting echo=True on create_engine(), the main query is:

SELECT salesperson.id AS salesperson_id, salesperson.name AS salesperson_name, address_association_1.id AS address_association_1_id, address_association_1.discriminator AS address_association_1_discriminator, address_1.id AS address_1_id, address_1.association_id AS address_1_association_id, address_1.street AS address_1_street, address_1.city AS address_1_city, address_1.zip AS address_1_zip, customer_1.id AS customer_1_id, customer_1.name AS customer_1_name, customer_1.sales_person_id AS customer_1_sales_person_id, customer_1.address_association_id AS customer_1_address_association_id 

FROM salesperson LEFT OUTER JOIN customer AS customer_1 ON salesperson.id = customer_1.sales_person_id LEFT OUTER JOIN address_association AS address_association_1 ON address_association_1.id = customer_1.address_association_id LEFT OUTER JOIN address AS address_1 ON address_association_1.id = address_1.association_id

so in the FROM we have:   salesperson -> customer -> address_association -> address

that’s correct.  you’ll note three joinedload() calls, three links (->).

then as the iteration proceeds, the sample calls upon address.parent.  This emits this query for two entries:

SELECT customer.id AS customer_id, customer.name AS customer_name, customer.sales_person_id AS customer_sales_person_id, customer.address_association_id AS customer_address_association_id 

FROM customer 

WHERE ? = customer.address_association_id

not sure if that’s the query you’re referring to.  That’s Address.parent, which is a proxy to CustomerAddressAssociation.parent, which is emitting a lazy load.   As this is the non-FK side of a one-to-one, that’s also correct.  A one-to-one is a special case of a one-to-many, basically, uselist=False means, fetch a one-to-many collection, but only deal with the first result.  CustomerAddressAssociation.parent is the backref of the Customer.address_association many-to-one that’s stated in the joinedload().

if this were a non-generic mapping where Customer.addresses were a one-to-many and Address.parent were a many-to-one, you wouldn’t get that extra load for Address.parent; a pure many-to-one is implicitly retrieved from the database and cached that way when the one-to-many side is loaded.  but in this case the “generic” mapping has flipped this around so that Customer->AddressAssoiation is a many-to-one.

Thank you so much for your help :)

~Victor

--
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.
For more options, visit https://groups.google.com/d/optout.

[Victor Reichert]

Thank you for your response, and double thank you for Sqlalchemy!

I was misunderstanding the second query.  I now see that the eager load is working properly.  Thanks!

So I know the limitations of my approach, it is possible to eager load the address.parent?

I've tried:

eager_addresses = session.query(Address).options(joinedload(Address.association).joinedload(Customer.assoc_cls.parent, Supplier.assoc_cls.parent)).all()

Where I've made the assoc_cls a class variable, the pastie with all the code is here: http://pastie.org/9677009

the eager_addresses SQL is:

2014-10-26 09:52:25,099 INFO sqlalchemy.engine.base.Engine SELECT address.id AS address_id, address.association_id AS address_association_id, address.street AS address_street, address.city AS address_city, address.zip AS address_zip, address_association_1.id AS address_association_1_id, address_association_1.discriminator AS address_association_1_discriminator 

FROM address LEFT OUTER JOIN address_association AS address_association_1 ON address_association_1.id = address.association_id

So, it's not joining to the customer and supplier tables.  Is there a way to join in the customer and supplier tables and make them eager loaded?

Thank you again :)

~Victor

[Victor Reichert]

I've taken another look at trying to eager load the address.parent.  Is it possible to do that?

I've tried a couple of ways, pasted a below.  My full code is at: https://gist.github.com/vfr292/a5939418285e4c8bd03b

eager_addresses = session.query(Address).options(joinedload(Address.parent))

#error sqlalchemy.exc.ArgumentError: mapper option expects string key or list of attributes

eager_addresses = session.query(Address).options(joinedload(Address.association).joinedload(Customer.assoc_cls.parent), joinedload(Address.association).joinedload(Supplier.assoc_cls.parent)).all()

#not joining to Customer or Supplier

eager_addresses = session.query(Address).outerjoin(Address.association).outerjoin(Customer.assoc_cls.parent).options(contains_eager(Address.parent)).outerjoin(Supplier.assoc_cls.parent).options(contains_eager(Address.parent).all())

#error sqlalchemy.exc.ArgumentError: mapper option expects string key or list of attributes

Any advice on how I can eager load address.parent would be much appreciated :)

Sincere thanks,

~Victor

On Sunday, October 26, 2014 3:06:13 AM UTC-7, Michael Bayer wrote:

[Michael Bayer]

On Nov 24, 2014, at 1:00 AM, Victor Reichert <vfr...@gmail.com> wrote:

I've taken another look at trying to eager load the address.parent.  Is it possible to do that?

Unfortuntately not really.     It should be in theory but I’m not able to work out an eager load that goes to both Customer and Supplier in terms of AddressAssociation at the same time.  I can get the query to render just fine but the eager-targeting logic at the moment doesn’t seem to know how to be told to go to two separate subclasses of a base class at the same time.  There’s probably improvements yet to be made in eager loading to support this case better, e.g. this is a bug, but I’ve wrestled with it for about an hour and I’m out of time on it for now, sorry.    Even if it works, the query is very unpleasant to look at  :)   If I get it working later I’ll send it out.

There’s also a bug in the example, add this, it will help a lot:

diff --git a/examples/generic_associations/discriminator_on_association.py b/examples/generic_associations/discriminator_on_association.py

index e03cfec..7bb04cf 100644

--- a/examples/generic_associations/discriminator_on_association.py

+++ b/examples/generic_associations/discriminator_on_association.py

@@ -84,6 +84,7 @@ class HasAddresses(object):

                         "%sAddressAssociation" % name,

                         (AddressAssociation, ),

                         dict(

+                            __tablename__=None,

                             __mapper_args__={

                                 "polymorphic_identity": discriminator

                             }

this is to override the automated __tablename__ thing in the example.   

I've tried a couple of ways, pasted a below.  My full code is at: https://gist.github.com/vfr292/a5939418285e4c8bd03b

the first query should be:

eager_sales_persons = session.query(SalesPerson).options(

    joinedload(SalesPerson.customers).

    joinedload(Customer.addresses.attr[0]).

    joinedload(Customer.addresses.attr[1])

).all()

[Michael Bayer]

On Nov 24, 2014, at 12:31 PM, Michael Bayer <mik...@zzzcomputing.com> wrote:

On Nov 24, 2014, at 1:00 AM, Victor Reichert <vfr...@gmail.com> wrote:

I've taken another look at trying to eager load the address.parent.  Is it possible to do that?

Unfortuntately not really.     It should be in theory but I’m not able to work out an eager load that goes to both Customer and Supplier in terms of AddressAssociation at the same time.  I can get the query to render just fine but the eager-targeting logic at the moment doesn’t seem to know how to be told to go to two separate subclasses of a base class at the same time.  There’s probably improvements yet to be made in eager loading to support this case better, e.g. this is a bug, but I’ve wrestled with it for about an hour and I’m out of time on it for now, sorry.    Even if it works, the query is very unpleasant to look at  :)   If I get it working later I’ll send it out.

OK here we go, the limitation is that the of_type() modifier is only recognized along a particular path once.   So to get over this we can use a with_polymorphic():

poly = with_polymorphic(

    AddressAssociation,

    [Customer.assoc_cls, Supplier.assoc_cls], aliased=True)

eager_addresses = session.query(Address).options(

    joinedload(Address.association.of_type(poly)).joinedload(

        poly.CustomerAddressAssociation.parent),

    joinedload(Address.association.of_type(poly)).joinedload(

        poly.SupplierAddressAssociation.parent),

)

this loads everything in one go.    this pattern is documented in terms of joined inheritance here: http://docs.sqlalchemy.org/en/rel_0_9/orm/inheritance.html#eager-loading-of-specific-or-polymorphic-subtypes

[Michael Bayer]

On Nov 24, 2014, at 5:16 PM, Michael Bayer <mik...@zzzcomputing.com> wrote:

On Nov 24, 2014, at 12:31 PM, Michael Bayer <mik...@zzzcomputing.com> wrote:

On Nov 24, 2014, at 1:00 AM, Victor Reichert <vfr...@gmail.com> wrote:

I've taken another look at trying to eager load the address.parent.  Is it possible to do that?

Unfortuntately not really.     It should be in theory but I’m not able to work out an eager load that goes to both Customer and Supplier in terms of AddressAssociation at the same time.  I can get the query to render just fine but the eager-targeting logic at the moment doesn’t seem to know how to be told to go to two separate subclasses of a base class at the same time.  There’s probably improvements yet to be made in eager loading to support this case better, e.g. this is a bug, but I’ve wrestled with it for about an hour and I’m out of time on it for now, sorry.    Even if it works, the query is very unpleasant to look at  :)   If I get it working later I’ll send it out.

OK here we go, the limitation is that the of_type() modifier is only recognized along a particular path once.   So to get over this we can use a with_polymorphic():

good news, i whacked that limitation in the latest master for 1.0.  so when 1.0 is released (in some months), you can do:

eager_addresses = session.query(Address).options(

    joinedload(

        Address.association.of_type(Customer.assoc_cls)).joinedload(

        Customer.assoc_cls.parent),

    joinedload(

        Address.association.of_type(Supplier.assoc_cls)).joinedload(

        Supplier.assoc_cls.parent),

)

this will behind the scenes build up that with_polymorphic() thing for you.

[Victor Reichert]

Thank you!  You are not an SQL alchemist, you are a SQL Wizard!  Thank you again!

For bonus points, an order by is possible on the discriminator like objects are returned consecutively:

poly = with_polymorphic(

    AddressAssociation,

    [Customer.assoc_cls, Supplier.assoc_cls], aliased=True)

eager_addresses = session.query(Address).options(

    joinedload(Address.association.of_type(poly)).joinedload(

        poly.CustomerAddressAssociation.parent),

    joinedload(Address.association.of_type(poly)).joinedload(

        poly.SupplierAddressAssociation.parent),

).order_by(poly.discriminator.desc(),  poly.id)

[Victor Reichert]

I should add the final version of my file ist at:  https://gist.github.com/vfr292/3330037cf5bc621d3d4b should anyone want to reference it.

Thank you again Mr. Bayer!