How to get all rows in a many-to-many relation using an association_table in SQLAlchemy?

Question

I have the tables shown below and am trying to return the children in a single parent row so I can iterate over them without fetching the parent row.

sub = select(Parent.children).where(Parent.id == 1).subquery()
children = select(Child).where(Child.id.in_(sub))

What am I doing wrong?

source: https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html#many-to-many

association_table = Table(
    "association_table",
    Base.metadata,
    Column("left_id", ForeignKey("left_table.id")),
    Column("right_id", ForeignKey("right_table.id")),
)

class Parent(Base):
    __tablename__ = "left_table"

    id: Mapped[int] = mapped_column(primary_key=True)
    children: Mapped[List[Child]] = relationship(secondary=association_table)


class Child(Base):
    __tablename__ = "right_table"

    id: Mapped[int] = mapped_column(primary_key=True)

Answer 1

Rather than trying to go through Parent, use the association table directly:

with Session() as s:
    sub = sa.select(association_table.c.right_id).where(association_table.c.left_id == 1).subquery()
    children = sa.select(Child).where(Child.id.in_(sub))
    for child in s.scalars(children):
        print(child)

Alternatively, add a relationship to the child model

class Child(Base):
    ...
    parents: Mapped[List[Parent]] = orm.relationship(secondary=association_table, back_populates='children')

and use that to access the children:

with Session() as s:
    children = sa.select(Child).where(Child.parents.any(Parent.id == 1))
    for child in s.scalars(children):
        print(child)