Do you know of anyway to get a distinct result set without using the ResultTransformer when using SetFirstResult and SetMaxResults?

Question

When i use SetFirstResult and SetMaxResult and if the query has joins the result have duplicate results instead of unique.

Then i use all type of Distinct helpers for criteria api. But it doesnt filter the whole result set it just filters the paged result.

How can i overcome this issue ?

Thanks

Answer 1

I have found a hacky thing to overcome this issue.

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.

Answer 2

I built a similiar solution but in just one trip to DB:

DetachedCriteria subQuery = ...//-->Add all Criterions you want and inclusive "SetFirstResult" and "SetMaxResults"

DetachedCriteria rootQuery = DetachedCriteria.For(); // where T is an entity

subQuery.SetProjection( Projections.Distinct( Projections.ProjectionList().Add(Projections.Alias(Projections.Property("ID"), "ID")) ) );

// Note: all my entities inherit from a base class who contains a property "ID" rootQuery.Add(Subqueries.PropertyIn("ID", subQuery));

// ...then use rootQuery to get the List of T and no repeated item will be retrieved.

I hope someone find this implementation helpfull :)

Román