Recursive fetch join not recursively fetching all children

I have a directory type structure represented as entities, i.e. think Directory entity and File entity.

The Directory entity has a collection of File entities and a collection of Directory entities.

I want to get the root directory and 'pre load' all directories and there files.

I am trying:

The query works it just does not pre load everything. I get all of the root directories and the root files, but when I start going into the sub directories and get the files queries are made. I.E. it seems like the recursion is not working.

I have also tried just a JOIN FETCH (which I think does an inner join fetch) with no luck.

Any ideas?

Sorry - answered the wrong question so I've deleted my post. Duh!

Messed around with inner vs outer joins with no luck. I also noticed that it seems like the recursion goes 1 level deep and then stops. Is there a property somewhere that defines how deep to go in recursive join like this?

Not sure how this ORM stuff works so this may not be any help to you, but are you looking for something like Oracle's CONNECT BY syntax for hierarchical queries?

I am not at all sure if that is something hibernate does under the covers. But I do know that hibernate does indeed support a recursive fetch. From hibernate docs;

A 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). The associated objects are also not returned directly in the query results. Instead, they may be accessed via the parent object. The only reason you might need an alias is if you 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

I am not sure why it is only grabbing some of the children.

JPA does not allow nested join fetches, nor allow an alias on a join fetch, so this is probably JPA provider specific.

In EclipseLink, you can specify a query hint to perform nested join fetches.

You can't make it recursive in JPQL though, you could go only at best n levels.
In EclipseLink you could use @JoinFetch or @BatchFetch on the mapping to make the querying recursive.

See,
http://java-persistence-performance.blogspot.com/2010/08/batch-fetching-optimizing-object-graph.html

What database are you on? Would it be easier to define a native SQL query to use here, based on your database's implementation of hierarchical queries? That might turn a "hard" Java problem into an easy SQL problem.

I am using hibernate and as stated before hibernate does support this type of query. Any hibernate users out there have any success with this? I don't think this is a 'hard' java problem I just must be missing something.

Theodore David Williams wrote:I am using hibernate and as stated before hibernate does support this type of query. Any hibernate users out there have any success with this? I don't think this is a 'hard' java problem I just must be missing something.

A number of databases can do this easily in a single SQL statement, while other people seem to have found it hard to do this with Hibernate/JPA.
Good luck, though.

Here was the subtle problem:

Call to single result seemed to indicate to hibernate to not bring back all the joins into memory.

Even though only one dir comes back (the distinct keyword in hibernate indicates to populate all children but only give back the root dir) a call to get result list works wonders.

All entities that were lazy loaded were brought back into memory in one select statement.

Went from ~30 seconds populating my tree server side to ~1 second.