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+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 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.