|
History
| Log In
|
|
|
|
Duplicate. I still think this is important, however.
It seems you may be able to accomplish what you want by using
setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY). See: http://www.hibernate.org/hib_docs/api/net/sf/hibernate/Criteria.html#setResultTransformer(net.sf.hibernate.transform.ResultTransformer) Problem with setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) is that it's done only after the query is executed.
If you happen to have criteria.setMaxResults(10) and first 8 items equal with each other, then you get only 3 items as a result: List results = session .createCriteria(User.class) .setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY) .setMaxResults(itemCount.intValue()) .list(); Now I don't know if I should fetch all the items from database and then just cut the first 10 or what... Another problem related to using setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) happens when using pagination. The counting the results is fairly easy and works fine even when counting distinct results, however, the count won't match the actual results if setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) is used afterward.
Any decision on this matter? I'm writing OReilly EJB3 book and need to know sooner or later. Couldn't this be as simple as taking the results and putting them into a LinkedhashMap and then doing a new List(map.keySet());?
Would the results of that be any different from using the ResultsTransformer? I had the same issue as mentioned above - I need the distinct to be applied before the maxResults and since the maxResults is accomplished in the database (at least in the Oracle implementation), it seems like the distinct would have to be done there too.
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.
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 (....)" 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 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. 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?
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
|
New Feature
Open
Minor
Add Distinct to Criteria API
<p>The return of duplicates in an entity list if you eagerly fetch their children is unexpected behavior which, while easily remedied in the app, perhaps does not belong in this layer. I understand that adding "DISTINCT" to the query would be a major performance hit, so why not handle it programatically after returning the results? We have to handle it in any case, since it is an unwieldy and unexpected result, so we might as well hide it in the proper layer.</p>