In MySql, It wasn't easy enough to get all the children's for specific nodes till now. Yes, I'm talking about MySql 8's "with recursive CTE".

Let me explain a scenario, consider this organogram

(consider emp_12) In department A, an employee may have "MANAGER" role but In department B, the same employee may have "COO" role

Now all i need to know all the direct child employee of specific node.

Please take some time yourself to think how you gonna find this and after that return and compare your thinking

Here's the rescue.

with recursive children as (
                select e.name, er.employee_id, r.title
                from employee_role er 
                    inner join employee e on er.employee_id = e.id
                    inner join role r on er.role_id = r.id
                where parent_employee_id = '$employeeId' and department_id = '$departmentId'
                union
                select e2.name, er2.employee_id, r2.title
                from children c 
                    join employee_role er2 on er2.parent_employee_id = c.employee_id
                    join employee e2 on er2.employee_id = e2.id
                    join role r2 on er2.role_id = r2.id
                where department_id = '$departmentId'
            ) select * from children

Do you've any concern about the speed? If so, then keep the concern away. It's super fast.

If you're curious and want to test yourself, here's the SQL to get you started.

Our Amazing Sponsors

View Website

DigitalOcean offers a simple and reliable cloud hosting solution that enables developers to get their website or application up and running quickly.

View Website

Laravel News keeps you up to date with everything Laravel. Everything from framework news to new community packages, Laravel tutorials, and more.

View Website

A Laravel Starter Kit that includes Authentication, User Dashboard, Edit Profile, and a set of UI Components. Learn more about the DevDojo sponsorship program and see your logo here to get your brand in front of thousands of developers.

Organogram Sql

In my test, I pulled 500 records from 32000 records in my local and it took lees then 200ms. and my query was little bit different then the avobe

select sum(amount) amount, depth, count(user_id) totalUser
            from (
                     with recursive children as (
                         select levelUser.user_id, inv.amount, r.id depth
                         from level_user levelUser
                                  inner join levels r on levelUser.level_id = r.id
                                  inner join invests inv on inv.user_id = levelUser.user_id
                         where parent_user_id = $this->currentUserId
                         union
                         select levelUser2.user_id, inv2.amount, r2.id depth
                         from children c
                                  inner join level_user levelUser2 on levelUser2.parent_user_id = c.user_id
                                  inner join levels r2 on levelUser2.level_id = r2.id
                                  inner join invests inv2 on inv2.user_id = levelUser2.user_id
                     )
                     select *
                     from children
                     group by user_id, amount, depth
                 ) as main
            group by depth