How to get Marshmallow-Sqlalchemy to Sort by an Association Object Field?

Question

How can I get Marshmallow to sort the returned dataset––by a field in the association object (seat_index)?
The end result is to sort a bunch of classroom objects by class start time, and then by student seat index.

I'm looking for something equivalent to the non-working code below...

query = Classroom.query.filter().order_by(Classroom.start_time, ClassStuAssocObj.seat_index).all()
data = ClassroomSchema(many=True).dump(query)

---

With SQLalchemy only I can create a working query––that sorts with seat_index)

stmt = (db.session.query(Classroom, Student, ClassStuAssocObj)
                    .filter(Classroom.classroom_id == ClassStuAssocObj.classroom_id,
                            Student.student_id == ClassStuAssocObj.student_id
                    ).order_by(ClassStuAssocObj.classroom_id, ClassStuAssocObj.seat_index)
         )
print(str(stmt))
query = stmt.all()

---

With Marshmallow and SqlAlchemy, I can order_by a field (start_time)--as long as the field is within the Classroom model

query = Classroom.query.filter().order_by(desc('start_time')).all()
data = ClassroomSchema(many=True).dump(query)

---

Problem––I'm not sure how to include the association object's seat_index field in the sort.
Any push in the right direction is appreciated––as I'm new to Marshmallow.

---

Abbreviated Code for reference

app = create_app()
app.app_context().push()
db.create_all()

data = ClassroomSchema(many=True).dump(Classroom.query.all())
pprint(data, indent=2)
db.session.close()

output

[ { 'course': { 
                'lesson': 10,
                'level': 20,
                'topic': 'My favorite Giraffe',
                'unit': 30},
    'start_time': '2020-02-10T06:00:00',
    'students': [ OrderedDict([ ('seat_index', 1),
                                ( 'student',
                                  { 'age': 8,
                                    'gender': 'M',
                                    'student_name': 'Billy'})]),
                  OrderedDict([ ('seat_index', 3),
                                ( 'student',
                                  { 'age': 9,
                                    'gender': 'F',
                                    'student_name': 'Jacky'})]),
                  OrderedDict([ ('seat_index', 2),
                                ( 'student',
                                  { 'age': 7,
                                    'student_name': 'Dora'})]),
                  OrderedDict([ ('seat_index', 4),
                                ( 'student',
                                  { 'age': 7,
                                    'gender': 'A',
'student_name': 'Cici'})])]},

schema.py

from flask_marshmallow import Marshmallow
from marshmallow import Schema, fields, pprint

ma = Marshmallow()


class StudentSchema(ma.Schema):
    class Meta:
        fields = ('student_id', 'student_name', 'gender', 'age')
        # ordered = True


class ClassStuSchema(Schema):
    class Meta:
        ordered = True
        fields = ('seat_index', 'student')

    student = fields.Nested(StudentSchema)


class CourseSchema(ma.Schema):
    class Meta:
        fields = ('course_id', 'topic', 'level', 'unit', 'lesson','classroom')
        # ordered = True


class ClassroomSchema(ma.Schema):
    course = fields.Nested(CourseSchema, many=False)
    students = fields.Nested(ClassStuSchema, many=True)

    class Meta:
        fields = ('id', 'start_time', 'course', 'students',)
        # ordered = True

model.py

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.dialects import postgresql

db = SQLAlchemy()

class ClassStuAssocObj(db.Model):
    __tablename__ = 'class_stu_assoc_obj'
    classroom_id = db.Column(db.Integer, db.ForeignKey('classroom.classroom_id'), primary_key=True )
    student_id = db.Column(db.Integer, db.ForeignKey('student.student_id'),  primary_key=True)
    classroom = db.relationship('Classroom', uselist=False, back_populates='students')
    student = db.relationship('Student', uselist=False)
    seat_index = db.Column(db.Integer) # extra data for association object

    def __repr__(self):
        return f'<ClassStuAssocObj {self.classroom.created_date}, {self.student.student_name},' \
               f' seat_index: {self.seat_index}>'


class Classroom(db.Model):
    __tablename__ = 'classroom'
    classroom_id = db.Column(db.Integer, primary_key=True)
    start_time = db.Column(db.DateTime)

    course_id = db.Column(db.Integer, db.ForeignKey('course.course_id'))
    course = db.relationship("Course", back_populates='classrooms')

    student = db.relationship('ClassStuAssocObj')
    students = db.relationship('ClassStuAssocObj', back_populates='classroom')


    def __init__(self, start_time):
        self.start_time = start_time

    def __repr__(self):
        return f'<Classroom {self.start_time}>'


class Course(db.Model):
    __tablename__ = 'course'
    topic = db.Column(db.String(100)) 
    course_name = db.Column(db.String(100))
    level = db.Column(db.Integer)
    unit = db.Column(db.Integer)
    lesson = db.Column(db.Integer)
    classrooms = db.relationship("Classroom", back_populates="course")


    def __init__(self, topic, course_name, level, unit, lesso):
        self.topic = topic
        self.course_name = course_name
        self.level = level
        self.unit = unit
        self.lesson = lesson


    def __repr__(self):
        return f'<Course {self.topic}, {self.course_name}, >'


class Student(db.Model):
    __tablename__ = 'student'
    student_id = db.Column(db.Integer, primary_key=True)
    student_name = db.Column(db.String(140))
    gender = db.Column(db.String(140))
    age = db.Column(db.Integer)


    classroom = db.relationship('ClassStuAssocObj')

    def __init__(self, student_id, student_name, gender, age):
        self.student_id = student_id
        self.student_name = student_name
        self.gender = gender
        self.age = age

    def __repr__(self):
        return f'<Student {self.student_id}, {self.student_name}, {self.gender}, {self.age}>'

app.py

from sqlalchemy.engine.url import URL
from config import DB
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()


def create_app():
    app = Flask(__name__)
    app.config['SQLALCHEMY_DATABASE_URI'] = URL(**DB['LOCAL'])
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
    db.init_app(app)
    return app

Answer 1

You can set up sorting for association objects directly within your SQL Alchemy Models using "order_by". Below is copied and pasted from SQL Alchemy docs on relationships.

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship("Child", order_by="Child.id")