> On Nov 24, 2014, at 12:31 PM, Michael Bayer <mike...@zzzcomputing.com> wrote:
>
>>
>> On Nov 24, 2014, at 1:00 AM, Victor Reichert <vfr...@gmail.com
>> <mailto: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
<http://docs.sqlalchemy.org/en/rel_0_9/orm/inheritance.html#eager-loading-of-specific-or-polymorphic-subtypes>
>
> 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
>> <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()
>
>
>
>>
>> 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:
>>
>>> On Oct 26, 2014, at 12:07 AM, Victor Reichert <vfr...@gmail.com <>> wrote:
>>>
>>> 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
>>>
>>> <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
>>> <http://pastie.org/9676017>
>>> Is there a loader strategy that would work for my situation or a work
>>> around for the AssociationProxy error?
>>>
>>>
>>
>> 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 <http://salesperson.id/> AS salesperson_id,
>> salesperson.name <http://salesperson.name/> AS salesperson_name,
>> address_association_1.id <http://address_association_1.id/> AS
>> address_association_1_id, address_association_1.discriminator AS
>> address_association_1_discriminator, address_1.id <http://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 <http://customer_1.id/> AS
>> customer_1_id, customer_1.name <http://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
>> <http://salesperson.id/> = customer_1.sales_person_id LEFT OUTER JOIN
>> address_association AS address_association_1 ON address_association_1.id
>> <http://address_association_1.id/> = customer_1.address_association_id LEFT
>> OUTER JOIN address AS address_1 ON address_association_1.id
>> <http://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 <http://customer.id/> AS customer_id, customer.name
>> <http://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
>>> <http://groups.google.com/group/sqlalchemy>.
>>> For more options, visit https://groups.google.com/d/optout
>>> <https://groups.google.com/d/optout>.
>>
>>
>> --
>> 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
>> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
>> To post to this group, send email to sqlalchemy@googlegroups.com
>> <mailto:sqlalchemy@googlegroups.com>.
>> Visit this group at http://groups.google.com/group/sqlalchemy
>> <http://groups.google.com/group/sqlalchemy>.
>> For more options, visit https://groups.google.com/d/optout
>> <https://groups.google.com/d/optout>.
>
>
> --
> 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
> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
> To post to this group, send email to sqlalchemy@googlegroups.com
> <mailto:sqlalchemy@googlegroups.com>.
> Visit this group at http://groups.google.com/group/sqlalchemy
> <http://groups.google.com/group/sqlalchemy>.
> For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>.
--
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.
For more options, visit https://groups.google.com/d/optout.
