Load child collection with one query.

EvgenyMaslov · **Joined:** Wed Jul 30, 2008 11:13 am **Posts:** 6

Hi everybody!
I need one little help, could you help me to write a right HQL for this situation.
two classes:

Code:

class A {
   List<B> ListB;
}

class B {
   A          ParentA;
   B          ParentB;
   List<B> childList;
}

I need effective HQL or may be multiple query to get huge data with one round trip to DB.
Unfortunatly nhibernate can't proceed this query:

Code:

select a from A as a join fetch a.ListB as b 
                     join fetch b.ListB as child

If I remove bottom line then hibernate make a query for each element B in ListB to get child items.

Thanks for you help.

wolli · **Posted:** Thu Jul 31, 2008 2:16 am

What do you mean with "can't process" ? No result, an exception ? Shouldn't it be:

Code:

select a from A as a join fetch a.ListB as b
                     join fetch b.chidlList as child

or is this just a typo in the post ?

EvgenyMaslov · **Joined:** Wed Jul 30, 2008 11:13 am **Posts:** 6

Yes you're right i made a mistake in the query.
But it's not crusial because real problem is an exception "Cannot fetch multiple collections in a single query if one of them is a bag".
As I understood "bag" means any collection. I tried with <list> and <bag> and got the same result.

wolli · **Posted:** Thu Jul 31, 2008 4:59 am

There is a major difference between a bag and the other collection types. The bag allows multiple items of the same item. And I think that's the reason why hibernate can't do a join over more than one.

If you join more than one collection you get duplicate instances and since a bag would allow multiple items, hibernate can't decide which is a real item and which is just a duplicate from the join. I think if you switch to a set or a list and set the correct index values for both collections, it might work.

Maybe you have to set a higher value to "hibernate.max_fetch_depth" in the configuration. I don't know what's the default for this, but you need at least 2.

EvgenyMaslov · **Joined:** Wed Jul 30, 2008 11:13 am **Posts:** 6

Hi!
Thank you for your help. Then I tried list + fetchsize = 3 it was help.
But now I face up with an another problem. All List<B> collections now contain a lot of "null" items. May be you know how to avoid this.
Of cource I can remove these items "by hand" but if nHibernate can do this I prefer this way :).

wolli · **Posted:** Thu Jul 31, 2008 10:33 am

is it possible that your index values are not consecutive and not starting with 0 for each parent ?

EvgenyMaslov · **Joined:** Wed Jul 30, 2008 11:13 am **Posts:** 6

The index value is consecutive but not starting with 0 for each parents.
Currently I'm using PK for index value.

wolli · **Posted:** Fri Aug 01, 2008 4:21 am

The items are added to the list at the "pk" position. You have to switch to a set or use explicit index values (separate column), starting with 0 for each parent.

EvgenyMaslov · **Joined:** Wed Jul 30, 2008 11:13 am **Posts:** 6

Hi!
Thnks for you help. Now all works properly.

EvgenyMaslov · **Joined:** Wed Jul 30, 2008 11:13 am **Posts:** 6

Hi!
Thnks for you help. Now all works properly.