8000 DisjointEagerLoading · sqlalchemy/sqlalchemy Wiki · GitHub
[go: up one dir, main page]
Skip to content

DisjointEagerLoading

Jump to bottom
Michael Bayer edited this page Sep 20, 2024 · 7 revisions

DisjointEagerLoading

The feature described in this entry is included in SQLAlchemy for simple cases using "select IN eager loading" described at https://docs.sqlalchemy.org/orm/loading_relationships.html .
This recipe can still be useful for special cases where only a specific SELECT for related items is desired

For special cases where a very specific kind of "SELECT" is desired for the collection of items, the general technique to load a set of objects, and related objects, and then to "knit" them together manually is as follows:

from itertools import groupby

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy.orm import attributes
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import relation
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

# parent


class Parent(Base):
    __tablename__ = "parent"
    id = Column(Integer, primary_key=True)
    data = Column(String)
    children = relation("Child", collection_class=set)


# child


class Child(Base):
    __tablename__ = "child"
    id = Column(Integer, primary_key=True)
    data = Column(String)
    parent_id = Column(Integer, ForeignKey("parent.id"))

    def __repr__(self):
        return "Child(%r)" % self.data


# setup
engine = create_engine("sqlite://", echo=True)
Base.metadata.create_all(engine)

session = sessionmaker(engine)()

# test data

session.add_all(
    [
        Parent(
            data="parent %d" % i,
            children=set(
                [Child(data="child %d parent %d" % (j, i)) for j in range(50)]
            ),
        )
        for i in range(100)
    ]
)
session.commit()

# query for Parent objects
q = session.query(Parent).filter(Parent.id.between(28, 39)).limit(12)

# load them
parents = q.all()

# create *any query desired* to load the child objects.  Here we will
# reuse the original query as a subquery.
subq = q.subquery()
child_q = session.query(Child).join(subq, subq.c.id == Child.parent_id)


# iterate the query and group into collections keyed by parent.id. this
# particular approach relies upon the objects being ordered by parent_id, but
# there are other approaches.
child_q = child_q.order_by(Child.parent_id)
children = dict(
    (k, list(v))
    for k, v in groupby(
        child_q,
        lambda x: x.parent_id,
    )
)

# piece together using set_committed_value

for p in parents:
    attributes.set_committed_value(p, "children", children.get(p.id, ()))
Clone this wiki locally
0