> here are also "wildcard" keys but
I don't think those cover exactly the use case you're trying to do,
which is basically "Order.items everywhere in the query"
If I uncomment the two commented out lines in my test, I can do
session.query(User) and it is able to properly handle the join regardless
of where it occurs. Is there not a way to make the Load() system behave the
same way?
user = relationship(User, backref=backref('orders', lazy='subquery'))
...
order = relationship(Order, backref=backref('items', lazy='joined'))
will work. Both are operating on a specific relationship, but when I try
query = Query(User).options(
Load(User).subqueryload('orders'),
Load(Order).joinedload('items'),
)
It is unable to determine that Load(User).subqueryload('orders') is
referring to User.orders? Am I just misunderstanding the meaning of the arg
being passed to Load()?
Does the arg refer to which base entity is being queried, rather than the
current 'node' in the chain?
If I use Query(User), will it only load strategies that are created using
Load(User)?
And when a subquery is issued, does it still retain the same base entity? I
thought maybe a subquery for orders would have 'Order' as the base, and
trigger the load strategies which were set with Load(Order), but they
aren't touch at all.
Thanks for the quick reply, you're totally awesome (as always).
On Wednesday, February 15, 2017 at 2:34:47 PM UTC-8, Mike Bayer wrote:
>
>
>
> On 02/15/2017 04:39 PM, Gerald Thibault wrote:
> > I have 3 classes, like so:
> >
> > |
> > class User(Base):
> > __tablename__ = 'users'
> > id = Column(Integer, primary_key=True)
> >
> > class Order(Base):
> > __tablename__ = 'orders'
> > id = Column(Integer, primary_key=True)
> > user_id = Column(Integer, ForeignKey(User.id))
> > user = relationship(User, backref=backref('orders'))
> > #user = relationship(User, backref=backref('orders', lazy='subquery'))
> >
> > class Item(Base):
> > __tablename__ = 'items'
> > id = Column(Integer, primary_key=True)
> > order_id = Column(Integer, ForeignKey(Order.id))
> > order = relationship(Order, backref=backref('items'))
> > #order = relationship(Order, backref=backref('items', lazy='joined'))
> > |
> >
> > The commented out variations of the relationships are the working ones,
> > which allow me to do
> >
> > |
> > results = session.query(User).all()
> > |
> >
> > and have it grab the users, then the join between the orders and items
> > in a second subquery.
> >
> > I have been trying to reproduce this behavior using the per-entity
> > default loading strategies described
> > at
> http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#per-entity-default-loading-strategies
>
> > but have been unable to get the same behavior.
> >
> > This works:
> > |
> > session.query(User).options(
> > Load(User).subqueryload('orders').joinedload('items')
> > )
> > |
> >
> > But I am trying to build something programmatically, so I'm hoping to
> > avoid the chaining. What I want to work, but does not, is this:
> >
> > |
> > session.query(User).options(
> > Load(User).subqueryload('orders'),
> > Load(Order).joinedload('items'),
>
> Well the "chaining" is needed to the degree that it matches the "paths"
> being loaded. So here's some other ways that would work:
>
> query(User).options(
> Load(User).subqueryload('orders'),
> Load(User).defaultload('orders').joinedload('items')
> )
>
>
> query(User).options(
> subqueryload("orders"),
> joinedload("orders.items")
> )
>
>
> But you can see, there's no way to refer to Order without qualifying
> that this is coming from the User.orders relationship. Because your
> query could be referring to Order in any number of ways simultaneously,
> the paths have to match up, the paths here being:
>
> User
> User/orders
> User/orders/items
>
>
> >
> > Is it possible to use the Load(...) system to replicate the behavior of
> > the lazy attribute provided to a relationship, as in, when the query is
> > constructed, it behaves _exactly_ as if the value provided to Load(...)
> > was actually set as the 'lazy' keyword of the attribute?
>
> the loader options that you send to options() are a mirror of the
> arguments you send to the "lazy" keyword on relationship, but the
> options need to know what relationship() they're referring towards, so
> that's why the path thing is there. There are also "wildcard" keys but
> I don't think those cover exactly the use case you're trying to do,
> which is basically "Order.items everywhere in the query"; I can see how
> that would be possible but I don't believe there's a direct route to
> that without inspecting the mappings.
>
> As far as the "paths", there are ways to progammatically figure them
> out. If you had a User class and said, "give me all the relationships
> that refer to Order", this can be done using the
> inspect(User).relationships collection. The information is there
> you'd just need to traverse it.
>
> A little tricky so here's a POC:
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base
>
> Base = declarative_base()
>
>
> class User(Base):
> __tablename__ = 'users'
> id = Column(Integer, primary_key=True)
>
>
> class Order(Base):
> __tablename__ = 'orders'
> id = Column(Integer, primary_key=True)
> user_id = Column(Integer, ForeignKey(User.id))
> user = relationship(User, backref=backref('orders'))
>
>
> class Item(Base):
> __tablename__ = 'items'
> id = Column(Integer, primary_key=True)
> order_id = Column(Integer, ForeignKey(Order.id))
> order = relationship(Order, backref=backref('items'))
>
>
> def find_all_order(query):
> options = []
> seen = set()
>
> def _add_order(ent, path=()):
> ent = inspect(ent)
> if ent is inspect(Order):
> print("Appending joinedload(%s.items" % (".".join(path), ))
> options.append(
> joinedload(
> "%s.items" % (".".join(path), )
> )
> )
>
> for rel in ent.relationships:
> if rel in seen:
> continue
> seen.add(rel)
> _add_order(rel.mapper, path + (rel.key, ))
>
> for desc in query.column_descriptions:
> _add_order(desc['entity'])
>
> return query.options(*options)
>
> e = create_engine("sqlite://", echo=True)
> Base.metadata.create_all(e)
>
> s = Session(e)
>
> s.add_all([
> User(orders=[
> Order(items=[Item(), Item()]),
> Order(items=[Item()])
> ]),
> User(orders=[Order(items=[Item()])])
> ])
> s.commit()
>
> q = s.query(User).options(subqueryload(User.orders))
> q = find_all_order(q)
>
> for user in q:
> print user
> for order in user.orders:
> print order
> for item in order.items:
> print item
>
>
>
> in the output we can see the subqueryload for user->orders that includes
> joinedload for items:
>
> SELECT users.id AS users_id
> FROM users
> 2017-02-15 17:32:37,260 INFO sqlalchemy.engine.base.Engine ()
> 2017-02-15 17:32:37,263 INFO sqlalchemy.engine.base.Engine SELECT
> orders.id AS orders_id, orders.user_id AS orders_user_id,
> anon_1.users_id AS anon_1_users_id, items_1.id AS items_1_id,
> items_1.order_id AS items_1_order_id
> FROM (SELECT users.id AS users_id
> FROM users) AS anon_1 JOIN orders ON anon_1.users_id = orders.user_id
> LEFT OUTER JOIN items AS items_1 ON orders.id = items_1.order_id ORDER
> BY anon_1.users_id
> 2017-02-15 17:32:37,263 INFO sqlalchemy.engine.base.Engine ()
> <__main__.User object at 0x7fa3d9d078d0>
> <__main__.Order object at 0x7fa3d9cb0bd0>
> <__main__.Item object at 0x7fa3d9cb0cd0>
> <__main__.Item object at 0x7fa3d9cb0d90>
> <__main__.Order object at 0x7fa3d9cb0c50>
> <__main__.Item object at 0x7fa3d9cb0f10>
> <__main__.User object at 0x7fa3d9d07950>
> <__main__.Order object at 0x7fa3d9cb0f90>
> <__main__.Item object at 0x7fa3d9cbe110>
>
>
>
>
>
> I poked around
> > in the source a bit, and with my failed attempt, JoinedLoader.__init__
> > is never even called. The subquery is issued, but is not joined against
> > anything. I'm not sure how to make this work.
> >
> > --
> > SQLAlchemy -
> > The Python SQL Toolkit and Object Relational Mapper
> >
> > http://www.sqlalchemy.org/
> >
> > To post example code, please provide an MCVE: Minimal, Complete, and
> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> > description.
> > ---
> > 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 <javascript:>
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
