• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

query for one to many relationship on multiple entities

 
Bhavin Sanghani
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 71
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic