Criteria API -- group by?

dslevine · **Joined:** Thu May 26, 2005 12:20 am **Posts:** 72

I have an object with a one-to-many relationship, for instance Cat to paws.

When i use the Criteria API on Cat it does a join on Paws and returns multiple rows for each Cat. In HQL I would (a) not join on Paws, or if i had to I would (b) do a group by on each field in Cat. How can i tell it to explicity do (a) or (b) using the Criteria API?

With some other objects that have one-to-many relationships this is not a problem.

I'm using Hibernate 3.0

Thanks!

dan

batmat · **Posted:** Mon Jul 03, 2006 9:37 am

I'm not sure I understand your problem : are you saying you call the setFetchMode() method with the Criteria API and you are looking for how to do it with the HQL ?

dslevine · **Joined:** Thu May 26, 2005 12:20 am **Posts:** 72

No, thats not what i was saying... but thank you for the reply.

Let me try to rephrase, with a little new info I found. (I found out it was happening because I specify outer-join=true in my mapping file.)

I want to have 'outer-join=true' in my mapping file, but I want to be able to use the Criteria API and not have it automatically join on all of those tables. (Or else I get many duplicates.)

So my options:

a. remove outer-join=true from my mapping files and have extra DB calls on my normal Hibernate joins

b. keep outer-join=true in my mapping file, and specify a different mapping in the Criteria API.

I think B is doable, and I've just started working through it. I'll post again in a bit if I get it sorted or get stuck.

Thanks for your reply batmat...

d

batmat · **Posted:** Wed Jul 05, 2006 4:34 am

dslevine wrote:

I want to have 'outer-join=true' in my mapping file, but I want to be able to use the Criteria API and not have it automatically join on all of those tables. (Or else I get many duplicates.)

Are you saying that for example, is it this case :
when you want to get a particular A that owns a collection of 4 B, your criteria.list() has 4 elements. These elements are all the same reference to the right A you want ?

If so, then this behaviour is normal. It is due to the underlying outer-join behaviour. Do the SQL manually, you should understand this. The Hibernate team decided not to remove the duplicates created by this kind of join for perfs.

So the logical workaround if you want to get objects once only (no duplicates) is for example to write a method that will delete dups, like this :

Code:

   public static List removeDuplicate(List list)
   {
      return new ArrayList(new HashSet(list));
   }

So, with Hibernate, now you'd write :

Code:

List myBeans = session.createCriteria(YourClass.class).add().....list();
myBeans = removeDuplicate(myBeans);

I didn't test it, but with Hibernate 3.2, retrieving "distinct" instances can be achieved. The thing is that it's with HQL, not with criteria as you want :

Code:

select distinct a from A a left join fetch a.b

dslevine · **Joined:** Thu May 26, 2005 12:20 am **Posts:** 72

Thanks for the ongoing replya. I do understand all of that.

In SQL, I *rarely* use DISTINCT because it is almost always just masking an inefficient query that can be better written (correctly) to only return a single result of each. I would assume that HQL DISTINCT just maps to a SQL DISTINCT which can be VERY inefficient.

Fetching all of the objects and then de-dup'ing is even more inefficient.

Another problem with de-dup'ing at the Java level is then the FirstResult and MaxResults applied to a Criteria are meaningless -- that has to be done at the Java level as well.

What I would like, is to secify in the Criteria API when it should or should not join on those tables. I'm not clear if doing an Alias automatically joins on that table (in some places it seems to, sometimes it seems not to).

d