This should work (different SQL, same result):
t = Session.query(
Posts.user_id,
func.max(Posts.post_time).label('max_post_time'),
).group_by(Posts.user_id).subquery('t')
query = Session.query(User, Posts).filter(and_(
User.user_id == Posts.user_id,
User.user_id == t.c.user_id,
Posts.post_time == t.c.max_post_time,
))
for user, post in query:
print user.user_id, post.post_id
Where c stands for ‘columns’