php - 从给定的父 id PHP 中查找所有子节点和孙节点

Question

我在推荐系统工作。假设用户 A 邀请用户 B 和用户 C。然后用户 B 邀请用户 D，用户 C 邀请用户 E，依此类推。我如何遍历所有子节点和孙节点？

这是我数据库中的示例数据

ID       name       referral_id
1        user A     null//assume the tree starts from here
2        user B     1
3        user C     1
4        user D     2
5        user E     3

我如何创建一个函数来检索用户 A 作为父节点，然后列出用户 B(具有子节点 D)和用户 C(具有子节点 E)？

我正在使用 CI，这是我从父 ID 检索 2 个子节点的查询

$downline_query = $this->db->query("SELECT * FROM user WHERE referral_id = '1'")
//above query will return user B and C as result.. hardcoded for example

$downline = array();
foreach($downline_query as $result){
   $downline[] = array( 
       "name" => $result['name'],
       "id"   => $result['id']
   );
}
//above $downline array will only contain user B and C. I don't know where to go again after this step.. 

$records = array("data"=>$downline);
echo json_encode($records);

我不知道在这一步之后去哪里.. 已经完成了几种类型的算法，包括 while 循环和 foreach 循环。但它仍然不是那么“动态”。

请注意，我只需要所有子孙的姓名和 ID。它不一定是多维数组。只要我能得到所有的 child 、孙子和曾孙子就已经可以了..

我打算将所有的子孙等保存到 $downline 数组中。所以所有都将在 1 个数组中，没有多维数组

确实需要有关如何执行此操作的指导。以前从未做过这种层次结构的事情。谢谢 :)

Answer 1

这应该可以，假设 $downline_query 包含您需要的所有行。 IE 1 数据库查询。

$affilites = [];
$parents = []
foreach($downline_query as $row){
   if( $row['referral_id'] ){
       if( !isset(  $affilites[$row['referral_id']] ) ){
             $affilites[$row['referral_id']] = [];
       }
       $affilites[$row['referral_id']][] = $row;
   }else{
       $parents[$row['id']] = $row;
   }
}
// you now have 2 arrays 1 of parents, one of children. now just combine ( shown below )
$parent = [
    1 => ['ID'=>1,'name'=>'user A', 'referral_id' =>null],  
];

$affiliate = [
    1 => [
            ['ID'=>2,'name'=>'user B', 'referral_id' =>1],
            ['ID'=>3,'name'=>'user C', 'referral_id' =>1],
        ],
    2 => [['ID'=>4,'name'=>'user D', 'referral_id' =>2]],
    3 => [['ID'=>5,'name'=>'user E', 'referral_id' =>3]],
    5 => [['ID'=>16,'name'=>'user F', 'referral_id' =>5]],
];


print_r(getparents( $parent, $affiliate));

function getparents( $parent, $affiliate ){
    $result = [];
    foreach ($parent as $row ){
        $id = $row['ID'];

        $result[] = $row;

        if( isset($affiliate[$id]) ){
         //no children
            $result[$id]['children'] = getparents( $affiliate[$id], $affiliate ); //recurse
        }
    }
    return $result;
}

最终输出

 Array(
        [0] => Array(
                [ID] => 1
                [name] => user A
                [referral_id] =>
                [children] => Array(
                        [0] => Array(
                                [ID] => 2
                                [name] => user B
                                [referral_id] => 1
                                [children] => Array(
                                        [0] => Array(
                                                [ID] => 4
                                                [name] => user D
                                                [referral_id] => 2
                                                )

                                        )

                                )

                        [1] => Array(
                                [ID] => 3
                                [name] => user C
                                [referral_id] => 1
                                [children] => Array(
                                        [0] => Array(
                                                [ID] => 5
                                                [name] => user E
                                                [referral_id] => 3
                                                [children] => Array(
                                                        [0] => Array(
                                                                [ID] => 16
                                                                [name] => user F
                                                                [referral_id] => 5
                                                                )

                                                        )

                                                )

                                        )

                                )

                        )

                )

        )