I am trying to use sqlalchemy’s OrderingList in a many-to-many relationship, but I have trouble setting up the OrderingList to use the attribute in the secondary (relationship) table.

More specifically, I have a Story (primary key (publisher, id)) a Tag (primary key (type, name)) model. A Story can have multiple Tags and a Tag multiple stories (many-to-many relationship). The order of tags in a story matters and can differ between stories using the same tags. These are linked together using the story_has_tag table (story_publisher, story_id, tag_type, tag_name, index).

I’ve already managed to setup the regular many-to-many relationship some time ago and am now trying to introduce the above mentioned order using the “story_has_tag.index” column.

Here are the relevant parts of the models:

story_has_tag_table = Table(
    "story_has_tag",
    Base.metadata,
    Column("story_publisher", String(MAX_SITE_LENGTH), nullable=False),
    Column("story_id", Integer, nullable=False),
    Column("tag_type", Unicode(MAX_STORY_TAG_LENGTH)),
    Column("tag", Unicode(MAX_STORY_TAG_LENGTH)),
    Column("index", Integer, autoincrement=False),
    ForeignKeyConstraint(
        ("story_publisher", "story_id"),
        ("story.publisher", "story.id"),
        ondelete="CASCADE",
    ),
    ForeignKeyConstraint(
        ("tag_type", "tag"),
        ("tag.type", "tag.name"),
        ondelete="CASCADE",
    ),
)

class Tag(UniqueMixin, Base):
    """
    This class represents a tag in the database.

    It is also used for warnings and similiar information.
    """
    __tablename__ = "tag"

    type = Column(Unicode(MAX_TAG_TYPE_LENGTH), primary_key=True)
    name = Column(Unicode(MAX_STORY_TAG_LENGTH), primary_key=True)
    stories = relationship("Story", secondary=story_has_tag_table, back_populates="tags")

class Story(Base):
    """
    This class represents a story in the database.
    """
    __tablename__ = "story"

    publisher = Column(String(MAX_SITE_LENGTH), primary_key=True)
    id = Column(Integer, primary_key=True, autoincrement=False)
    tags = relationship(
        "Tag",
        secondary=story_has_tag_table,
        back_populates="stories",
        collection_class=ordering_list("index"),  # <--- this line is problematic
        order_by="story_has_tag.c.index",
    )

The problem lies in the collection_class=ordering_list("index") line, which causes an AttributeError later on. I’ve tried a couple other values like "story_has_tag_table.index", but sqlalchemy always interprets it as an attribute of Tag.

I’ve googled for similar problems, but found no mention of using the ordered list with a secondary table. Reading the source code, comments mention that more complex behavior could be achieved by subclassing the ordering list, but that’s something I’d like to avoid if possible. Also, I am not sure I could implement get_order_value() to reference the secondary table.