so_67578639.py

# Complete code for the answer at https://stackoverflow.com/a/67578848
from sqlalchemy import ForeignKey, String
from sqlalchemy import create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session
from sqlalchemy.orm import mapped_column, relationship
from sqlalchemy.orm import contains_eager

class Base(DeclarativeBase):
    pass

class Child(Base):
    __tablename__ = 'child'
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(40))
    toys = relationship('Toy')

class Toy(Base):
    __tablename__ = 'toy'
    id: Mapped[int] = mapped_column(primary_key=True)
    color: Mapped[str] = mapped_column(String(40))
    child_id = mapped_column(ForeignKey('child.id'))

children = [line.split()[:2] for line in """
1   First
2   Second
""".splitlines() if line.strip()]

toys = [line.split()[:3] for line in """
1   Blue     1
2   Red      1
3   Orange   2
4   Red      2
""".splitlines() if line.strip()]

engine = create_engine('sqlite://')
Base.metadata.create_all(engine)

with Session(engine) as session:
    session.add_all(
        [Child(id=int(i), name=j) for i, j in children])
    session.add_all(
        [Toy(id=int(i), color=j, child_id=int(k)) for i, j, k in toys])
    session.commit()

# enable logging to output the generated queries for the answer
import logging
logging.basicConfig()
logging.getLogger("sqlalchemy.engine").setLevel(logging.DEBUG)

with Session(engine) as session:
    query = (select(Child)
        .join(Child.toys)
        .filter(Toy.color == 'Red')
        .options(contains_eager(Child.toys))
    )
    filtered_children = session.scalars(query).unique().all()

for child in filtered_children:
    for toy in child.toys:
        print(f'{child.id} {child.name} {toy.id} {toy.color}')