laravel recursion display referred users by level/depth

Question

So I'm working with affiliate and doing fine with registration and saving who referred to a user, now I'm struggling in showing those users with referrals by Level.

Level is not save in the database, I'm thinking of it as incrementing in the logic area?

users table structure

id |  name  | sponsor_id |
 1 |  John  |    NULL    |
 2 |  Jane  |      1     |
 3 |  Jess  |      1     |
 4 |  Joe   |      2     |

so the output I want should be like this:

I am John, level 1 are who's sponsor_id is mine, and level 2 are who's sponsor_id is id's of whom I invited.

ID |  Name  |  Level  |
2  |  Jane  |    1    | 
3  |  Jess  |    1    |
4  |  Joe   |    2    |

where Jane & Jess's sponsor_id is mine, and Joe's sponsor_id is Jess

User has sponsor

public function sponsor()
{
    return $this->belongsTo('App\User', 'sponsor_id');
}

User has referrals

public function referrals()
{
    return $this->hasMany('App\User', 'sponsor_id');
}

user_4197505 · Answer

If MySQL 8 (very advised with hierarchical data like yours), you can do this with a recursive CTE :

WITH RECURSIVE CTE_users_levels(id, name, level) AS 
    ( 
          SELECT id, name, 0 as level
          FROM users 
          WHERE sponsor_id IS NULL 
          UNION ALL 
          SELECT u.id, u.name, cte.level+1 
          FROM CTE_users_levels cte 
          JOIN users u ON cte.id=u.sponsor_id
    ) 

-- To output all users' levels:
SELECT * FROM CTE_users_levels ORDER BY level;

Now you have virtual table containing the level column for all your users and you can query it

| id  | name | level |
| --- | ---- | ----- |
| 1   | John | 0     |
| 2   | Jane | 1     |
| 3   | Jess | 1     |
| 4   | Joe  | 2     |

DBFiddle

GOing further...

Make a view out of your CTE

CREATE VIEW VIEW_User_Levels AS
(
WITH RECURSIVE CTE_users_levels(id, name, level) AS 
    ( 
          SELECT id, name, 0 as level
          FROM users 
          WHERE sponsor_id IS NULL 
          UNION ALL 
          SELECT u.id, u.name, cte.level+1 
          FROM CTE_users_levels cte 
          JOIN users u ON cte.id=u.sponsor_id
    ) 
SELECT * FROM CTE_users_levels ORDER BY level   
  
 )

Now it's easy to get all your users levels (no need to have the CTE statement) :

SELECT * FROM VIEW_User_Levels

Then, if you have a lot of users, it's a bit ovekilling to recompute the whole tree all the time (which the VIEW does)

Then you can fix the level of your users in a new column.

ALTER TABLE users ADD level INT;

And populate that column for all users with the help of your view:

UPDATE users u, VIEW_User_Levels v
SET  u.level=v.level
WHERE u.id=v.id;

Which gives

SELECT * FROM users

id  name    sponsor_id  level
1   John    null        0
2   Jane    1           1
3   Jess    1           1
4   Joe     2           2

DBFiddle

If you have a lot of users, and your aim is to query the level column a lot, INDEX IT.

Note that you can also update the level value of only one user (for instance if its sponsor_id changes, or if the user is new)

UPDATE users u, VIEW_User_Levels v
SET  u.level=v.level
WHERE u.id=v.id AND u.name='Jane';

laravel recursion display referred users by level/depth

1 Answers