Hibernate HQL Count Distinct not working?

java spring hibernate grails hql

confile · Sep 13, 2013 · Viewed 20.6k times · Source

I have the following classes:

class User {
  hasMany = [ratings: Rating] 
} 

class Item {
 hasMany = [ratings: Rating]
}

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

I want to count the distinct users that rated on an item.

The following does not work:

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

How do I have to modify the HQL query to make it work?

Answer

Your query should work as expected with a minor modification to the way you use distinct:

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

An other, but more lengthy way, of doing this query is by using User and join:

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