休眠 HQL 计数非重复性不起作用？

java hibernate spring grails hql

2022-09-02 11:48:54

我有以下类：

class User {
  hasMany = [ratings: Rating] 
} 

class Item {
 hasMany = [ratings: Rating]
}

class Rating {
 belongsTo = [user: User, item: Item]
}

我想计算对某个项目进行评级的不同用户。

以下情况不起作用：

select count(distinct(r.user)) from Rating as r
        where r.item=:item
        group by r.user

如何修改 HQL 查询才能使其正常工作？

答案 1

您的查询应按预期工作，但对使用方式稍作修改：distinct

select count(distinct r.user) from Rating as r 
where r.item = :item group by r.user

执行此查询的另一种（但更冗长）方法是使用和：Userjoin

select count(distinct u) from User as u 
inner join u.ratings as r where r.item = :item
group by r.user

答案 2

这是在休眠标准中执行的操作

Criteria crit = session.createCriteria(Rating.class)
.add(Restrictions.like("item", item)
.addOrder(Order.asc("user"))
.setProjection(
       Projections.distinct(Projections.projectionList()
                        .add(Projections.property("user"), "user")))
.setResultTransformer(Transformers.aliasToBean(Rating.class));