Deferred Eager Loading

[Randy Syring]

Mike,

Any interest in supporting deferred eager loading?  That is, I want to have support for subquery or selectin relationship loading, but I only want queries to be issued of the collections are actually used.  That is, I want to defer the query for collections until they are actually used.  Example:

import sqlalchemy as sa

import sqlalchemy.ext.declarative as sadec

import sqlalchemy.orm as saorm

engine = sa.create_engine('sqlite:///')

meta = sa.MetaData()

Base = sadec.declarative_base(metadata=meta)

Session = saorm.sessionmaker(bind=engine)

sess = Session()

class MethodsMixin:

@classmethod

def add(cls, **kwargs):

obj = cls(**kwargs)

sess.add(obj)

return obj

class Blog(Base, MethodsMixin):

__tablename__ = 'blogs'

id = sa.Column(sa.Integer, primary_key=True)

# lazy = True / 'joined' are the same

comments = sa.orm.relationship(lambda: Comment, lazy='subquery_deferred', backref='blog')

class Comment(Base, MethodsMixin):

__tablename__ = 'comments'

id = sa.Column(sa.Integer, primary_key=True)

blog_id = sa.Column(sa.ForeignKey(Blog.id, ondelete='cascade'), nullable=False)

b1 = Blog.add()

b2 = Blog.add()

Comment.add(blog=b1)

Comment.add(blog=b1)

Comment.add(blog=b2)

Comment.add(blog=b2)

sess.commit()

assert sess.query(Blog).count() == 2

assert sess.query(Comment).count() == 4

# Would result in one SQL query. Because Blog.comments is not accessed, the subquery is never

# issued.

for b in sess.query(Blog):

assert b.id

# Would result in two SQL Queries. One issue as soon as the query is ran, to get all blog

# records.

for b in sess.query(Blog):

# The second query is issued the first time .comments is accessed, to load all comments for

# all blog records in the parent query. Therefore, we avoid n+1 queries, but only issue

# eager queries when collections are actually used.

assert b.comments

In essence, I'd like to have a default way of configuring relationships that avoids n+1 queries but only issues SQL if the collections are actually used.

Thoughts?

Thanks.

Randy Syring
Husband | Father | Redeemed Sinner

"For what does it profit a man to gain the whole world
and forfeit his soul?" (Mark 8:36 ESV)

[Mike Bayer]

Hi Randy -

I believe Hibernate has (or had?) a similar feature to this. I'm
not against it but I think there's a *lot* of complexity to it and I
don't have the development resources on the horizon to try to get
something like this working. I'm still fixing bugs in the existing
eager load implementations. "select in" loading would be the only
candidate for this, as we can at least scan through the Session and
only load for primary keys that are still referenced within the
Session. That we now have "select in" loading makes this a little
bit more possible / feasible. But the 1.3 milestone still has about
30 more issues than I want it to have and I've been pushing dozens of
issues out to later milestones and I'm completely overwhelmed with
what I have right now.

- mike

>
> Thanks.
>
>
> Randy Syring
> Husband | Father | Redeemed Sinner
>
> "For what does it profit a man to gain the whole world
> and forfeit his soul?" (Mark 8:36 ESV)
>

> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy-devel" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy-dev...@googlegroups.com.
> To post to this group, send email to sqlalche...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy-devel.
> For more options, visit https://groups.google.com/d/optout.