title: How to get Marshmallow-Sqlalchemy to Sort by an Association Object Field?
body: <p>How can I get Marshmallow to sort the returned dataset––by a field in the association object (seat_index)? <br/>The end result is to sort a bunch of classroom objects by class start time, and then by student seat index.</p>
  
  <p>I'm looking for something equivalent to the non-working code below...</p>
  
  <pre><code>query = Classroom.query.filter().order_by(Classroom.start_time, ClassStuAssocObj.seat_index).all()
  data = ClassroomSchema(many=True).dump(query)
  </code></pre>
  
  <hr>
  
  <p>With SQLalchemy only I can create a working query––that sorts with seat_index)</p>
  
  <pre><code>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()
  </code></pre>
  
  <hr>
  
  <p>With Marshmallow and SqlAlchemy, I can order_by a field (start_time)--as long as the field is within the Classroom model</p>
  
  <pre><code>query = Classroom.query.filter().order_by(desc('start_time')).all()
  data = ClassroomSchema(many=True).dump(query)
  </code></pre>
  
  <hr>
  
  <p>Problem––I'm not sure how to include the association object's seat_index field in the sort.<br/>
  Any push in the right direction is appreciated––as I'm new to Marshmallow.</p>
  
  <hr>
  
  <p>Abbreviated Code for reference</p>
  
  <pre><code>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()
  </code></pre>
  
  <p>output</p>
  
  <pre><code>[ { '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'})])]},
  </code></pre>
  
  <p>schema.py</p>
  
  <pre><code>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
  </code></pre>
  
  <p>model.py</p>
  
  <pre><code>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'&lt;ClassStuAssocObj {self.classroom.created_date}, {self.student.student_name},' \
                 f' seat_index: {self.seat_index}&gt;'
  
  
  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'&lt;Classroom {self.start_time}&gt;'
  
  
  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'&lt;Course {self.topic}, {self.course_name}, &gt;'
  
  
  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'&lt;Student {self.student_id}, {self.student_name}, {self.gender}, {self.age}&gt;'
  </code></pre>
  
  <p>app.py</p>
  
  <pre><code>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
  </code></pre>