I have some tables where I store parent child relationships that go into the same table, allowing me to build multi-level relationships with a single table.

An example for this looks like this:

Company

This table allows me to build the following relationships:

1. Daimler
     2. Mercedes
        4. Mercedes Plant Stuttgart
     3. Smart
5. Volkswagen Group
     6. Audi
     7. Porsche

I have a few other tables that follow the same architecture. Now while building the relationships, top-down, is easier, I have to build those relationships bottom-up too, and then create an array of those elements.

While creating the bottom-up list, I have a array of ids, for which I have to filter the bottom most elements first.

Say for example, I have an array of ids (4,7). The list should filter out all unnecessary elements, and return the following:

1. Daimler
     2. Mercedes
        4. Mercedes Plant Stuttgart
5. Volkswagen Group
     7. Porsche

Since there are multiple levels, and I cannot assume that a relationship can end at a certain level,I have to assume all possibilities.

Using a top-down approach and loading all the possible relations and then filtering out the ids, is something I have already tried and it turns out to be very slow and resource intensive due to the amount of data that needs to be loaded and then filtered.

Using the bottom-up approach seems to be the most efficient way to do this. And so far I think if I can achieve the following result from my SQL, I can do the rest using Java.

(Assuming I only provide ids 4 and 7 to my SQL parameters, it returns its parent, and its parent and so on, in the result list)

SQL Result

I have tried using crosstab for this, but could not achieve much. As I want to use this in JPA Postgres and use the SQL for multiple other tables too, I am trying to build something generic (function?, stored procedure?) to use in all the tables that have this structure.

CodePudding user response：

For a single ID you can just write a SQL recursive query (there are endless examples of how to do this on the internet, if you've not written a recursive query before). If you want to get the results for multiple IDs in one go then you'd need to put the recursive query inside a loop in an SP and pass each ID into the next iteration of the loop - adding the result from each iteration of the loop to a final resultset

CodePudding user response：

I have solved the problem for now. I was not aware of the term, Adjacency List, which is implemented here.

As soon as searched using this, I was able to find a solution for the problem.

For anyone else wondering how to this, I did this with the following recursive query in postgres:

WITH RECURSIVE RESULT_ROW AS (SELECT ID, NAME, ID_PARENT
                             FROM COMPANY
                             WHERE ID = 758
                             UNION
                             SELECT C.ID, C.NAME, C.ID_PARENT
                             FROM COMPANIES C
                                      INNER JOIN RESULT_ROW CS ON CS.ID_PARENT = C.ID)
SELECT *
FROM RESULT_ROW;