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

I am currently creating a feature like 'other people who liked this also like'.

The HQL statement in question returns a list of product ids (ordered by count of shared 'likes' between two products). But the result is not distinct - stripped down to the very basics, the query looks something like this. (Please note: it's not the original query, rather a simplified example to give you an idea of what I am doing)开发者_JS百科

select prd2.id from UserLike ul2
join ul2.product prd2
where ul.userId in (
    select ul.userId from UserLike ul
    join ul.product prd
    where prd.id=:productId
)
group by prd2.id
order by count(prd2.id) desc

Starting from there, is there a common pattern to retrieve the complete row/entity for each product?

In SQL I'd use the query above as a subselect within FROM and join back to the product table.

As HQL does not support subselects within FROM, I do not think there is another way than to wrap

from product p where p.id in (SUBSELECT_AS_ABOVE)

but there goes the sorting. :(

Maybe this sounds a bit weird, but I think this is a common use case - so are there any common workarounds for this?

Thanks a lot in advance and best regards, Peter

You can do it in two steps: 1. Get list if IDs (which you have already done); 2. Get all products by IDs list. You can do that with Expression.In("Id", idList) where idList is IList result from first query.

Also, if only possible, try to do everything w/o HQL but with criteria and restrictions.