How to use order_by in SQLAlchemy when using relationship() with secondary-parameter (many-to-many)?

Question

I create a many-to-many relationship between a Person (as an author) and a Reference (as a scientific publication).

ReferenceAuthor = sa.Table('ReferenceAuthor', _Base.metadata,
        sa.Column('ReferenceID',  sa.Integer, sa.ForeignKey('Reference.ID'), primary_key=True),
        sa.Column('PersonID', sa.Integer, sa.ForeignKey('Person.ID'), primary_key=True),
        sa.Column('Index', sa.Integer)
        )


class Reference(_Base):
    __tablename__ = 'Reference'

    _id = sa.Column('ID', sa.Integer, primary_key=True)

    _authors = sao.relationship('Person', secondary=ReferenceAuthor)


class Person(_Base):
    __tablename__ = 'Person'

    _id = sa.Column('ID', sa.Integer, primary_key=True)

I would like to have the objects in Reference().authors ordered by ReferenceAuthor.Index.

The three tables here come from a foreign sqlite3-database. I am not able to change the structure of that tables. I have to deal with them like they are.

I could finde some examples about order_by in relationship() but nothing combined with the use of secondary=.

I tried an association_proxy and read the docs about that.

class ReferenceAuthor(_Base):
    __tablename__ = 'ReferenceAuthor'

    _reference_id = sa.Column('ReferenceID',  sa.Integer, sa.ForeignKey('Reference.ID'), primary_key=True)
    _person_id =  sa.Column('PersonID', sa.Integer, sa.ForeignKey('Person.ID'), primary_key=True)
    _index = sa.Column('Index', sa.Integer)

    _person = sao.relationship('Person')

class Reference(_Base):
    __tablename__ = 'Reference'

    _id = sa.Column('ID', sa.Integer, primary_key=True)

    _authors = saa.association_proxy('ReferenceAuthor', 'Person')

When I read the docs I think I understand the concept behind it. But with my code it doesn't work and I don't understand why. The error is

sqlalchemy.exc.InvalidRequestError: Mapper 'Mapper|Reference|Reference' has no property 'ReferenceAuthor'.

You'll need to use an [association proxy](http://stackoverflow.com/questions/7417906/sqlalchemy-manytomany-secondary-table-with-additional-fields). — univerio, Feb 13 '16 at 04:14
@univerio Thanks for the hint. I read the docs and like it. But can't run it wiht my own code. I updated the question with a use example of `association_proxy`. — buhtz, Feb 13 '16 at 16:45
Follow the example closely. To set up an association proxy on `Reference` referring to `Person`, you need to first set up a relationship on `Reference` to `ReferenceAuthor` ordered by its `Index` column. Then, add an association proxy with the name of the relationship you just set up and the attribute name on the remote class. If your relationship to `ReferenceAuthor` is named `_reference_authors`, you need to write `association_proxy("_reference_authors", "_person")`. — univerio, Feb 13 '16 at 18:26
Thanks for your explanation. Helped a lot to understand it better. I am often confused with SQLA when to use the names of the sql-fileds (e.g. `Index`) or the Python object names (e.g. `_index`) and when use them as strings (`'`). Is there a elegant way to access `_index` like that: `oneReference._authors[0]._index`? — buhtz, Feb 14 '16 at 09:09

buhtz · Accepted Answer · 2016-02-14T14:34:40.393

Solution 1:

Depending on the nice comment-answer of @univerio:

class ReferenceAuthor(_Base):
    __tablename__ = 'ReferenceAuthor'

    _reference_id = sa.Column('ReferenceID',  sa.Integer, sa.ForeignKey('Reference.ID'), primary_key=True)
    _person_id =  sa.Column('PersonID', sa.Integer, sa.ForeignKey('Person.ID'), primary_key=True)
    _index = sa.Column('Index', sa.Integer)

    _person = sao.relationship('Person')


class Reference(_Base):
    __tablename__ = 'Reference'

    _id = sa.Column('ID', sa.Integer, primary_key=True)

    _reference_authors = sao.relationship('ReferenceAuthor',
                                          order_by=ReferenceAuthor._index)
    _authors = saa.association_proxy('_reference_authors', '_person')

Solution 2:

Got this hint from the sqla-mailinglist. Depending on the first code in my initial question:

_authors = sao.relationship('Person',
                            secondary=ReferenceAuthor,
                            order_by=ReferenceAuthor.c.Index)

This looks quite simpler.

Side question: What is the difference between that two solutions?

What would insertion look like if you wanted to manually supply the index value on ReferenceAuthor? — Brian Peters, Feb 15 '19 at 21:52

How to use order_by in SQLAlchemy when using relationship() with secondary-parameter (many-to-many)?

1 Answers1