Ah, ok. I thought defaultload() meant use whatever was originally specified
in the relationship(). That helps a lot!
On Friday, October 26, 2018 at 12:34:53 PM UTC-4, Mike Bayer wrote:
>
> On Fri, Oct 26, 2018 at 12:10 PM Alex Wang <aw16...@gmail.com
> <javascript:>> wrote:
> >
> > I ended up needing one more layer of selectinload(Node.direct_children),
> but it got rid of all the lazy loads. Thanks for the suggestion; that's
> definitely a lot better than what I initially had. I was thinking that I
> needed to specify eager loading separately, but now that I think more about
> your suggestion the redundancy becomes clearer.
> >
> > There's one more layer of complications --- if I need to get multiple
> entries off of each node, is there a clean-ish way of eagerly loading those
> attributes in addition to the entries mentioned in my original post? Or
> should I just write a function like what you suggested and use that?
>
> if you want to keep specifying the same path and add more attributes,
> defaultload() helps with that:
>
> selectinload(Foo.bar).selectinload(Bar.attribute1)
> defaultload(Foo.bar).selectinload(Bar.attribute2)
> defaultload(Foo.bar).selectinload(Bar.attribute3)
>
> above, the "defaultload" just means, "here is a token in the path but
> don't change the existing loading options on this token".
>
>
>
>
>
> >
> > And just to make sure, selectinloads should be the right choice for this
> kind of nested collection, right?
> >
> > Thanks!
> >
> > On Thursday, October 25, 2018 at 6:54:53 PM UTC-4, Mike Bayer wrote:
> >>
> >> On Thu, Oct 25, 2018 at 4:21 PM Alex Wang <aw16...@gmail.com> wrote:
> >> >
> >> > Hi all!
> >> >
> >> > I'm trying to write a small script to interface with a database
> controlled by a third-party application, and I'm not sure the way I set up
> eager loading is right.
> >> >
> >> > The code I have looks something like the following:
> >> >
> >> > from sqlalchemy.ext.declarative import declarative_base
> >> > from sqlalchemy.dialects.mssql import FLOAT, NVARCHAR,
> UNIQUEIDENTIFIER
> >> > from sqlalchemy.orm import foreign, relationship, remote
> >> > from sqlalchemy.schema import Column
> >> > Base = declarative_base()
> >> > class Node(Base):
> >> > NodeID = Column(NVARCHAR(length=65), primary_key=True,
> nullable=False)
> >> > ParentNodeID = Column(NVARCHAR(length=65), nullable=True)
> >> > direct_children = relationship('Node', primaryjoin=(NodeID ==
> remote(foreign(ParentNodeID))))
> >> > entries = relationship('Entry', primaryjoin=('Node.NodeID ==
> remote(foreign(Entry.NodeID))')
> >> >
> >> > def node_and_all_children(self):
> >> > result = [self]
> >> > for child in self.direct_children:
> >> > result += child.node_and_all_children()
> >> > return result
> >> >
> >> > def cost(self):
> >> > entries = [e for p in self.node_and_all_children() for e in
> p.entries]
> >> > return sum(e.Value1 * e.Value2 for e in entries)
> >> >
> >> > class Entry(Base):
> >> > TEID = Column(UNIQUEIDENTIFIER, primary_key=True, nullable=False)
> >> > NodeID = Column(NVARCHAR(length=65), nullable=False)
> >> > Value1 = Column(FLOAT, nullable=True)
> >> > Value2 = Column(FLOAT, nullable=True)
> >> >
> >> > I want to write something like this:
> >> >
> >> > def get_costs(session, node_ids: List[str]):
> >> > nodes =
> session.query(Node).filter(Node.NodeID.in_(node_ids)).all()
> >> > return {n.NodeID: n.cost() for n in nodes}
> >> >
> >> > From what I understand, this results in an N+1-ish access pattern. I
> can eagerly load the children easily enough (I think I can guarantee <= 3
> levels of children, so three selectinload() calls are hopefully enough?):
> >> >
> >> > def get_costs(session, node_ids: List[str]):
> >> > nodes = (
> >> > session.query(Node)
> >> > .options(
> >> > selectinload(Node.direct_children)
> >> > .selectinload(Node.direct_children)
> >> > .selectinload(Node.direct_children)
> >> > )
> >> > .filter(Node.NodeID.in_(node_ids))
> >> > .all()
> >> > )
> >> > return {n.NodeID: n.cost() for n in nodes}
> >> >
> >> > It's here that I get stuck, though. This eagerly loads the children,
> but doesn't eagerly load each node's entries, which results in a query
> being sent to the database for each child. I can specify that entries
> should be eagerly loaded at each level:
> >> >
> >> > def get_costs(session, node_ids: List[str]):
> >> > nodes = (
> >> > session.query(Node)
> >> > .options(
> >> > selectinload(Node.direct_children)
> >> > .selectinload(Node.direct_children)
> >> > .selectinload(Node.direct_children),
> >> > selectinload(Node.entries),
> >> >
> selectinload(Node.direct_children).selectinload(Node.entries),
> >> >
> selectinload(Node.direct_children).selectinload(Node.direct_children).selectinload(Node.entries)
>
>
> >> > )
> >> > .filter(Node.NodeID.in_(node_ids))
> >> > .all()
> >> > )
> >> > return {n.NodeID: n.cost() for n in nodes}
> >> >
> >> > But this feels pretty gross, and I'm hoping there is a better way.
> >>
> >> you don't have to specify selectinload() twice like that, you can say:
> >>
> >> selectinload(Node.entries),
> >> selectinload(Node.direct_children).selectinload(Node.entries),
> >>
> selectinload(Node.direct_children).selectinload(Node.direct_children).selectinload(Node.entries)
>
>
> >>
> >> if you are using query.options(), that's the API. if you put the
> >> selectinload on your relationship, you can use join_depth:
> >>
> >> direct_children = relationship(Node, lazy="selectin", join_depth=3)
> >>
> >> The options form of this doesn't have a "join_depth" option right now
> >> but you can certainly make yourself a function, something like this:
> >>
> >> def selectinload_selfref(attr, depth):
> >> opt = selectinload(attr)
> >> for i in range(depth - 1):
> >> opt = opt.selectinload(attr)
> >> return opt
> >>
> >>
> >>
> >>
> >> >
> >> > Is there a cleaner way of specifying that entries should be eagerly
> loaded for each level of children? Or is there a totally different query
> structure that would be even better?
> >> >
> >> > Thanks!
> >> >
> >> > --
> >> > 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.
> >> > To post to this group, send email to sqlal...@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+...@googlegroups.com <javascript:>.
> > To post to this group, send email to 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.
