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

Question

我有以下关系：acourse有许多course_instances，每个course_instance都属于asemester

以下是一组大大简化的模型：

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"
)

我唯一能想到的就是在CourseInstance中添加一个重复的列，比如semester_sort_ix

Answer 1

有点像一个具有不同关系集的this issue的dup。以下是解决方案：

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)",
    )