Sqlalchemy 关系：按连接表排序子项

Question

我有以下关系： acourse有很多course_instances，每个都course_instance属于 a semester。这是一组大大简化的模型：class Course(db.Model):    __tablename__ = "courses"    id = Column(Integer, primary_key=True)    course_instances = relationship("CourseInstance", back_populates="course")class CourseInstance(db.Model):    __tablename__ = "course_instances"    id = Column(Integer, primary_key=True)    course_id = Column(Integer, ForeignKey("courses.id"))    semester_id = Column(Integer, ForeignKey("semesters.id"))    course = relationship("Course", back_populates="course_instances")    semester = relationship("Semester", back_populates="course_instances")class Semester(db.Model):    __tablename__ = "semesters"    id = Column(Integer, primary_key=True)    sort_ix = Column(Integer)    course_instances = relationship("CourseInstance", back_populates="semester")我经常在什么时候查询一门课程并将所有实例包含在joinedload/contains_eager/selectinload. 这些实例应始终按sort_ix每个实例所在学期的列排序。有没有办法在课程模型级别指定此默认顺序？是这样的：course_instances = relationship(    "CourseInstance",    back_populates="course",    order_by="CourseInstance.semester.sort_ix")我唯一能想到的就是为CourseInstancelike添加一个重复的列semester_sort_ix。

Answer 1

用一组不同的关系来解决这个问题。这是解决方案：

class CourseInstance(db.Model):

    semester_sort_ix = deferred(select([Semester.sort_ix]).where(Semester.id == semester_id))

class Course(db.Model):

    course_instances = relationship(

        "CourseInstance", back_populates="course", order_by="desc(CourseInstance.semester_sort_ix)",

    )