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:
// 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 , AsSpouseFamilyI D, AsParentFamilyI D
AsChildFamilyID means the family(ID) in which the user is a child. Similarly for AsSpouseFamilyI D and AsParentFamilyI D.
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 , AsSpouseFamilyI D, AsParentFamilyI D
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
// INCORRECT SQL CODE:
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
// 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 , AsSpouseFamilyI D, AsParentFamilyI D
AsChildFamilyID means the family(ID) in which the user is a child. Similarly for AsSpouseFamilyI D and AsParentFamilyI D.
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 , AsSpouseFamilyI D, AsParentFamilyI D
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
Code:
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);
}
}