Swami
3/19/2007 4:47:00 AM
I wrote an algorithm to get all the relatives of a person in a family tree.
I'm basically getting all the users from the DB and am doing the recursive
logic in code, so that there is only 1 call made to the DB. However, I am
trying to do the same thing within a stored procedure in SQL using recursive
CTEs (I think the performance might be better) but I'm finding it really
tough to craft the CTE. I would really appreciate if someone could translate
the following code to a recursive CTE in SQL. I tried to write the SP myself
but I am not getting the right results. Here's the (incorrect) SP, and just
below that is the C# code that works correctly and an explanation of my
schema:
ALTER PROCEDURE [dbo].[spGetFamilyMembersForUser]
-- Add the parameters for the stored procedure here
@UserName varchar(50)
AS
BEGIN
declare @familyid int
set @familyid = (select AsChildFamilyID from Users where UserName =
@UserName);
WITH Ancestors (UserName, Gender, AsChildFamilyID, AsSpouseFamilyID,
AsParentFamilyID) AS
(
-- Base case (get father of user)
SELECT u.UserName, u.Gender, u.AsChildFamilyID, u.AsSpouseFamilyID,
u.AsParentFamilyID
FROM Users u
WHERE u.AsParentFamilyID = @familyid AND u.Gender = 'Male'
UNION
-- Base case (get mother of user)
SELECT u.UserName, u.Gender, u.AsChildFamilyID, u.AsSpouseFamilyID,
u.AsParentFamilyID
FROM Users u
WHERE u.AsParentFamilyID = @familyid AND u.Gender = 'Female'
UNION ALL
-- Recursive step
SELECT u.UserName, u.Gender, u.AsChildFamilyID, u.AsSpouseFamilyID,
u.AsParentFamilyID
FROM Users u
INNER JOIN Ancestors a ON a.AsChildFamilyID = u.AsParentFamilyID
WHERE u.AsChildFamilyID <> -1
)
SELECT * FROM Ancestors
WHERE Ancestors.UserName <> @UserName
END
//FYI
1. _dtAllUsers contains all users
2. _dtAllRelatives is initially empty, and is a clone of _dtAllUsers
3. The Users table has the following columns:
UserName, Gender, AsChildFamilyID, AsSpouseFamilyID, AsParentFamilyID
AsChildFamilyID means the family(ID) in which the user is a child. Similarly
for AsSpouseFamilyID and AsParentFamilyID.
A value of -1 means that the user does not have that role in the family. For
instance, if AsChildFamilyID is -1, then user is not a child in that family.
Example:
UserName, Gender, AsChildFamilyID, AsSpouseFamilyID, AsParentFamilyID
Joe, Male, 1, -1, -1
Jack, Male, 2, 1, 1
Jill, Female, -1, 1, 1
Jim, Male, -1, -1, 2
In this example, Jack and Jill are Joe's parents. Jim is Jack's father.
// POST-CONDITION
1. _dtAllRelatives contains all relatives for user
public void ComputeAllRelativesForUser(string userName)
{
DataTable dtUser = UserManager.GetUser(userName);
int asChildFamilyID = (int)dtUser.Rows[0]["AsChildFamilyID"];
int asSpouseFamilyID =
(int)dtUser.Rows[0]["AsSpouseFamilyID"];
int asParentFamilyID =
(int)dtUser.Rows[0]["AsParentFamilyID"];
string motherUserName = string.Empty; //the user's mother's
username
string fatherUserName = string.Empty;
string spouseUserName = string.Empty;
string siblingUserName = string.Empty;
string childUserName = string.Empty;
int fathersAsChildFamilyID = -1; //the AsChildFamilyID of
user's father
int mothersAsChildFamilyID = -1;
int spousesAsChildFamilyID = -1;
int siblingsAsChildFamilyID = -1;
int childsAsChildFamilyID = -1;
foreach (DataRow row in _dtAllUsers.Rows)
{
//get Father
if ((int)row["AsParentFamilyID"] == asChildFamilyID &&
asChildFamilyID != -1 && (string)row["Gender"] == "Male")
{
_dtAllRelatives.Rows.Add(row);
fatherUserName = (string)row["UserName"];
fathersAsChildFamilyID =
(int)row["AsChildFamilyID"];
}
//get Mother
if ((int)row["AsParentFamilyID"] == asChildFamilyID &&
asChildFamilyID != -1 && (string)row["Gender"] == "Female")
{
_dtAllRelatives.Rows.Add(row);
motherUserName = (string)row["UserName"];
mothersAsChildFamilyID =
(int)row["AsChildFamilyID"];
}
//get Spouse
if ((int)row["AsSpouseFamilyID"] == asSpouseFamilyID &&
asSpouseFamilyID != -1)
{
_dtAllRelatives.Rows.Add(row);
spouseUserName = (string)row["UserName"];
spousesAsChildFamilyID =
(int)row["AsChildFamilyID"];
}
//get Sibling
if ((int)row["AsChildFamilyID"] == asChildFamilyID &&
asChildFamilyID != -1)
{
_dtAllRelatives.Rows.Add(row);
siblingUserName = (string)row["UserName"];
siblingsAsChildFamilyID =
(int)row["AsChildFamilyID"];
}
//get Child
if ((int)row["AsChildFamilyID"] == asParentFamilyID &&
asParentFamilyID != -1)
{
_dtAllRelatives.Rows.Add(row);
childUserName = (string)row["UserName"];
childsAsChildFamilyID = (int)row["AsChildFamilyID"];
}
//recursive for father
if (fathersAsChildFamilyID != -1)
ComputeAllRelativesForUser(fatherUserName);
//recursive for mother
if (mothersAsChildFamilyID != -1)
ComputeAllRelativesForUser(motherUserName);
//recursive for spouse
if (spousesAsChildFamilyID != -1)
ComputeAllRelativesForUser(spouseUserName);
//recursive for sibling
if (siblingsAsChildFamilyID != -1)
ComputeAllRelativesForUser(siblingUserName);
//recursive for child
if (childsAsChildFamilyID != -1)
ComputeAllRelativesForUser(childUserName);
}
}