python - 如何从SQLAlchemy中的ID池中获得每组的前n个结果？

我有两张桌子。第一个表示每行一个article，第二个表示每行一个news_cluster（每个集群都有一个数组列）。
我需要得到最好的19篇文章，然后为每个文章获得4篇最好的文章（按articles_id排序）。
目前，我使用SQLAlchemy从所有19个news_cluster中获取所有news_cluster，然后使用python按l_score column和articles_id对它们进行排序：

all_news_obj = News.query.order_by(score).limit(19)
all_ids = sum([a_news.articles_id for a_news in all_news_obj], [])
all_articles = Articles.query.filter(Articles.id.in_(all_ids)).all()
articles_sorted = 4_best_l_score_sorted_by_cluster(all_articles)

我想知道是否有一种更快的方法来获取和排序这些文章，我看到您可以通过集群（news_cluster）进行分组，并使用此psql查询获得每个组的前4个news_cluster：

SELECT ranked.* FROM
(SELECT articles.*, rank() OVER (PARTITION BY news_id ORDER BY l_score DESC)
    FROM articles
    WHERE id = ANY(ARRAY[209146, 209140, 209154...])
) ranked
WHERE rank <=4

因此，如果psql首先处理排序，可能会更快，但我不知道如何在SQLAlchemy中实现这个查询。

最佳答案

我的问题主要在这里回答：Convert rank and partition query to SqlAlchemy
我只需要调整它以适应我的Articles模型，并添加一个过滤器以匹配all_ids_array中的id：

subquery = db.session.query(
    Articles,
    func.rank().over(
        order_by=Articles.l_score.desc(),
        partition_by=Articles.news_id
    ).label('rank')
).filter(Articles.id.in_(all_ids_array)).subquery()

articles_sorted = db.session.query(subquery).filter(subquery.c.rank <= 4)

我是一个快乐的人：它比获取所有文章并用python进行排序快0.1秒。