python - How to meaningfully use additional properties of an assosiation prox object?

Question

Consider a many-to-many relationship of Things where the order of the children (or parents) matters. So the association object ("Link") has an additional property "position". When I fill the structure, I obviously can't use the "association-jumping" append() method because I need to explicitly access the Link object to set the "position" attribute. Sad but understandable.

What I'm struggling with now is to retrieve the children of a Thing in the order given by the Link's position property. Is that in any way possible? If not, the association_proxy is not of any use to me in either populating nor retrieving the data which makes me wonder what it is good for at all. I've tried to wrap my head around the use cases in the documentation but they don't seem to apply for my situation.

Here's a self-contained example:

from sqlalchemy import create_engine, CHAR, Column, Integer,
    String, ForeignKey
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Thing(Base):
    __tablename__ = 'thing'
    id            = Column(Integer, primary_key=True)
    name          = Column(String(255))
    up = association_proxy('coll_up', 'up')
    down = association_proxy('coll_down', 'down')

class Link(Base):
    __tablename__ = 'link'
    id_up         = Column(Integer, ForeignKey('thing.id'),
                           primary_key=True)
    id_down       = Column(Integer, ForeignKey('thing.id'),
                          primary_key=True)
    position      = Column(Integer)
    up = relationship('Thing', primaryjoin=id_up == Thing.id,
                       backref='coll_down')
    down = relationship('Thing', primaryjoin=id_down == Thing.id,
                        backref='coll_up')

if __name__ == '__main__':
# I know that for convenient append()ing stuff I'd need an __init__()
# method, but see comments below

    engine = create_engine('sqlite://')
    Base.metadata.create_all(engine)
    Session = sessionmaker(engine)

    if __name__ == '__main__':
        db = Session()
        root = Thing(name='Root thing')
        db.add(root)
# inserting "reversed" to have something to sort
        for i in reversed(range(5)):
            t = Thing(name='Thing %d' % i)
# If Link had an __init__ method I could use root.append(t), but...
            db.add(t)
# ...in order to set position, I need to explicitly use the association
# object anyway
            l = Link(up=root, down=t, position=i)
            db.add(l)
        db.commit()

# OK, so far the association_proxy construct hasn't been of any use. Let's
# see about retrieving the data...

        root = db.query(Thing).filter_by(name='Root thing').one()
        for thing in root.down:
            print(thing.name)
# This, as expected. prints the elements in the order in which they were
# inserted (which is "reversed"). How can I sort them by the "position"
# property of the "Link" element, or is the associaton object useless in
# this scenario?

Answer 1

I'd suggest investigating the order_by parameter of relationships. You can can order the ORM's organization of related object by the child's properties. (Ordering will not work without session.commit()).

Assuming you want to order by Link.position:

class Link(Base):
    __tablename__ = 'link'
    id_up = Column(Integer, ForeignKey('thing.id'), primary_key=True)
    id_down = Column(Integer, ForeignKey('thing.id'), primary_key=True)
    position = Column(Integer)

    # Note the syntax for order_by -- the ORM model is referred to as a string
    up = relationship(
        'Thing', primaryjoin=id_up == Thing.id,
        order_by='Link.position',
        backref='coll_down'
        )

    down = relationship(
        'Thing',
        primaryjoin=id_down == Thing.id,
        order_by='Link.position',
        backref='coll_up'
        )

If you need more rigid ordering, meaning, ordering before the child is committed to the session, you can define an orderinglist on the relationship which "intercept" and reorganize relationships in the ORM. (Ordering will work without session.commit()).

from sqlalchemy.ext.orderinglist import ordering_list

class Link(Base):
    ...
    
    up = relationship(
        'Thing',
        primaryjoin=id_up == Thing.id,
        order_by='Link.position',
        collection_class=ordering_list('position')
        backref='coll_down'
        )
    ...

As a next step, if you're looking to organize your position based on ids (perhaps position = id_up - id_down), I'd suggest looking into sqlalchemy's event API. An elegant approach would be trigger recalculation of relevant attributes based on a relevant update. (Ex: id_up is increased by 1, increase position by 1).