SQLAlchemy filter children

Question

I have two SQLAlchemy models, a parent and a child, with a relationship between them, and I would like to query the parent table and filter the children. An example that doesn't work:

from sqlalchemy import (
    Column,
    ForeignKey,
    Integer,
    Text,
    UniqueConstraint,
    select,
    create_engine,
)
from sqlalchemy.orm import relationship, Session, joinedload

from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()
engine = create_engine("sqlite://") # In memory DB for example, I'm using MySQL in reality


class Parent(Base):
    __tablename__ = "parent"

    id = Column(Integer, primary_key=True, autoincrement=True)
    text = Column(Text)

    children = relationship("Child", back_populates="parent")


class Child(Base):
    __tablename__ = "child"

    id = Column(Integer, primary_key=True, autoincrement=True)
    parent_id = Column(Integer, ForeignKey("parent.id"), nullable=False)
    name = Column(Text)

    question = relationship("Parent", back_populates="children")


Base.metadata.create_all(engine)

with Session(engine) as db:
    p = Parent(
        text="a",
        children=[
            Child(name="foo"),
            Child(name="bar"),
       ]
    )
    db.add(p)
    db.commit()

with Session(engine) as db:
    parent = db.execute(
        select(Parent)
        .join(Child)
        .where(Child.name == "foo")
        .options(joinedload(Parent.children))
    ).scalars().first()

    print(parent.children)  # I get both children here

What I would like is to somehow filter Parent.children so I only get the child with name == "foo" (in my example, there might be thousands of children, so I'd like to filter in the database).

I know I can "reverse" the query, with select(Child)... and getting the relevant data, but if possible, I'd like to retain the object structure of one top level Parent object with a filtered list of children. Is this somehow possible?

Does this answer your question? [SQLAlchemy query filter on child attribute](https://stackoverflow.com/questions/40524749/sqlalchemy-query-filter-on-child-attribute) — Yaakov Bressler, Oct 15 '22 at 17:00
No, that question is about filtering the parent by a child value. I have the right parent element(s), but need the returned relationship list to be filtered. — M.O., Oct 16 '22 at 00:40

score 2 · Accepted Answer · answered Oct 16 '22 at 14:35

I figured it out, it’s described in the documentation here. So, what I needed was

from sqlalchemy.orm import contains_eager

with Session(engine) as db:
    parent = db.execute(
        select(Parent)
        .join(Child)
        .where(Child.name == "foo")
        .options(contains_eager(Parent.children))
        .execution_options(populate_existing=True)
    ).scalars().first()

    print(parent.children)  # Here I only get the one child

SQLAlchemy filter children

1 Answers1