How to model most recent version as default version in SQLAlchemy?

Question

Let's say you have these (Flask) SQLAlchemy models which model a Post and several different versions of the post, which represent different points in time. This is a one-to-many relationship between posts and versions. Each version of the post has a different number of likes, but you want to treat the most recent version as the default so that if you try to make a query referencing Post.likes, you're actually joining to the versions table and querying the likes from the versions table.

class Post(db.Model):
     __tablename__ = 'posts'
     post_id = Column(BigInteger, primary_key = True)
     
     versions = relationship('PostVersion', back_populates = 'post')


class PostVersion(db.Model):
     __tablename__ = 'post_versions'
     post_version_id = Column(BigInteger, primary_key = True)
     post_id = Column(ForeignKey(Post.post_id), primary_key = True)
     likes = Column(BigInteger)

     post = relationship('Post', back_populates = 'versions')

What kind of hybrid property expression or other method can we add to either of these models to make the following query (or something close to it) possible, not just for the likes field but or any field?

posts = Post.query.filter(Post.likes >= 100).all()

or

posts = Post.query.filter(Post.most_recent_version.likes > 100).all()

Answer 1

This is the best answer I've figured out so far. Although I'd still hopefully like to do it the way I specified in my question, that may not be possible.

class Post(db.Model):
     __tablename__ = 'posts'
     post_id = Column(BigInteger, primary_key = True)

     versions = relationship('PostVersion', back_populates = 'post')

     @hybrid_property
     def most_recent_version(self):
         return self.versions[-1]


class PostVersion(db.Model):
     __tablename__ = 'post_versions'
     post_version_id = Column(BigInteger, primary_key = True)
     post_id = Column(ForeignKey(Post.post_id), primary_key = True)
     likes = Column(BigInteger)

     post = relationship('Post', back_populates = 'versions')


     @hybrid_property
     def is_most_recent_version(self):
         return self.version_id == self.post.most_recent_version.version_id

     @is_most_recent_version.expression
     def is_most_recent_version(cls):
        max_id_per_post = db.session.query(func.max(PostVersion.version_id)).group_by(PostVersion.version_id).subquery()
        return cls.version_id.in_(max_id_per_post)

And that lets you run this query:

mr_posts = Post.query.filter(PostVersion.is_most_recent_version)
posts = mr_posts.filter(PostVersion.likes > 1).all()