Can someone explain to me how come this issue still unresolved? In my mind it is not a trivial one. What happened to the solution provided by Sylvain Tremblay?

Has there been any more progress on this one?

The only "workaround" for this that I've been able to come up with is to issue two queries from the criteria same criteria object. The first one gets the id's the second one is contrained to the ids.

//set up crtieria as you wish, including pagination
myCriteria = doStuffToSetupCriteria();
myCriteria.setFirstResult((page-1)*itemsPerPage);
myCriteria.setMaxResults(itemsPerPage);

//get the list if primary keys
myCriteria.setProjection(Projections.distinct(Projections.property("myAllias.id"));
List ids = gacc.list();

//now add the id's into the restriction
myCriteria.add(Restrictions.in("myAlias.id, ids));

//clean up from the last critiera run
gacc.setProjection(null);
gacc.setFirstResult(0);
gacc.setMaxResults(Integer.MAX_VALUE);

//your results
List objects = gacc.list()

A little hacky I agree, but the only acceptable soltion I could find given this limitiation.

I have been working on a new Projection called RootProjection which can be used to solve this problem.
This projection can be compared to the PropertyProjection but all fields from the Root criteria are returns.

You can then set the projection on the Criteria and chain it with the Distinct Projection:

Criteria myCriteria = mySession.createCriteria(MyRootClass.class);
myCriteria.createCriteria("association1").createCriteria("association2)......add(Restrictions.eq("someField", "someValue");

myCriteria.setProjection(Projections.distinct(Projections.RootProjection()));

The select generated will then look like this:

select root.field1, root.field2, .... from root inner join association1......inner join associations2........where..........

Then you use the myCriteria.list() to get DISTINCT root entities.

However to make this work minor changes needs to be done in Hibernate.
I will be submitting a patch so this gets integrated in future version.

Sylvain

It would be nice if hibernate could fetch associations this way
1. Select root entities, apply max rows limit
2. In one shot select required associations with something like "WHERE root_entity_id IN (....)"

If you're only using the joined criteria for restricting the list of entities returned, there's no need to fetch the columns of the joined entity at all.