I am currently building a web ap[ using Spring Boot to display family tree data (similar to Ancestry.com). However, I ran into a problem with the actual display of the data. The way I store the family data is in MySQL, where I have a tree that has a list of core families (parents and their kids). My problem is that when I want to put all the families together so that the core families would combine properly, I can't think of an efficient way of doing so, becuase the only thing that tells me the role of each person in families are pointers of whether that individual is a child in the family or a spouse (most people have both, as they are both).
However, I don't have experience in using complex MySQL queries. Therefore, I was wondering if I could be pointed at resources or if someone could help me in coming up with a good query. I tried some stuff, but they didn't work, the most recent query I tried to create (with support from chatGPT) gave me this error: Error Code: 1292. Truncated incorrect DOUBLE value: '@I182485067792@'. From what I understood it is because I am doing something wrong in the comparison in my query.
The query I used was:
WITH RECURSIVE FamilyHierarchy AS (
SELECT
f.id,
f.family_pointer,
f.marriage_date,
f.husband_id,
f.wife_id,
CAST(i.individual_pointer AS CHAR) AS child_token,
CAST(i.individual_pointer AS CHAR) AS spouse_token,
0 AS generation
FROM
tree_families tf
JOIN Family f ON f.id = tf.families_id
JOIN Individual i ON i.family_child_token = f.family_pointer OR i.family_spouse_token = f.family_pointer
WHERE
tf.tree_id = 9 -- Specify the tree ID here
UNION ALL
SELECT
f.id,
f.family_pointer,
f.marriage_date,
f.husband_id,
f.wife_id,
CAST(i.individual_pointer AS CHAR) AS child_token,
CAST(i.individual_pointer AS CHAR) AS spouse_token,
fh.generation + 1
FROM
FamilyHierarchy fh
JOIN Family f ON f.husband_id = fh.child_token OR f.wife_id = fh.child_token
JOIN Individual i ON i.family_child_token = f.family_pointer OR i.family_spouse_token = f.family_pointer
)
SELECT
fh.id,
fh.family_pointer,
fh.marriage_date,
fh.generation,
h.name AS husband_name,
w.name AS wife_name,
c.name AS child_name
FROM
FamilyHierarchy fh
LEFT JOIN Individual h ON fh.husband_id = h.individual_pointer
LEFT JOIN Individual w ON fh.wife_id = w.individual_pointer
LEFT JOIN Individual c ON fh.child_token = c.individual_pointer
ORDER BY
fh.generation;
MMy table structure that is related to the tree is:
What sort of query do I need to create so that I would recieve in Spring Boot a tree object that has all the families in the correct order ready to displayed in HTML?
