Using Declarative syntax, how do I define a relationship representing the latest object in a many-to-one collection?

I'm using SQLAlchemy's declarative syntax and I'd like to specify a relationship that provides the latest (max primary id) element in a collection. I've found this post: How do I define a SQLAlchemy relation representing the latest object in a collection? but I'm having a tough time using this pattern, creating a subquery 开发者_如何转开发using Declarative only. Any pointers or help would be greatly appreciated!

General Idea:

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base(bind=engine, metadata=metadata)

from sqlalchemy import *
from sqlalchemy.orm import *

class NewsPaper(Base):
    __tablename__ = "newspapers"
    id = Column(Integer, nullable=False, primary_key=True)
    name = Column(String(255))
    latest_article = relationship("Article",
                                primaryjoin="(Article.newspaper_id==NewsPaper.id) &"
                                               "(Article.id==SUBQUERY_FOR_LATEST_ID)")
    def __repr__(self):
        return '''<name={0}>'''.format(self.name)


class Article(Base):
    __tablename__ = "articles"
    id = Column(Integer, nullable=False, primary_key=True)
    title = Column(String(255))
    newspaper_id =  Column(Integer, ForeignKey('newspapers.id'))
    newspaper = relationship("NewsPaper", backref=backref('articles') )

    def __repr__(self):
        return '''<title={0}>'''.format(self.title)

---

The easiest way is to define the relationship outside the class once all classes are defined.
Remove your definition of latest_article from NewsPaper and add following code after the class Article definition (taken directly from Mike's answer you linked to):

# define the relationship
t_article = Article.__table__
t_newpaper = NewsPaper.__table__
latest_c = (select([t_article.c.id]).
            where(t_article.c.newspaper_id == t_newpaper.c.id).
            order_by(t_article.c.id.desc()).
            limit(1).
            correlate(t_newpaper).
            as_scalar()
            )
NewsPaper.latest_article = relationship("Article", 
                            primaryjoin=and_(
                                t_article.c.id==latest_c,
                                t_article.c.newspaper_id==t_newpaper.c.id,
                            ),
                            uselist=False
                           )

One note though: the relationship is working directly on the database, and as such it will not include those Article instances which are not yet commited but are part of the session. And most probably they would be the ones that you really want. So just be careful with that.