Work With Recursive Database Relations

[olk]

Hello Community, maybe I'm a little bit over-worked, but I'm stucked in a problem with recursive database queries.

I have a DB table with from/to relations. Here is an excerpt for only one relation (from rel_item_id_from=3 to rel_item_id_to=3):

rel_id	rel_item_id_from	rel_item_id_to
1		52					3
2		3					17

My question is: How can I get a recursive solution to get the upward and downward children? It is also possible to have multiple relations (1:n or n:m).

Thanks for your assistance - OLK

[requinix]

Well, you could do it... you know... recursively. As in you do one query to find the next step's relations, then another query for the next, and another for the next, until you've gone as far as you want (or you run out of relations). Remember not to check IDs you've already seen before.

Depending on your database server, you may be able to write a recursive SQL query using CTEs...

[Barand]

If you want a recursive approach it is far more efficient to read all your data into a suitably structured array then process the array recursively.

[olk]

Thank you both for these quick and helpful suggestions.

My first goal was to give the heavy work to the Database. Unfortunately I have to dive into the world of "WITH" (CTEs). So I'll try Barands suggestion first.

Wish me luck 😉 and best regards!

[Phi11W]

8 hours ago, olk said:

I have to dive into the world of "WITH" (CTEs)

Depends on how many "levels" you need to work with.  

If you only need, say, immediately related parent or child records, or even as far as grandparent or grandchild, you can do that with a regular query, just joining the table to itself the required number of times.  

select ... 
from table1 parent
inner join tabel1 child 
      on parent.child = child.parent
inner join table1 grandchild 
      on child.child = grandchild.parent 
;

But, as soon as you start getting arbitrary depth of nesting, a CTE is the way to go. 

Also, bear in mind that you want some way of stopping this recursion. 
You have no guarantee that, eventually, someone won't manage to create a loop in your data!  (record1 -> record2 -> record3 -> ... -> record1). 

You can do this in the query itself or, perhaps better, with something (i.e. a Trigger) in the database to detect and reject the creation of such loops.  You might remember to write your query with this Gotcha! in mind, but there's no guarantee that the next Developer to work on this Application will do the same! 

Regards, 
   Phill  W.