Filtering across related models with SQLAlchemy core API (using AIOPG)

Question

I'm trying to do something I'm fairly sure is simple using AIOPG, which can use sqlalchemy's core API. My SQL is not great, so that's where I'm falling down here.

models

class School(Base):
    __tablename__ = 'schools'

    id = Column(Integer, primary_key=True, nullable=False)


sa_school = School.__table__


class SubjectCategory(Base):
    __tablename__ = 'subject_categories'

    id = Column(Integer, primary_key=True, nullable=False)
    name = Column(String(63))


sa_subject_category = SubjectCategory.__table__


class Subject(Base):
    __tablename__ == 'subjects'

    id = Column(Integer, primary_key=True, nullable=False)
    name = Column(String(63))
    category = Column(Integer, ForeignKey('subject_categories.id'), nullable=False)


sa_subject = Subject.__table__


class SchoolSubject(Base):
    __tablename__ = 'school_subjects'

    id = Column(Integer, primary_key=True, nullable=False)
    school = Column(Integer, ForeignKey('schools.id'), nullable=False)
    subject = Column(Integer, ForeignKey('subjects.id'), nullable=False)


sa_school_subject = SchoolSubject.__table__

So I'm just trying to get all schools which teach subjects that have a certain subject_category ID.

Currently I have:

from sqlalchemy import select, join

school_c = sa_school.c
school_subj_c = sa_school_subject.c


async def get_schools(subject=None, subj_cat=None)
    query = select(
        [school_c.id, school_c.name]
    ).select_from(sa_school.join(sa_school_subject)
    if subj_cat:
        # Then I need to filter where a school_subj.subject.category == subj_cat
        pass
    elif subject:
        query = query.where(sa_school_subject.c.subject == subj)

    cur = await conn.execute(query)
    return [dict(b) async for b in cur]

Answer 1

After a bit of faffing, I can simply do:

query = select(
    [school_c.id, school_c.name]
).select_from(sa_school.join(sa_school_subject.join(sa_subject)))