SQLAlchemy. How to order on many to many relationship?

Question

I have a SQLAlchemy model named NoteType with a relationship named sections. The NoteSection table is joined to the NoteType table through NoteTypeToSectionMap.

I want the sections list on the NoteType model to be ordered by the position field on the NoteTypeToSectionMap model. The code I have below seems to randomly be ordering the sections.

Does anyone know how to get the ordering to work?

Thanks!

class NoteType(ModelAbstract):

  __tablename__ = "noteType"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255))
    description = db.Column(db.String(255))

    sections = db.relationship("NoteSection",
                secondary=NoteTypeToSectionMap.__table__,
                primaryjoin=id==NoteTypeToSectionMap.__table__.c.noteTypeId,
                secondaryjoin=id==NoteTypeToSectionMap.__table__.c.noteSectionId,
                order_by=NoteTypeToSectionMap.__table__.c.position)

-

class NoteTypeToSectionMap(ModelAbstract):

    __tablename__ = "noteTypeToSectionMap"
    id = db.Column(db.Integer, primary_key=True)
    noteTypeId = db.Column(db.Integer, db.ForeignKey("noteType.id"))
    noteSectionId = db.Column(db.Integer, db.ForeignKey("noteSection.id"))
    position = db.Column(db.Integer)

Jakobovski Jakobovski · Accepted Answer · 2015-05-29T19:37:18

Re-write the relationship as follows.

sections = db.relationship("NoteSection",
            secondary=NoteTypeToSectionMap.__table__,
            order_by=NoteTypeToSectionMap.__table__.c.position)

SQLAlchemy. How to order on many to many relationship?

1 Answers