java - hibernate nested query HQL/criteria -

i'm trying express complex query in hibernate either hql or criteria didn't find way. i'm aware cannot expect orm solve i'm not such expert in hibernate , got closed i'm hoping i'm missing silly.

i've been looking in questions , there this one , nobody answered except author, gave , posted solution using plain sql. maybe i'm wasting time , should same. , maybe consider duplicate... i'm not sure.

the query i'm trying build is

select * user_rank user_id in (select user_id     (select user_id user_id, row_number() over() rownumber          user_rank         ... additional parameters ...         order rank desc) maxrows     rownumber <= :number) , ... additional parameters ... 

the table stores user ranking , i'm getting top 5. row_number() over() trick postgresql able filter top 5 in outer query.

my closest attempt criteria , looks this,

projectionlist pl = projections.projectionlist()         .add(projections.property("id.userid"))         .add(projections.sqlprojection("row_number() over() 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 ...")) 

which work except fact not select top 5 users. cannot add following subcriteria

        .add(restrictions.sqlrestriction("rownum <= 5")); 

because column rownum not exist yet. , there no way add in in statement of criteria2. guess paginate on results , not slow?

i solve surely slower.

criteria usersc = session.createcriteria(userrank.class)         .addorder(property.forname("rank").desc())         .add(property.forname("... additional ..."))         .add(property.forname("... additional ..."))         .setprojection(pl);  // java code fetches query , creates list `users`  criteria criteria = session.createcriteria(userpagerankevolution.class)         .add(restrictions.in("id.userid", users))         .add(property.forname("... additional ...")) 

with hdl didn't closed.

many patience , help.