I have a table in which the foreign key refers to the id of the same table. I need to find the children of the first element that pass me as a parameter, until I reach a certain level.
I need to find all the children of one element.
The first time I had the ID_FK, but after, I
" ";"ID";"ID_FK";"ISLAST"
"1";"12519";"12518";"N"
"2";"12520";"12518";"N"
"3";"12521";"12518";"N"
"4";"12522";"12518";"N"
"5";"12523";"12518";"N"
But afterwards, I have to find each of the results of the ID column, in the ID_FK field, until I find an S in the ISLAST column.
"ID";"ID_FK";"ISLAST"
"12543";"12519";"N"
Recursive until:
"ID";"ID_FK";"ISLAST"
"12519";"12568";"S"
The result:
" ";"ID";"ID_FK";"ISLAST"
"1";"12519";"555018";"s"
"2";"12520";"112318";"s"
"3";"12521";"128818";"s"
"4";"12522";"133888";"s"
"5";"12523";"125888";"s"
I need a query to do this, to be able to pass it to JPA, or else, a way or some idea of how to do it with JPA directly with Entities and recursion.
JPA does not support recursion.
You will have to use SQL.
Here is the documentation of PostgreSQL and its recursive functionality:
https://www.postgresql.org/docs/current/queries-with.html
And here the documentation of Oracle for recursive queries:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm