consider the following schema
@Entity
Class employee{
@OneToMany()
List<employee> manaagedEmps;
@OneToOne
employee manager;
}
how to write a query that get all the managed employee for a certain manager , direct(the list of managedEmps) and indirect (managed by managed employee).
It seems that JPA does not support recursive queries. Recently I solved the smilar problem by adding "path" field of type ltree (postgresql). Path is generated by adding id separated by dot to path of parent and path of root nodes is just id. With that field you are able to query subtree (direct and indirect employees) of some node (manager):
SELECT * FROM nodes WHERE path ~ '*.42.*{1,}'; /* for path of type ltree */
SELECT * FROM nodes WHERE path LIKE '%.42.%'; /* for path of type varchar */
The following JPQL query returns flat list of subs for employee with id 2.
List<Employee> subs = em.createQuery(
"SELECT e FROM Employee e LEFT JOIN FETCH e.subs WHERE e.path LIKE '%.' || ?1 || '.%'",
Employee.class
).setParameter(1, '2').getResultList();
//Returns a list of the managed employee of the manager with the specified ID.
@NamedQuery(name="queryName", query="SELECT p.managedEmps FROM employee p WHERE p.manager.uuid = :uuid")
I am using postgresql here.
I did this through native query like this:
Suppose following entity
@Entity
@Table(name = "employee")
public class Employee {
@Id
private Long id;
@ManyToOne
@JoinColumn(name = "parent_id")
private Employee parent;
}
Now, following query can be used to get all childs and sub childs under one manager recursively:
public interface IEmployeeRepository extends JpaRepository<Employee, Long> {
@Query(value = "with recursive subordinates as ("
+ " select e1.id as id, e1.parent_id as parent from employee e1 where e1.parent_id = :parentId"
+ " union"
+ " select e2.id, e2.parent_id from employee e2"
+ " inner join subordinates s on (s.id = e2.parent_id)"
+ " ) select * from subordinates", nativeQuery = true)
Collection<Employee2> getChilds(@Param("parentId") Long parentId);
public static interface Employee2 {
Long getId();
Long getParent();
}
}
Now, you have to convert this result Collection into List in your service layer. That's it.
References: postgres recursive queries Jpa Projections to get result
Hope this helps.
I usually prefer to offer some code, but in this case I think the article itself does a better job of explaining.
