Problems with Hibernate Criteria and distinct entities

I've encountered a strange lack of ability in Hibernate Criteria API. Suppose you have an entity called User with many-to-many relationship to groups. If you need to find all users appearing in one or more of given groups, you can write an HQL like

u from User u join u.groups g where g.id in :groups

This query will do the job, but if a user appears in a few groups, it will appear few times in the result set. To fix that, we can use "distinct" keyword:

distinct u from User u join u.groups g where g.id in :groups

Unfortunately, I had to provide an ability to order the results by any property of User (first name, last name, etc), and support paging for showing only a few results out of thousands. HQL does not allow variables in "order by" clause, so I had to use Criteria for that.

Without the "distinct" the code is quite simple:

Collection<Long> groups = ... //will come as a parameter
Order order = ... //will also come a parameter
int maxResults = ... //you've got the idea

Criteria c = getSession().createCriteria(User.class);
c.createAlias("groups", "g");
c.add(Restrictions.in("g.id", groups);
c.addOrder(o);
c.setMaxResults(maxResults);
return c.list();

So far so good. Let us add distinct now. Criteria API has support for that. First I tried to add

c.setProjection(Projections.distinct(Projections.id()))

But the problem with this approach is it does not return a collection of User entities anymore, it returns a collection of Ids (integers). I tried to google a little bit for the solution, but found only complains on this missing feature.

So at the end I made up an inner query. I have to check yet what will be the performance degrade of this solution relative to the original HQL one, but at least it returns correct results:

Collection<Long> groups = ... //will come as a parameter
Order order = ... //will also come a parameter
int maxResults = ... //you've got the idea

DetachedCriteria dc = DetachedCriteria.forClass(User.class);
dc.createAlias("groups", "g");
dc.add(Restrictions.in("g.id", groups);

Criteria c = getSession().createCriteria(User.class);
c.add(Property.forName("id").in(dc));
        
c.addOrder(o);
c.setMaxResults(maxResults);
return c.list();

This criteria will create the following SQL (for brevity I shortened the aliases and replaced with the asterisk the long list of columns, generated by Hibernate:

select this_.*
    from
        Users this_
    where
        this_.ID in (
            /* criteria query */ select
                distinct this_.ID 
            from
                Users this_
            join
              User_to_Group u2g
                on this_.ID=u2g.USER_ID
            join
              Groups gr
                on u2g.GROUP_ID=gr.ID
            where
              gr.ID in (
                ?,?
            )
        )
    order by
        this_.UserName

One can see that in addition to SELECT IN SELECT, caused by the limitation of Criteria API I explained above, this query makes also unnecessary second because of many-to-many relationship. Ideally, I would like to get SQL like:

select this_.*
    from
        Users this_
          join
            User_to_Group u2g
              on this_.ID=u2g.USER_ID
    where
        u2g.GROUP_ID in (
          ?,?
        )
    order by
        this_.UserName

If you have suggestions how to improve my solution, please do not hesitate to comment. Thank you!

Thank you for your interest!

We will contact you as soon as possible.

Send us a message

Oops, something went wrong
Please try again or contact us by email at info@tikalk.com