<p>这会让你得到你想要的:</p>
<pre><code>SimilarPost = aliased(Post)
SimilarPostOption = aliased(PostOption)
post_popularity = (db.session.query(func.count(SimilarPost.id))
.select_from(PostOption)
.filter(PostOption.post_id == Post.id)
.correlate(Post)
.outerjoin(SimilarPostOption, PostOption.val == SimilarPostOption.val)
.join(SimilarPost, sql.and_(
SimilarPost.id == SimilarPostOption.post_id,
SimilarPost.place_id == Post.place_id)
)
.as_scalar())
popular_post_id = (db.session.query(Post.id)
.filter(Post.place_id == Place.id)
.correlate(Place)
.order_by(post_popularity.desc())
.limit(1)
.as_scalar())
deduped_posts = (db.session.query(Post, post_popularity)
.join(Place)
.filter(Post.id == popular_post_id)
.order_by(post_popularity.desc(), Post.timestamp.desc())
.all())
</code></pre>
<p>我不能谈论大型数据集的运行时性能,可能有更好的解决方案,但这正是我从许多源代码(<a href="https://stackoverflow.com/questions/6879391/mysql-join-with-limit-1-on-joined-table">MySQL JOIN with LIMIT 1 on joined table</a>,<a href="https://stackoverflow.com/questions/6206600/sqlalchemy-subquery-in-a-where-clause">SQLAlchemy - subquery in a WHERE clause</a>,<a href="http://docs.sqlalchemy.org/en/rel_0_7/orm/query.html" rel="nofollow noreferrer">SQLAlchemy Query documentation</a>)中合成的。最大的复杂因素是您显然需要使用<code>as_scalar</code>将子查询嵌套在正确的位置,因此无法从同一个子查询中同时返回Post id和count。在</p>
<p>FWIW,这是一个庞然大物,我同意user1675804,SQLAlchemy代码这么深很难摸索,而且不太容易维护。您应该仔细研究更多的低技术解决方案,如向数据库添加列或用python代码做更多的工作。在</p>