fetch join - multiple collections

gITs · **Joined:** Tue May 08, 2007 11:25 am **Posts:** 15

Hi List,

I was trying to optimize loading of my DomainModel objects and added multiple "left join fetch" into my repositories.
According to JIRA http://jira.nhibernate.org/browse/NH-428, this is fixed, however I still receive following error :

"Cannot fetch multiple collections in a single query if one of them is a bag"

Am I doing something wrong or is this feature not implemented jet ?

Thanks

sergey · **Posted:** Mon Jul 23, 2007 4:39 am

It's impossible to implement - try thinking about it.

gITs · **Joined:** Tue May 08, 2007 11:25 am **Posts:** 15

That's a pitty,

I saw this documentation on Hibernate:

Quote:

http://www.hibernate.org/hib_docs/refer ... ryhql.html
fetch join does not usually need to assign an alias, because the associated objects should not be used in the where clause (or any other clause). Also, the associated objects are not returned directly in the query results. Instead, they may be accessed via the parent object. The only reason we might need an alias is if we are recursively join fetching a further collection:

from Cat as cat
inner join fetch cat.mate
left join fetch cat.kittens child
left join fetch child.kittens

Is there any other way I could limit the number of selects ?

My situation is

L1
---L2
-------L3

The number of L1 are limited (1-2-3), but the number of L2's can be about 2000, the number of L3's (linked to L2) are about 10.

By using fetching to 1 level, I can reduce the number of selects already a lot. But being able to get L1-> L3 in one select statement would reduce this to 1 select.

Would it be interesting in this case to do the joining and then splitting the tuples myself ?

Thanks

christian · **Posted:** Tue Jul 24, 2007 4:51 pm

Your 1 query would return a resultset with 3 x 2000 x 10 rows. It should be obvious why this is not a good idea.

You can of course create such a product if you really want to, just not with bags. This has nothing to do with performance but with the simple fact that you can not "split the tuples" of this resultset if two bags are fetched - because you don't have enough information in the resultset to distinguish in what collection a subset of attributes of the tuple belongs.