How can I get the reporting hierarchy of an employee in an organization using Java?

Java
1

I have an Employee/User table as shown below:

image

I want to update the HIERARCHY column as follows:

image

How can I achieve using Spring Boot, JPA, and Java.

2

@naveen.gupta
I am not expert in Java but what you can apply this pseudocode in batch process in any language. It is a recursive function

FUNCTION - EMP_HIERARCHY_SET
For Given User: EMP_1

  1. LET $HIERARCHY = “”
  2. Find SUPERVISOR_ID ROW:
  3. IF SUPERVISOR_ID == NULL | EMPTY
  4. SET $HIERARCHY = ~EMP_1 && UPDATE ROW
  5. RETURN $HIERARCHY
  6. ELSE IF SUPERVISOR_ROW.HIERARCHY == NULL && SUPERVISOR_ID IS NOT NULL
  7. REPEAT 1-5
  8. ELSE SET $HIERARCHY = SUPERVISOR_ROW.HIERARCHY~EMP_1 && UPDATE ROW
  9. RETURN $HIERARCHY

Run EMP_HIERARCHY_SET for each records.

It is not efficient but I assume this is a one time activity post that any new employee hierarchy can be easily created by picking his managers “HIERARCHY” column and appending “~EMP_ID” to it.

Apart from this, there are some libraries which actually does the same in some frameworks. Assuming these are employee records, there is limit to a number of employees. Therefore, this should be executed pretty fast despite having an order of O(n2)

3

@naveen.gupta You can also achieve the same with the below query

Look up database schema

employee

To update the hierarchy column in the employee table based on the manager_id and emp_id reference, you can use a recursive CTE (Common Table Expression) in PostgreSQL. Here is an example query:

WITH RECURSIVE employee_hierarchy AS (
  SELECT emp_id, manager_id, emp_id AS hierarchy
  FROM employee
  WHERE manager_id IS NULL -- Assuming NULL manager_id represents top-level employees
  
  UNION ALL
  
  SELECT e.emp_id, e.manager_id, eh.hierarchy || '/' || e.emp_id
  FROM employee e
  INNER JOIN employee_hierarchy eh ON e.manager_id = eh.emp_id
)
UPDATE employee e SET hierarchy = eh.hierarchy
FROM employee_hierarchy eh
WHERE e.emp_id = eh.emp_id;

This query uses a recursive CTE called employee_hierarchy to traverse the employee table recursively, starting from the top-level employees (those with a NULL manager_id). It concatenates the emp_id values with the hierarchy path from the higher-level employees.

Then, the UPDATE statement updates the hierarchy column in the employee table by joining it with the employee_hierarchy CTE using the emp_id.

Please note that you need to replace the table and column names (employee, emp_id, manager_id, hierarchy) with the actual names used in your database schema.