我找到了方法 . 我知道不能指望 orm
解决所有问题,但我希望我能错过一些愚蠢的东西 .
我一直在寻找问题,并且有this one并且没有人回答,除了作者,他放弃并使用普通的 SQL
发布了一个解决方案 . 所以也许我'm wasting my time and I should do the same. And maybe you consider this is a duplicate... I'我不确定 .
我正在尝试构建的查询是
select * from user_rank
where user_id in (select user_id
from (select user_id as user_id, row_number() over() as rownumber
from user_rank
where ... additional parameters ...
order by rank desc) as maxrows
where rownumber <= :number)
and ... additional parameters ...
该表存储了用户排名,我获得了前5名 . row_number() over()
只是 postgresql
能够过滤外部查询中前5名的技巧 .
我最接近的尝试是针对Criteria,它看起来像这样,
ProjectionList pl = Projections.projectionList()
.add(Projections.property("id.userId"))
.add(Projections.sqlProjection("row_number() over() as rownum", new String[] {"rownum"}, new Type[] { new IntegerType() }));
DetachedCriteria subCriteria = DetachedCriteria.forClass(UserRank.class)
.addOrder(Property.forName("rank").desc())
.add(Property.forName("... additional ..."))
.add(Property.forName("... additional ..."))
.setProjection(pl)
Criteria criteria2 = session.createCriteria(UserRank.class)
.add(Property.forName("id.userId").in(subCriteria))
.add(Property.forName("... additional ..."))
这将是有效的,除了它没有选择前5名用户 . 我无法将以下内容添加到 subCriteria
.add(Restrictions.sqlRestriction("rownum <= 5"));
因为列 rownum
尚不存在 . 并且无法在 criteria2
的 in
语句中添加它 . 我想我可以对结果进行分页并且它不会太慢?
我也可以这样解决它肯定会慢一点 .
Criteria usersC = session.createCriteria(UserRank.class)
.addOrder(Property.forName("rank").desc())
.add(Property.forName("... additional ..."))
.add(Property.forName("... additional ..."))
.setProjection(pl);
// Java code that fetches from the query and creates the list `users`
Criteria criteria = session.createCriteria(UserPageRankEvolution.class)
.add(Restrictions.in("id.userId", users))
.add(Property.forName("... additional ..."))
随着 HDL
我甚至没有关闭 .
非常感谢您的耐心和帮助 .