CODEWITHSUNDEEP
javasqljpa
digiarnie
digiarnie

Reputation: 23355

Recursively query column using JPA to find descendants of an object

Let's say I have a table called Person with the columns:

id, name, parent_id

and let's say I have some data like this:

1, Bob, null
2, Mary, 1
3, Tim, 1
4, Sally, 3

So Bob has 2 kids: Mary and Tim. And Tim has 1 kid: Sally (who grandparent is Bob)

What is the easiest way to write a JPA query such that I could find all descendants of Bob? (i.e. the result would return Mary, Tim and Sally)

Upvotes: 4

Views: 10183

Answers (4)

ceiroa
ceiroa

Reputation: 5973

In SQL Server 2008 the following query works to retrieve the level each element is at, assuming you table is named Hierarchical_Test1, and contains fields "id, name, parent_id":

with family_tree (parent_id, id, name, level) AS
(
-- Anchor member definition
select f.parent_id, f.id, f.name, 0 as level
from dbo.Hierarchical_Test1 f
where f.parent_id=0
union all
-- Recursive member definition
select f2.parent_id, f2.id, f2.name, level + 1
from dbo.Hierarchical_Test1 as f2
inner join family_tree as f3
on f2.parent_id = f3.id
)
-- Statement that executes the CTE
select parent_id, id, name, level
from family_tree

And this one gets you all the ancestors of an element:

with family_tree (parent_id, id, name, level, parent_path) AS
(
-- Anchor member definition
select f.parent_id, f.id, f.name, 0 as level, 
            CAST('/' as varchar(255)) as parent_path
from dbo.Hierarchical_Test1 f
where f.parent_id=0
union all
-- Recursive member definition
select f2.parent_id, f2.id, f2.name, level + 1, 
            CAST(
                CAST('/' as varchar(255)) 
                + CAST(f2.parent_id as varchar(255)) 
                + CAST(f3.parent_path as varchar(255))
            as varchar(255))
from dbo.Hierarchical_Test1 as f2
inner join family_tree as f3
on f2.parent_id = f3.id
)
-- Statement that executes the CTE
select parent_id, id, name, level, parent_path
from family_tree

Upvotes: 1

javagirl
javagirl

Reputation: 1675

I used nested sets for solving the same problem. Please see my question&answer : How to show tree-view replies to message? Java & Hibernate

I believe it's the optimal solution to query hierarhical data in the relational DBs.

Upvotes: 1

user330315
user330315

Reputation:

Most modern DBMS support hierarchical queries using recursive Common Table expressions. If you can pass a plain SQL statement to the JPA layer, this can easily be done with a single statement similar to the following:

WITH RECURSIVE people_tree (id, name, parent) as 
(
  SELECT id, name, parent_id
  FROM people
  WHERE parent name = 'Bob'

  UNION ALL

  SELECT p2.id, p2.name, p2.parent_id
  FROM people p2 
    INNER JOIN people_tree ON people_tree.id = p2.parent_id
)
SELECT * 
FROM people_tree
ORDER BY name;

Upvotes: 2

szhem
szhem

Reputation: 4712

I don't think you can do this with plain JPA.

But if you can modify your table like this

id, name, parent_path(string)

and your data will look like this

1, Bob, null
2, Mary, 1
3, Tim, 1
4, Sally, 1/3
5, John, 1/3/4
6, Huge, 1/3/4/5

then you can query for all the descendants using like clause. For example:

select p from Person p where p.parentPath like '1/%'

here 1 - is parentPath + entityId, so for Bob like clause looks like

like '1/%'

because Bob's parentPath is null and Bob's id is 1.

And for Sally the query will look like this

select p from Person p where p.parentPath like '1/3/4/%'

because Sally's parentPath is '1/3' and Sally's id is 4.

if you have to add a new child you just need to set its parent_path to

parent.parentPath + '/' + parent.id

Upvotes: 2

Related Questions