Writing a complex MySQL query for a Spring Boot application

Question

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?

In my experience, join conditions with OR tend not to scale well. — Taylor, Jul 06 '23 at 17:28
Sorry, prev comment was cutoff. I would consider a structure where your joins are not ambiguous, i.e. a relationship object that builds the connection between individual and family, and is typed e.g. as spouse or child. — Taylor, Jul 06 '23 at 20:12
@Taylor Normally I just use the built-in commands of JPA, such as find tree by id. However, while that returns the whole tree as I want, the are families out of order. I was hoping to use the recursive function to return the tree with the families in order. If that is not a good way to approach this, what would you recommend? — namer, Jul 09 '23 at 14:51
recursive is fine, but those join conditions won't scale well. The error you're getting is syntactical in nature, to deal with that, take a look at similar questions on SO https://stackoverflow.com/questions/16068993/error-code-1292-truncated-incorrect-double-value-mysql. It looks like it's a type conversion problem — Taylor, Jul 09 '23 at 17:54
@Taylor if it doesn't scale well, is there another way I can approach this so that it will work well regardless of size of data? — namer, Jul 10 '23 at 11:19
I already explained. Create a relationship table with a family id, an individual id and a type. — Taylor, Jul 10 '23 at 21:56

Writing a complex MySQL query for a Spring Boot application

0 Answers0