query for one to many relationship on multiple entities

[Bhavin Sanghani]

Hi,

I have few classes and between them one-to-many relationship exists. e.g.

One Object A can have many Object Bs.
One Object B can have many Object Cs.
and list goes on upto 6-7 levels. You can think of "tree" structure.

Now, I require to fetch data of all the objects. I am trying to figure out betch fetching strategy:

1) I keep lazy loading but then also it would fire many queries
2) If I go for eager loading then it ends up with n+1 select problem
3) If I use left outer join then single query can give me the result but I would require to process on the list of resultsets in DAO and final list I need to send at web layer.
4) If I use join fetch then gets "can not fetch multiple bags" error


Can anyone suggests best approach if any or similar example explained online?

Thanks,
Bhavin

[Cristian Vrabie]

Hi there. I'm really not an expert but i do think that the "best solution" it very much depends on what you're trying to achieve.

For example if you're looking for performance and you need all the data at once, you should use the left outer join and process the data yourself in the DAO. Of course this means that you're not making use of the ORM system.

If you need only small parts of that "tree" at one time, do use lazy loading.

So please share what do you want to obtain and maybe someone with some more experience can share his knowledge. I'm curious too about this one.

Cristian

[Bhavin Sanghani]

Hi,

Thanks for your reply.

Yes, actually I have many joins and thought to use left outer join in DAO and process the data. But simultaneously I did not like that I am not able to use ORM here. I am confused whether its a limitation of ORM to support this kind of functionality? Or explicit left outer join is much more powerful in this case? Lazy loading would fire many queries while rendering the data of each node in depth...since at every tree level node type has different type...e.g. level 0 - ClassA, level 1 - Class B etc.

[Bhavin Sanghani]

What I am trying to achieve is : Allowing admin to create dynamic questionairre form. So, admin can create Pages, Sections, Questions, Answers and associate Rules with them.
[ December 26, 2008: Message edited by: Bhavin Sanghani ]