PHP层次数据优化

Question

我有一个表雇员，其中包含列：employee_id、name、employee_manager_id引用employee_id。这是一个层次数据。

我有这个输出使用PHP，但不能实现它只使用一个mySQL查询。到目前为止，我需要使用递归函数处理PHP中的数据，这样才能实现这种输出。

Sample Array Output 
0 => (
                employee_id => 2,
                name => Jerald,
                employee_manager_id => 1,
                depth => 1
            ),
        1 => (
                employee_id => 3,
                name => Mark,
                employee_manager_id => 2,
                depth => 2
            ), 
        2 => (
                employee_id => 6,
                name => Cyrus,
                employee_manager_id => 3,
                depth => 3
            ), 
        3 => (
                employee_id => 4,
                name => Gerby,
                employee_manager_id => 2,
                depth => 2
            )

到目前为止，这是我在PHP中实现上述输出的递归函数。

function get_employees_by_hierarchy( $_employee_id = 0, $_depth = 0, $_org_array = array() ) {
    if ( $this->org_depth < $_depth ) {
        $this->org_depth = $_depth;
    }

    $_depth++;
    $_query = "SELECT * FROM employees WHERE ";

    if ( !$_employee_id ) {
        $_query .= "employee_manager_id IS NULL OR employee_manager_id = 0";
    }
    else { 
        $_query .= "employee_manager_id = " . $this->dbh->quoteSmart( $_employee_id );
    }
    $_result = $this->query( $_query );

    while ( $_row = $_result->fetchRow() ) {
        $_row['depth'] = $_depth;
        array_push( $_org_array, $_row );
        $_org_array = $this->get_employees_by_hierarchy(
            $_row['employee_id'],
            $_depth,
            $_org_array
        );
    }
    return $_org_array;
}

我的问题是，是否存在这样我就可以使用一个mysql查询来实现我想要的数组输出？如果在mysql查询中不可能，那么在我当前的代码中还有需要优化的地方吗？

任何帮助都将不胜感激。

谢谢

Answer 1

您可以尝试嵌套的集合a.k.a。芹菜树，但插入和删除是非常昂贵的。还有闭包和路径枚举(物化路径)，但我不是专家。MySql不支持递归查询。

Answer 2

我认为在不对结果进行任何处理的情况下，您无法获得当前模型的深度，但您不需要进行多次查询。

假设$employees是由employee_id索引的员工列表，您可以这样做：

function set_employee_depth(&$employees, $id) {
    if (!isset($employees[$id]['depth'])) {
        $employee_manager_id = (int) $employees[$id]['employee_manager_id'];
        if (!$employee_manager_id) {
            $employees[$id]['depth'] = 0;
        } elseif ($employee_manager_id !== $id) {
            $employees[$id]['depth'] = 1 + set_employee_depth($employees, $employee_manager_id);
        } else {
            throw new \Exception('Employee cannot be its own manager!');
        }
    }
    return $employees[$id]['depth'];
}

foreach ($employees as $id => $employee) {
    set_employee_depth($employees, $id);
}

Answer 3

因此，您的表由3列(employee_id、name、employee_manager_id)组成。employee_manager_id是对employee_id的自我引用。您希望使用所有记录构造一个数组，添加一个名为depth的额外字段，该字段表示员工到“大老板”的距离，其中只有一个对数据库的查询。对吗？我还假设数据库结构不能更改。

如果这些假设是正确的，这是一个基本的HIERARCHICAL/TREE数据结构，因此，您有几种方法可以解决这个问题。

第一稿

第一个脚本按顺序运行结果数组，首先找到主节点/主干(大老板)，然后添加它的子节点，然后是孙辈等等。每当一个节点被“排序”时，它就会从循环中被删除，直到没有节点被剩下。它假定：

• 没有孤儿记录(managers_ids无效的雇员)
• 没有循环引用，无论是简单的(A是B的经理，B是A的经理)还是复杂的(*B的经理B，B的C的经理和C的C经理的A)
• 每个路径(从主节点到最后一个节点)都可以有无限多的节点。
• $results是通过运行简单的查询SELECT * FROM employees ORDER BY employee_manager_id生成的。

代码：

$finalArray = array();
$limit = count($results);

while (count($results) > 0) {
    $results[0]['cnt'] = isset($results[0]['cnt']) ? $results[0]['cnt']++ : 0; // set num of times each element was already visited
    if ($results[0]['cnt'] === $limit) { //prevent an infinite cycle
        break;
    }
    $manId = $results[0]['manager_id'];
    if ($manId === null) {
        $results[0]['depth'] = 0;
    } else if ( ($key = searchForId($manId, $finalArray)) !== null ) {
        $results[0]['depth'] = $finalArray[$key]['depth'] + 1; //use the depth of parent to calculate its own
    } else {
        $results[] = $results[0]; //parent was not visited yet so we add it to the end of array
        array_shift($results);
        continue;
    }
    unset($results[0]['cnt']);
    $finalArray[] = array_shift($results);   
}

function searchForId($id, $array) {
   foreach ($array as $key => $val) {
       if ($val['id'] === $id) {
           return $key;
       }
   }
   return null;
}

这个脚本非常简单。它只对DB运行一个查询。在最好的情况下，它只遍历数组一次。在更糟糕的情况下，它将访问count(array) - 1**， element ，这对于大数组来说可能比较慢。**但是，由于结果是预先排序的，最好的情况可能会更常见。

第二稿

第二个脚本构建一个实际的元素树。这有点复杂，但也取得了类似的结果。并对深度进行了动态计算。

class Employee {
    public $id;
    public $name;
    public $manager;
    
    public function __construct($id, $name, Employee $manager = null) {
        $this->id = $id;
        $this->name = $name;
        $this->manager = $manager;
    }
    
    public function setManager(Employee $manager) {
        $this->manager = $manager;
    }
    
    public function getDepth() {
        if ($this->manager === null) {
            return 0;
        } else {
            return $this->manager->getDepth() + 1;
        }
    }
}

$finalArray = array();
$paths = array();


foreach ($results as $r) {
    $finalArray[(int) $r['id']] = new Employee((int)$r['id'], $r['name']);
    if ($r['manager_id'] !== null) {
        $paths[(int) $r['id']] = (int) $r['manager_id'];
    }
}

foreach ($paths as $k => $v) {
    if (isset($finalArray[$k]) && isset($finalArray[$v])) {
        $finalArray[$k]->setManager($finalArray[$v]);
    }
}