sorting sqlalchemy relationships by primary key

Question

How can I make a relationship attribute be sorted by the child tables primary key? I have a schema where many of the tables have a common parent and the parent has a list of each of its children using the declaritive system like this

class Scenario(Base):
   __tablename__ = 'scenarios'
   id = Column(Integer, primary_key=True, autoincrement=True)

class ScenarioMixin(object):
   @declared_attr
   def scenario_id(cls):
      return Column(None, ForeignKey(Scenario.id),
                    primary_key=True,
                    index=True)

   @declared_attr
   def scenario(cls):
      return relationship('Scenario',
                          backref=backref(cls.__tablename__))

# example scenario-specific table
class Child(Base, ScenarioMixin):
   __tablename__ = 'children'
   id1 = Column(String, primary_key=True)
   id2 = Column(String, primary_key=True)

So the scenario object has an attribute children which is a list of children. This works fine, except that the order of the Child objects in the list is arbitrary. This is fine for the semantics of the application, it leads to nondeterministic behavior. What's the best way to configure the mixin so that every child list will be sorted by the primary key of its table, even if it's a compound primary key?

Answer 1

Specify order_by argument in your relationship definition as in the code below.

@declared_attr
def scenario(cls):
    return relationship(
        'Scenario',
        backref=backref(
            cls.__tablename__,
            order_by=lambda: cls.__table__.primary_key.columns
        ),
    )

In you case you cannot return cls.__table__.primary_key.columns straight away because at the definition time the __table__ does not exist yet. But returning a callable works.

See Relationships API for more information.