Can anybody have a look at the difference between these queries :

from com.dhl.acg.capacity.model.AccountCapacity accountCapacity
inner join fetch accountCapacity.allocationDay allocationDayAlias
inner join fetch allocationDayAlias.positions positionsAlias
inner join fetch accountCapacity.capacity capacityAlias
inner join fetch accountCapacity.capacityUnits capacityUnitsAlias
inner join fetch capacityUnitsAlias.cargoItems cargoItemsAlias
inner join fetch cargoItemsAlias.bookingSplitPiece bookingSplitPiece
inner join fetch cargoItemsAlias.bookingSplitPosition bookingSplitPosition
where allocationDayAlias.allocation = :allocation and capacityAlias.movementDt >= :fromDate
and (capacityAlias.movementDt <= :untilDate or :untilDate is NULL)

-------------------------
where I have no problem accessing the collection of capacityUnits; but since capacityUnits.cargoItems might be a empty collection, we miss some results. We need a left join there... so I rewrite :
-------------------------

from com.dhl.acg.capacity.model.AccountCapacity accountCapacity
inner join fetch accountCapacity.allocationDay allocationDayAlias
inner join fetch allocationDayAlias.positions positionsAlias
inner join fetch accountCapacity.capacity capacityAlias
inner join fetch accountCapacity.capacityUnits capacityUnitsAlias
left join fetch capacityUnitsAlias.cargoItems cargoItemsAlias
left join fetch cargoItemsAlias.bookingSplitPiece bookingSplitPiece
left join fetch cargoItemsAlias.bookingSplitPosition bookingSplitPosition
where allocationDayAlias.allocation = :allocation and capacityAlias.movementDt >= :fromDate
and (capacityAlias.movementDt <= :untilDate or :untilDate is NULL)

-------------------------
where I have a problem accessing the collection of capacityUnits; the collection of capacityUnits does not seem to be loaded ?! (LazyInit exception)
-------------------------

Other related question is :

In the docs i read : only one collection role may be fetched in a query ? Does this mean I can only have one 'inner/left join fetch object.collectionItems" per query ? I have several cases where this does work ... but not using left joins ....

thx in advance,

Koen

You can only perform one left join fetch per query.. its one of hibernates current limitations

Not correct, you can only fetch one collection role, but as many "-to-one" associations as you like.

It's a good limitation btw, it prevents that you permanently shot yourself in the food.

hello all,
i think i have opened an issue about this good limitation.
Imagine we have:
A ---- * B
|
|
*
C


If we query like "from A a fetch join a.bs fetch join a.cs"
the parser could detect 2 fetch collections and transform the previous query in two queries like:
from A a fetch join a.bs
and
from A a fetch join a.cs

of course this doen't solve the A---*B---*C case...

Oh, it should be "foot", not "food" :)

My bad Christian I misunderstood =) I still think we should be able to perform multiple fetch queries... whether or not they get split up into multiple actual SQL queries, it would make life in the Multi Tier'd DAO Webapp world significantly easier... as is I have to write specific methods that are capable of calling Hibernate.Initialize... which is difficult... oh and btw your Manning book rocks =)

Strange to see though that it works fine having multiple INNER JOIN fetch statements (on collections!) but not anymore as soon as LEFT JOIN fetch is involved ???

We really have a performance problem here because the

A -> * B -> * C ends up with several thousands of objects of type C

Our original solution - looping through and calling Hibernate.initialize() - is not an option here because of the numerous round trips to the database....

Even the go-between solution ( fetching type B and looping type C ) is not fast enough here... too many round trips. While it is obvious from our problem domain that a single SQL statement could do the job.



Any idea/hint ?