Get downlines from a particular user in Hibernate or SQL

Question

I have a User table which looks like below

UserID  Name    SponsorID (FK)
1       A       null
2       B       1
3       C       1
4       D       3

The SponsorID refers to UserID. Now I need write a query which returns all user who is descendant of a given UserID.

Example

For UserID 1 the query returns all 4 users
For UserID 3 the query should return 1 user

The current implementation is getting the user list by looping each direct downline and I am looking for a better solution if it's possible.

UPDATE

Current code

public void findDownlineSponsorByUserBO(UserBO rootBO) throws Exception {
    List<UserBO> downlines = businessOperationService.findUserBySponsorId(rootBO.getId(), "createdDate", false);
    memberList.addAll(downlines);
    for (UserBO memberBO : downlines) {
        findDownlineSponsorByUserBO(memberBO);
    }

}

Add your current implementation code. – Pரதீப் Nov 22 '14 at 12:37 — Pரதீப், Nov 22 '14 at 12:37
updated my question with the current code. – abiieez Nov 22 '14 at 13:00 — abiieez, Nov 22 '14 at 13:00

score 1 · Accepted Answer · edited May 23 '17 at 12:12

You're going to have to use an iterative or recursive solution here, unless (perhaps) you're limited to one level of sponsor and can relate UserID to SponsorID in one join. You could load the table into a tree structure in memory, and then query that. Loading it would be O(nlogn), but then traversing it would be O(logn).

This other SO question might give you some useful ideas: Is it possible to query a tree structure table in MySQL in a single query, to any depth?

Get downlines from a particular user in Hibernate or SQL

1 Answers1