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. I tried to write the sql to get me just the ancestors to start off with, but even that is not coming out right. Here's the (incorrect) SP, and just below that is the C# code that works correctly:
// INCORRECT SQL CODE:
-
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
-
// C# CODE THAT WORKS
// ASSUMPTIONS
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 username of the user's mother
-
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);
-
}
-
}
