I'm presently developing a feature like 'other individuals who loved this like'.
The HQL statement under consideration returns a listing of product ids (purchased by count of shared 'likes' between two items). But it makes sense not distinct - removed lower towards the very fundamentals, the query looks something similar to this. (Please be aware: it isn't the initial query, rather a simplified example to provide you with a concept of things i am doing)
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
Beginning after that, it is possible to common pattern to retrieve the entire row/entity for every product?
In SQL I'd make use of the query above like a subselect within FROM and join to the merchandise table.
As HQL doesn't support subselects within FROM, I don't think there's one other way rather than wrap
from product p where p.id in (SUBSELECT_AS_ABOVE)
but there goes the sorting. :(
Maybe this sounds a little strange, however i think this can be a common use situation - so what are the common workarounds with this?
Thanks ahead of time and sincerely, Peter
It can be done in 2 steps: 1. Get list if IDs (that you've already done) 2. Get all items by IDs list. It can be done with Expression.In("Id", idList) where idList is IList derive from first query.
Also, if perhaps possible, attempt to fit everything in w/o HQL however with criteria and limitations.