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

Question

我有两张桌子。第一个表示每个行一个article，第二个表示每个行一个news_cluster（每个簇的所有articles_id都有一个数组列）。你知道吗

我需要得到19篇最好的news_cluster，然后为每个news_cluster得到4篇最好的文章（按l_score column排序）。你知道吗

目前，我使用SQLAlchemy从所有19 news_cluster中获取所有articles_id，然后使用python按news_cluster和l_score排序：

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_id）对组，并通过这个psql查询获得每个组的前4l_score：

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中实现这个查询。你知道吗

Answer 1

我的问题主要在这里回答：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秒。你知道吗