HQL：使用 where-in-subselect 构造获取完整行时如何保持排序顺序？
我目前正在创建一个类似“其他喜欢这个的人也喜欢”的功能。
相关 HQL 语句返回产品 ID 列表（按两个产品之间共享“喜欢”的数量排序）。但结果并不明显 - 精简到最基本的内容，查询看起来像这样。 （请注意：这不是原始查询，而是一个简化的示例，让您了解我在做什么）
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
从那里开始，是否有一个通用模式来检索每个产品的完整行/实体？
在 SQL 中，我将使用上面的查询作为 FROM 中的子选择并连接回产品表。
由于 HQL 不支持 FROM 中的子选择，因此我认为除了换行之外没有其他方法
from product p where p.id in (SUBSELECT_AS_ABOVE)
，但会进行排序。 :(
也许这听起来有点奇怪，但我认为这是一个常见的用例 - 那么有没有常见的解决方法？
提前非常感谢，并致以最诚挚的问候， 彼得
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)
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您可以分两步完成：
1. 获取 ID 列表（您已经完成了）；
2. 根据ID列表获取所有产品。
您可以使用 Expression.In("Id", idList) 来执行此操作，其中 idList 是第一个查询的 IList 结果。
另外，如果可能的话，尝试在没有 HQL 的情况下做所有事情，但有标准和限制。
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.