I have a business requirement to write a service that returns the list of child agent codes from the given agent code. this agent_details table as below.

This table agent_code is unique, but reporter_code can be duplicated. every agent has a reporter. Also, the reporter is an agent. According to this table agent and reporter are linked as follows.

I need a service to get the list of child agents by giving agent code. As example when giving 1011 that should returns [1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 1010]

when giving 1005 that should returns [ 1002, 1003].

I am using Jpa repository for this project. my entity class and repository class as follows. How to solve this problem sql query or jpa repository?

@Data
@Entity
@Table(name = "agent_details")
public class AgentDetails  {
    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(name = "id", unique = true, nullable = false)
    private Long id;

    @Column(name = "agent_code", nullable = false, length = 16)
    private String agentCode;

    @Column(name = "reporter_code", nullable = false, length = 16)
    private String reporterCode;

    @Column(name = "branch", nullable = false, length = 32)
    private String branch;
}

public interface AgentDetailsRepository extends JpaRepository<AgentDetails, Long>
{
    AgentDetails findByAgentCode(String agentCode);

    List<AgentDetails> findByReporterCode(String reporterCode);

}

CodePudding user response：

You could try this (Common Table Expression): ex.: '1011' as your dynamic parent node.

    with recursive
    cte
      (childs) 
    as
      ( select agent_code as childs
        from agent_details where agent_code = '1011'
      union all
        select  g.agent_code as childs
        from cte  a
        join agent_details g
        on a.childs = g.reporter_code
      ) 
    
    select childs as parent_and_childs from cte;

and then create a method in your repository that recieve a List<String>.