Hibernate HQL Count Distinct not working?

Question

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?

Solution

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

Comments

[confile]

How can I do that with HQL?

[aksappy]

select distinct r.user from Rating r where r.item=:item group by r.user; Then do a size()?

[confile]

is there a way to do it in one query?

[Jason D]

It's really strange that the extra parens inside of the count on the distinct cause it to fail with a syntax error. This is the correct fix. Certainly saved me tons of time.

[Haroldo_OK]

It's inneficient doing it that way, since you will be listing every record just to see how many of them are present. For a better example using criteria, please see stackoverflow.com/questions/22164717/…

[Jon]

Note that the criteria API has a Projections.countDistinct(). If you use that it will give the correct behavior with a lot less code. I think you can use crit.setProjection(Projections.countDistinct("user"));