Java – hibernate HQL counts are different, can’t they?

I have the following categories:

class User {
  hasMany = [ratings: rating] 
} 

class Item {
 hasMany = [ratings: rating]
}

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

I want to calculate the different users who score on a project

The following does not work:

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

How to modify HQL query to make it work properly?

Solution

Your query should work as expected, with minor modifications to the way you use distinct:

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

Another longer way to execute this query is to use users and connections:

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