SQLAlchemy: Order_by on backref

Question

I have 2 tables:

Products
========================================
   ID          Name            Desc
--------|----------------|--------------
        |                |
        |                |

Studies
========================================
   ID          Title           Year
--------|----------------|--------------
        |                |
        |                |

These 2 tables are connected by a relationship:

products_studies_association = Table('products_studies', Base.metadata,
Column('product_id', Integer, ForeignKey('products.id')),
Column('study_id', Integer, ForeignKey('studies.id')))

studies = relationship('Study', secondary=products_studies_association, backref='products')

I would like product.studies to give me the studies related to that product such that the studies are ordered by year (most recent first). None of the following work:

 studies = relationship('Study', secondary=products_studies_association, backref='products', order_by=Study.year.desc())
 studies = relationship('Study', secondary=products_studies_association, backref=backref('products', order_by=Study.year.desc()))

What is the right way of doing this? I haven't been able to find much information on ordering by anything except id.

Answer 1

I know this is old, but I stumbled across it during a search for something slightly different but still applicable, so maybe someone will find it useful.

Add the relationship to the main tables, not the association table. Also, if you explicitly add the relationship to each table, you do not need to use backref.

class Product(Base):
    __tablename__ = 'products'
    id = Column(Integer, primary_key=True)
    name = Column(Text)
    desc = Column(Text)

    studies = relationship('Study', secondary='products_studies', order_by='Study.year.desc()')

class Study(Base):
    __tablename__ = 'studies'
    id = Column(Integer, primary_key=True)
    title = Column(Text)
    year = Column(String(4))

    products = relationship('Product', secondary='products_studies')

class ProductStudyAssoc(Base):
    __tablename__ = 'products_studies'
    study_id = Column(Integer, ForeignKey('studies.id'), primary_key=True)
    product_id = Column(Integer, ForeignKey('products.id'), primary_key=True)

I put relationship(order_by='Study.year.desc()') in quotes as shown. Unquoted might work sometimes when the table is defined before referenced, but quoted should always work. If you want it to be ascending, that is default so you can omit the .desc(). (I used String(4) only to show an option; Text or Integer could work here.)

For many-to-many, I do it as above, because I'm defining both relationships anyways. For one-to-many, you can also put it in your backref as below (don't forget to import backref from sqlalchemy.orm). The following example assumes each study will serve as evidence for only one product, but each product might be supported by multiple studies.

class Product(Base):
    __tablename__ = 'products'
    id = Column(Integer, primary_key=True)
    name = Column(Text)
    desc = Column(Text)

class Study(Base):
    __tablename__ = 'studies'
    id = Column(Integer, primary_key=True)
    title = Column(Text)
    year = Column(String(4))

    product_id = Column(Integer, ForeignKey('products.id'))
    product = relationship('Product', backref=backref('studies', order_by='Study.year.desc()'))