CODEWITHSUNDEEP
javaspringhibernategrailshql
Michael
Michael

Reputation: 33297

Hibernate HQL Count Distinct not working?

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?

Upvotes: 19

Views: 25570

Answers (3)

iamfnizami
iamfnizami

Reputation: 183

Use distinct as keyword not function, inside count function like-

count(distinct column) : Works

count(distinct(column)): Doesn't work

Upvotes: 0

dmahapatro
dmahapatro

Reputation: 50245

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

Upvotes: 48

aksappy
aksappy

Reputation: 3400

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));

Upvotes: 0

Related Questions