Mysql hierarchy count for deep more than 10 levels using php

Question

Am trying to make an referral system which has more than 10+ levels of hierarchy. The first level will have a 5 referrals. And next level will have 5*5 which would be 25 and by next level would be 25*5 = 125. So this is the way it goes on.

My concern is, how to track back and count number of referrals in each level from the last to the top parent id ?

 Root User
 -Level 1 
   - User1 
      - Level 1 
         - has 5 user 
            - Level 2 - has 25 users
   - User2 
       - Level 1 
         - has 5 user 
             - Level 2 - has 25 users
   - User3 
       - Level 1 
          - has 5 user, Level 2 
             - has 25 users
   - User4 
       - Level 1 
          - has 5 user 
             - Level 2 - has 25 users
   - User5 
       - Level 1 
           - has 5 user 
              - Level 2 - has 25 users

So according to the above example, when the user1, user2, user3, user4 and user5 reaches 5 people under each then the root users achieves 2 nd level which is 25 people and this tree goes on.

And what is the best approach to get the total count of users under root user ? whats the efficient way to find this. At this point i seriously dont have any idea how to approach this. So i dont have any code to show !Please guide

See [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/q/20215744). Summarizing: if you need unknown/unlimited depth, you need either a) MySQL 8+ or b) a data model that supports this (e.g. a nested set (linked somewhere in that question), or some of the other models mentioned that are not a linked list). — Solarflare, Jul 19 '19 at 07:50

Mahdi Youseftabar · Answer 1 · 2019-07-20T16:54:52.203

0

You can use something that I call Parent path:

add parent_id (nullable - unsigned int - foreign to user) and parent_path (string - nullable) to your users table.
create UserObserver for the user model
on user creation , add parent_id (id of its parent)
add parent relation to your user model

in your UserObserver:

public function creating(User $user)
{
    if ($user->isDirty('parent_id')) {
        $parentPath = null;
        if ($user->parent_id) {
            $parent = $user->parent;
            $parentPath = ($parent->parent_path ? ($parent->parent_path . '/') : '') . $parent->id;
        }
        $user->parent_path = $user;
    }
}

After adding a user to another user's referrals, you can find anything you like using the parent_path field.

For example imagine you have these users:

user 1, parent id is null
user 2, parent id is 1
user 3, parent id is 2

The users3's parent path will be '1/2' now you can have a user's parent tree with it's parent_path field and you can find all of a user's children using this query:

select * from users where parent_path like '%USER_ID%'

the benefits of this method is:

in creating of of user if it have parent , you just calling one query to get his parent and when you have it's parent , you have all of his parent by parent_path field !
for getting his parents later , you should just run one query ... no need to run recursive queries !!!

edited Jul 20 '19 at 16:54

answered Jul 19 '19 at 09:51

Mahdi Youseftabar

2,273
1
22
29

1

Thanks for answering, still i couldnt understand what your proposing in parent path ? I understand parent_id thing, but could not get parent path ? – Dazzile Pro Jul 19 '19 at 13:57
imagine you have these users: user1 , parent id is null user 2, parent id is 1 user3, parent id is 2 then in users3 parent path you will have : '1/2' in that case you will know all the parents of one user and also you van know all children of one user with this query " parent_path like '%USER_ID%' – Mahdi Youseftabar Jul 19 '19 at 14:46
Will that be efficient on long and deep depth ? For instance if the users level is 10 then it needs should be 10 reversal check to find the top id ? – Dazzile Pro Jul 19 '19 at 14:52
And how would we able to find all parents by using parent_path ? – Dazzile Pro Jul 19 '19 at 15:31
@DazzilePro when you getting a user , get parent_path and explode that by "/" then you have array of parent ids .. – Mahdi Youseftabar Jul 20 '19 at 09:11
@DazzilePro when you got parent ids , then you can run just one query with "whereIn('id',[1,2,3,4])" to get all parents ... so you just need to run 2 query for all your needed information ! – Mahdi Youseftabar Jul 20 '19 at 09:13
Understood, how would a parent ID for deep levels ? the example which you said has three users so users 3 would have parent_id as 1/2, what will the user 5 would have the path ? 1/2/3/4 ? – Dazzile Pro Jul 20 '19 at 09:21
And one more doubt, how can i get all parents paths if i am registering at level 10 of user ? As he will be having 10 parents , how can i retrieve the paths ? – Dazzile Pro Jul 20 '19 at 09:24
@DazzilePro if we have user 4 that has parent_id=3, for users 5 (with parent_id=4) we will have 1/2/3/4 but if user 5 had parent_id=3 then in user5 parent_path you will have : 1/2/3 – Mahdi Youseftabar Jul 20 '19 at 16:58
@DazzilePro the string in parent_path will show you a chain of parents until you see root node – Mahdi Youseftabar Jul 20 '19 at 16:59
1

So how to populate all path ? do i need to current path with previous path ? – Dazzile Pro Jul 20 '19 at 18:41
@DazzilePro for filling existing table, you should do it manually :( – Mahdi Youseftabar Jul 21 '19 at 15:08

Mysql hierarchy count for deep more than 10 levels using php

1 Answers1