version 1.0.2005.1 final (self-servicing) VS2005 asp.net 2.0

morning :-)

I have a table of a family tree

tblPerson personId motherId FK tblPerson.personId fatherId FK tblPerson.personId sexId FK tblSex.sexId dateOfBirth

etc

I need to be able to search the family tree of a particular person. I think that the above schema would work.I'm not sure how the predicate would work though.

In essence, I need to be able to return: 1) the mother of the person 2) the father of the person 3) the maternal grand mother 4) the maternal grand father 5) paternal grand mother 6) paternal grand father

Do I need 2 separate recursive queries, differing by tblPersonFields.SexId?

Any help much appreciated

ta,

yogi

The easiest way I can think of doing this is creating a TypedList with the tlblPerson and n (depending the number of level you wanna reach in each call) self (left) joins (remember adding aliases so we can easily differentiate the relationships later on), and in the Fields Mapped on entity fields tab add the fields you wish to retrieve for every level of the tree (let's say FirstName, MiddleName) and then you can aliased these fieldNames the way you like the most (FatherFirstName, GrannieFirstName).

And then from your BL create a method (let's say FamilyTreeTypedList ObtainFamilyTreeViaPersonId(int personId){....})

And from there you add the predicate to match the value of the pesronId sent in the method parameters with the first level of the relationships (the one with no alias maybe)

this way you'll obtain a TypedList fetched with only one record (hopefully). Some of the fields would be null of course like in the upper level of the tree.

You could also add the sex in the predicate.

In other words each time you call this method you'll obtain the whole family branch of predecessors of a person.

Like I said in the beginning this may not be the prettiest way to do it but sure it works, the other ways I can think of is a recursive function as you suggested or playing with coalesce in the joins or even a function that uses reflection in order to construct the right predicate.

Buenos,

Thanks for the reply. I've realised that Iprobably won't be able to have "fatherId" / "motherId" as a relation in tblPerson.

The reason I say this is that I obviously can't go back to the dawn of time with the family tree.Obviously, at some stage, the "fatherId" and "motherId" are going to be NULL.

Will this affect the approach that I should take?

many thanks,

yogi

I've realised that Iprobably won't be able to have "fatherId" / "motherId" as a relation in tblPerson.

The reason I say this is that I obviously can't go back to the dawn of time with the family tree.Obviously, at some stage, the "fatherId" and "motherId" are going to be NULL.

Will this affect the approach that I should take?

This will have no effect. As you can have nullable FKs.

hiya,

Ok, I think I've made a bit of progress.Thanks.

I've created "motherId" and "fatherId" as nullable FK's.

In the designer, I've create the typedList "tListFamilyTree" In the "Entities selected for this tyedList" section,

the tblPerson is listed 3 times.Is this correct?

I believe it should be listed:

1) for the current person 2) for the tblPerson.personId - MOTHERAlias.personId (m:1) 3) for the tblPerson.personId - FATHERAlias.personId (m:1)

(Let's forget about making the relationship any deeper for moment,If I can get the father mother hierarchy working, then that'll be a good start)

In the "selected used relation" section:

tblPerson - MOTHERAlias (m:1) tblPerson.motherId (m:1) MOTHERAlias.personId

In the "selected used relation" section:

select alternative FATHERAlias.motherId - tblPerson.personId (m:1)

FATHER - tblPerson (m:1) FATHERAlias.motherId (m:1) - tblPerson.personId

I'm hoping that the above is correct.

Goose, when you say...

and n (depending the number of level you wanna reach in each call) self (left) joins

..Do you mean that I shouls use "left" as a join hint?

many thanks,

yogi

...yes, you must use left joins because of the nullable FK's.