How to load full object graph in HQL?

grennis · **Joined:** Sat Mar 25, 2006 9:16 am **Posts:** 150

I have a deep object nesting of parent/child relations. When I load the top level parent object using ICritiera, hibernate issues multiple queries to load the full object graph. It cant be done with joins since there are multiple child collections in most parents.

My question is, what is the best way to do this using HQL? I then also need to issue mulitple queries to fill in the children and grandchildren. Do I manually set or populate the collection properties on the parent objects? I don't see any example of anybody doing this though.

jbosseur · **Posted:** Fri May 26, 2006 11:04 am

Hi,

I am not exactly sure wahte you want but as i understand you
want the complete object tree loaded when you execute a criteria?

it works like this:

Code:

   Object o = session.createCriteria(YourObj.class,"y")
                      .setFechMode("children", FetchMode.JOIN)
                ................. // and so one
                .uniqueResult();
                  

Or you can specify lazy="false" in your mapping
there is a but.... this does not work correctly in Hibernate 3.0.5 you have to have 3.2

grennis · **Joined:** Sat Mar 25, 2006 9:16 am **Posts:** 150

Thanks, but my question is how to do this with HQL. To better explain here is an example:

Code:

Companies -> Divisions -> Departments -> Employees -> Comments
Companies -> Locations -> Units
Companies -> Partners -> Reps

I want to get a list of companies and all their children using HQL. Since company has multiple child collections, you cant just "fetch join" them all. You would have to use multiple HQL queries, right? (From the log I can see that this is what the Criteria method is doing).

The question is how is this done?

jbosseur · **Posted:** Fri May 26, 2006 2:57 pm

grennis wrote:

Code:

Companies -> Divisions -> Departments -> Employees -> Comments
Companies -> Locations -> Units
Companies -> Partners -> Reps

Like this?!?!?!?!

List l = session.createQuery("
from Companies as c
join fetch c.divisions as d
join fetch d.departments
join fetch c.locations.....
etc.......
")

Good luck!

grennis · **Joined:** Sat Mar 25, 2006 9:16 am **Posts:** 150

Thanks, but as I mentioned already, you cannot join fetch multiple child collections at the same time. Give it a try and you see you get an exception. It makes sense anyway if you think about the sql this would generate. Remember these are one-to-many assocs.

jbosseur · **Posted:** Fri May 26, 2006 3:35 pm

What version of hibernate are you using?
I was having a similar problem with Criterias
and it was bug in 3.0.x we upgraded to 3.1.x and worked like a charm....

Just tried it with HQL and worked as well....

grennis · **Joined:** Sat Mar 25, 2006 9:16 am **Posts:** 150

I am using nhibernate. But I dont see why the technology would matter, you cant retrieve multiple unrelated child collections into a single parent using one select statement without gross inefficiency because that would create the cartesian cross join

jbosseur · **Posted:** Fri May 26, 2006 9:16 pm

grennis wrote:

I am using nhibernate.

There is a forum espesialy for nhibernate.....
what I was refering to was the version which I think solved eaxactly your problem...

But since You are using NHibernate about the bugs there

I did somethis like this:

List l = session.createQuery(
from paret p
join fetch p.children c
join fetch c.grandchildren

)

this worked fine for me....

I did not try:

List l = session.createQuery(
from paret p
join fetch p.children c
join fetch c.grandchildren
join fecth p.cousins

)

grennis · **Joined:** Sat Mar 25, 2006 9:16 am **Posts:** 150

Not p.cousins really, just p.otherchildren. See what I mean?

jbosseur · **Posted:** Fri May 26, 2006 10:51 pm

grennis wrote:

Not p.cousins really, just p.otherchildren. See what I mean?

I am not sure what you mean by otherchildren....

is that a subset of children????

grennis · **Joined:** Sat Mar 25, 2006 9:16 am **Posts:** 150

No

As I mentioned the parent has several sets of children. The children are not related in any way to other children.

As I mentioned, a company has divisions and a company has locations. Divisions and locations are not related in any way. Now, how do I select companies and their divisions and locations using HQL? And as I mentioned you cannot use two fetch outer joins.

jbosseur · **Posted:** Sat May 27, 2006 8:21 am

grennis wrote:

No

As I mentioned the parent has several sets of children. The children are not related in any way to other children.

Exactly like you showed above:

Companies -> Divisions -> Departments -> Employees -> Comments
Companies -> Locations -> Units
Companies -> Partners -> Reps

But is that not more os re less the same as

Families -> Children > GrandChildren
Families > Friends > Children
??

Ik Java more or less like :

Code:

Class Compnay {
    private List divisions
    private List locations
}

Class Division{
    private List departments
}

Class Location{
    private List units
}

etc.......

If so I am pretty sure I was allready able to do this....
What version of hibernate are you using??? and wat is the erros are you getting???

Greetings..

grennis · **Joined:** Sat Mar 25, 2006 9:16 am **Posts:** 150

That's fine, I'm not asking about the object representation. I'm asking about the HQL to use. You cannot use two outer fetch joins and it doesn't relate to hibernate version... It is grossly inefficient even if it did work.

jbchurn · **Posted:** Sat May 27, 2006 10:49 am

Hi grennis,

First of all, i'm also newbie to hibernate but see if this help.

If i understand your situation correctly, you want to know how to join the following but in HQL usually we have to follow the object graph kind of joining.

Code:

                    |-----> divisions
company  ----
                    |-----> locations

If we can't join company to divisions then further navigate to join to locations, maybe you can try to start join using the sequence below:

Code:

division -----> company --------> locations.

Hope it helps.[/code]

grennis · **Joined:** Sat Mar 25, 2006 9:16 am **Posts:** 150

Thanks for the reply. Unfortunately, my tables are actually much more complex (this was just a simple example). I don't really think it is feasible with my tables.

I appreciate the suggestion though, it is a good idea in some situations