使用SQLAlchemy核心API对相关模型进行过滤(使用AIOPG)

Question

我正在尝试使用AIOPG (可以使用sqlalchemy的核心API )来做一些我确信很简单的事情。我的SQL不是很好，所以这就是我要下降的地方。

模型

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__

所以，我只是想得到所有的schools，它教的科目有一个特定的subject_category ID。

目前我有：

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

经过一段时间后，我可以简单地做：

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