Hibernate version: 2.1.7, about to upgrade to 3.0b

I have a table structure
A 1:n B n:1 C 1:n D 1:n E
so you can think of
- A having an optional n:n to C (e.g. A possibly has no B:s or thus C:s)
- D and E are aggregates of C that should always be loaded with C.

Now, eager fetching of A thru C works ok with:

"from A left join fetch A.bs".

To get correct functionality e.g. load C's aggregates, I load D:E using lazy="false" for all 1:n .

However, this results in a separate SQL statements for each C x D x E , e.g. unacceptable performance. Is there a way to augment the query to load A thru E in a single go, or perhaps two queries ?

//markku

(An attempt to clarify my previous question,)

assume three tables implementing a parametrized n:n relationship, using a one-to-many and many-to-one mapping. The table to the right has an 1:n relationship to a fourth table(, which represents aggregation). The fourth table has an additional level of 1:n aggregates in a fifth table, in principle this could extend indefinitely, but what I have is these five tables.

Q: In terms of a number of SQL statements generated, what is the most efficient way to load data from all of these tables? I need the objects on the "leftmost" table to be returned as the handle to the object graph.

The approach I posted before explodes the number of generated SQL statements at the aggregate "junction". I did set all mappings eager and outer-join. I found several threads hovering around similar issues, but found little to solve this...

//markku

PS. not yet posting the mappings in an attempt to keep the question short (!) and generic....

In your first example, you cannot load A through E in a single query. The hibernate docs explicity state that you can only fetch a single collection role in a single query (the same is true for eager loading, as you found out).

You can, however, accomplish this in 3 queries. You will need to mark C's aggregates, C:D:E using lazy="true" for all 1:n. Then do something like:

from A a left join fetch a.bs
from C c left join fetch c.ds where c.id in (:cids)
from D d left join fetch d.es where d.id in (:dids)

Or maybe:

from A a
left join fetch a.bs
where a.xyz = 'foo'

from A a
left join a.bs b
left join b.c c
left join fetch c.ds
where a.xyz = 'foo'

from A a
left join a.bs b
left join b.c c
left join c.ds d
left join fetch d.es
where a.xyz = 'foo'

There are probably a few variations like this that you can play around with. It would be nice to have more examples of this in the hibernate docs -- e.g. the "best practices" for a use case that requires fetching a deep hierarchy with many collection roles in a single go.

Batch loading is another option. Try putting a batch-size="20" on your collection definitions.