database - HQL: how can I maintain sort order when fetching complete rows using a where-in-subselect construct? -

i creating feature 'other people liked like'.

the hql statement in question returns list of product ids (ordered count of shared 'likes' between 2 products). result not distinct - stripped down basics, query looks this. (please note: it's not original query, rather simplified example give idea of doing)

select prd2.id userlike ul2 join ul2.product prd2 ul.userid in (     select ul.userid userlike ul     join ul.product prd     prd.id=:productid ) group prd2.id order count(prd2.id) desc 

starting there, there common pattern retrieve complete row/entity each product?

in sql i'd use query above subselect within , join product table.

as hql not support subselects within from, not think there way wrap

from product p p.id in (subselect_as_above) 

but there goes sorting. :(

maybe sounds bit weird, think common use case - there common workarounds this?

thanks lot in advance , best regards, peter

you can in 2 steps: 1. list if ids (which have done); 2. products ids list. can expression.in("id", idlist) idlist ilist result first query.

also, if possible, try w/o hql criteria , restrictions.