Hibernate HQL Count Distinct not working?
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 = :itemgroup by r.user
This is how to do in Hibernate Criteria
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));