Sqlalchemy relationship: Order children by joined table

user_195530 0
Viewed 230

I have the following relationship: a course has many course_instances and each course_instance belongs to a semester.

Here's a greatly simplified set of models:

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

I often when to query for a course and include all of instances with joinedload/contains_eager/selectinload. These instances should always be sorted by the sort_ix column of each instance's semester.

Is there any way to specify this default ordering on the Course-model level? Something like this:

course_instances = relationship(
    "CourseInstance",
    back_populates="course",
    order_by="CourseInstance.semester.sort_ix"
)

The only thing I can think of is adding a duplicate column to CourseInstance like semester_sort_ix.

sqlalchemy python
1 Answers

Sort of a dup of this issue with a different set of relationships. Here is the solution:

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