I need help with being able to associate a set of children records to their respective parents.

What I need to do is select a set of parent records, then using the same where clause select the children from another table using a join to pull back all the child records. Once I have all the parent and children records back I need to associate them to one another. I was using a bag collection with a sub-select, but I have a multiple key relation from the parent to the child, so I cannot use a sub-select (it generates sql that is not well-formed and if it was it does not hold the relation i need). I have tried the composite-id, but it is not working for me. I have started to use the sql-query, but I cannot figure out how to associate the children with the parent and to only execute the sql-query once.

Example:

I have 5 parent records, each parent has several children. My where clause to select the parents is "where lastUpdated_Date >= '7/2/2007'" I now want to execute the following command only once and then associate the children to the parents. "Select c.* from childTable c join parentTable p on p.key1 = c.key1 and p.key2 = c.key2 where p.lastUpdated_date >= '5/17/2007'"
So my query would return all the children for the 5 parents then associate them to each other.

I am dealing with thousands of records so selecting each set of children per parent is very slow. I was amazed how fast nhibernate was with the sub-select but like I cannot figure out how to use it with my situation.

I have read a ton of documentation over the weekend, but haven't found anything that I think will work. I am a newbie and could use some help.

Thanks a ton.

Nothing here worked for you?

First of all, thanks for your reply. I have not had any luck with this forum yet.

The sub-select doesn't work for me as I stated earlier. The select is very slow for me. I tried the join, which is what I had before I started using NHibernate and the sql it produced was the same as select, so it would only select the immediate parents children at a time (there was also no join in the sql it produced). I have not been able to find out how to use batch, it is not listed in my xsd for the bag collection.

Thanks

Right you are, everything there is based on Criteria queries. Instead of issuing two queries, one for the parents then another for children, just use a fetch join in your parent load.



See http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/queryhql.html#queryhql-joins

This is a little closer, but there are still some issues. First, the reason I wanted to select the children after the parents is so that I can select only child information, thus eliminating duplicate parent records. Also, I didn't think I would need to specify this but it turns out I do so I apologize for not stating this earlier. I do not just have one set of children per parent I have 3 different sets of children per parent. I tried using the method you posted with commas between each children but it throws an exception and adds to the duplicate parents. I know I am not going to get all 3 sets back in one select, but I am planning on 1 select for the parent and 1 for each child, so a total of 4.

Thanks

Okay. How about this: since this is a parent-child relationship, the children should carry a reference to the parent, so what if you try


?

Well I don't really have a reference to the parent, but I do have the parameters I need for the where clause. If I do it this way how do I relate the child set with their respective parents. I am assuming this method will load the children, but not automatically associate them to their parents, is this right?

Thanks

It depends on whether you make both calls within the same session (or Multi*, to really speed things up). I believe that the happy side effect would be that the linking would occur automatically, but now I'm out of my ken. You've exhausted my expertise (read: educated guessing) on this question. Experiment with a bidirectional relationship, let us know how it goes.

Okay I found a solution that worked for me (at least so far). So basically all these ORM tools at some point or another are a string builder. They take pieces from one xml tag and combine it with another and eventually you get a sql string. Well since I couldn't get it to produce the sql I wanted and still have my collections mapped. I "cheated" the system and got it to produce the sql I needed. Basically I wanted to do the join but without retrieving duplicate parent records. So what I did was in the table attribute of my child classes I set the value to be "parentTable, childTable" then in the where clause of the Bag attributes I did my join criteria to get it to produce the join i needed (i.e. where parentTable.key1 = childTable.key1 and parentTable.Key2 = childTable.key2 and 'the origianl criteria'"). I really appreciate all your help.

Thanks again