Hey,

I have an object model where I have three classes with 1-* relations between them. They look like this:

A -1-*> B -1-*> C

As you can see, A has a bunch of B's as children and B has a bunch of C's as children.

The following hql query is more then logical to grab the whole tree in one query:



of course this will result in two LEFT JOINs in the SQL query which is perfectly fine.
Why can't NHibernate handle this? A.Children will not hold all the related B's, although it should.

What is the fastest way to load this graph of objects? I keep ending up running N+1 queries which sucks and I'm not sure how else I could pull this off.

Any idea?

Thanks,
RGabo

Now that I've read a little more about the issue, one question came up in my mind:

Why is hibernate.max_fetch_depth not implemented? ;)

..and what's the workaround..

RGab

Guys,

I can't believe nobody has an answer to this trivial question. All I want is to load objects where the root object has a one-to-many relationship, where the objects on the many end have another one-to-many relationship.

In Hibernate, this can be controlled with the maximum deepness of a fetch. It seemed to me, NHibernate can't really cope with this.

The problem I am facing is trivial. A country has many states, where the states have many cities. How can I grab those objects in one query?

Thanks,
RGabo

max_fetch_depth works in the latest CVS version. As for your problem, unfortunately Hibernate 2.1 HQL translator is coded in such a way as to allow only one collection to be fetched in a query, so this won't be fixed until after 1.0 is released. For now I only added a check for this problem to the code.

But would it work if I use ICriteria? If not, what kind of workaround or tip can you give me to avoid N+1 problems and successfully load that graph fast!

It would be really helpful and important for me!

Thanks!
RGabo

rgabo,

i have exactly the same problem. Did you find a good workaround:?:

Well, I'm checking right now if the implementation of fetching multiple levels deep in the latest CVS source tree works or not. It is different from the latest released 0.9.1.0 source so I'm optimistic.

Great, I am extremely interested in the results. I'm just starting to use nhibernate and finding lots of cool stuff, but also missing functionality.

Well, It seems to me the reason it won't go deeper is because it allows only one collection association or better put: fetch in a query. This would be really bad news. Sergey, can you confirm this?

RGab

I believe that fetching multiple collections in one query was enabled in Hibernate 3 only recently. Fetching multiple many-to-one's should work ok. The Loader accepts only one collection to load in its methods, making it work with multiple collections would be quite an effort and I don't want to do it until after 1.0 is released. As to why it was coded that way, I don't know, probably it was much easier to code for Hibernate Team.

Sergey, you rock, thanks for the quick confirmation. It is bad news indeed but I need some sort of recommendation from you. What I do now takes N+1 queries for N objects with one-to-many relationships two levels deep and a fairly high number of cache hits of course when I actually query the root objects itself. It takes longer than I would want to already and the problem is that N could increase substantially.

What would be your final recommendation? I almost have no time to do my best, what could be the best? Schema change to have only one one-to-many? Some sort of special query? Advanced caching of stuff? Incremental loading? Some sort of lazy loading?

What I know is that I need the full graph for one of the features of the app. I can lazy load the ones that show up in the UI (30 or so). It will introduce a little latency but that's fine, it's not production grade anyway.

Just show me the path, you don't even have to lead me. My fate is in your hands, Sergey ;)

Thanks,
Gabor

What about this:

:)

("left join fetch c.A" is required only if A is lazy loaded)

The downside is that A.Children1 will not be initialized! And its initialization will need accessing the database (even if all its items are all in the cache) :(
Anyway, it might be the solution if you can work it around...

Well, at least we reduced it from N+1 queries to 2 queries! That's already an improvement.. Makes a lot of sense what you did! This is how I did:



I am wrapping around NHibernate so the syntax is slightly different but you get the point. Hope that helps! Any other suggestions?