如何将组织层级关联数组转换为查询用结构化筛选数组？

一、递归实现（简洁直观） #

递归是处理这种嵌套层级结构的天然方案，之前效果不佳大概率是逻辑细节没处理到位。以下是正确的递归实现，核心是逐层收集筛选值，遇到最后一层（team列表）时生成完整条件：

function flattenOrganizations(array $selections, array $current = [], $level = 0): array
{
    $levels = ['group', 'unit', 'department'];
    $result = [];

    foreach ($selections as $key => $value) {
        // 前三层用键作为筛选值，递归遍历下一层
        if ($level < count($levels)) {
            $newCurrent = $current;
            $newCurrent[$levels[$level]] = $key;
            $result = array_merge($result, flattenOrganizations($value, $newCurrent, $level + 1));
        } else {
            // 第四层是team列表，每个team生成一条完整条件
            foreach ($value as $team) {
                $result[] = array_merge($current, ['team' => $team]);
            }
        }
    }

    // 处理空层级（比如department下没有team的情况）
    if ($level === count($levels) && empty($selections)) {
        $result[] = $current;
    }

    return $result;
}

// 使用示例
$flattened = flattenOrganizations($selections);

二、非递归迭代实现（避免递归深度问题） #

如果担心层级过深导致递归栈溢出，可以用栈模拟递归过程，实现迭代式扁平化：

function flattenOrganizationsIterative(array $selections): array
{
    $levels = ['group', 'unit', 'department'];
    $result = [];
    // 栈中存储当前遍历状态：[当前数组, 已收集的条件, 当前层级]
    $stack = [[$selections, [], 0]];

    while (!empty($stack)) {
        [$currentArr, $currentCond, $level] = array_pop($stack);

        foreach ($currentArr as $key => $value) {
            if ($level < count($levels)) {
                $newCond = $currentCond;
                $newCond[$levels[$level]] = $key;
                // 将下一层级压入栈
                $stack[] = [$value, $newCond, $level + 1];
            } else {
                // 遍历team列表生成条件
                foreach ($value as $team) {
                    $result[] = array_merge($currentCond, ['team' => $team]);
                }
            }
        }

        // 处理空的末级节点（比如无team的department）
        if ($level === count($levels) && empty($currentArr)) {
            $result[] = $currentCond;
        }
    }

    return $result;
}

// 使用示例
$flattened = flattenOrganizationsIterative($selections);

三、Laravel 查询优化 #

原示例中的查询逻辑存在隐患：若条件数组没有team键，直接调用$array->team会报错。调整后的严谨写法如下：

$fullQuery = DB::table('mytable');

foreach ($flattened as $cond) {
    $fullQuery->orWhere(function($query) use ($cond) {
        $query->where('group', $cond['group'])
              ->where('unit', $cond['unit'])
              ->where('department', $cond['department']);
        // 仅当存在team条件时添加该筛选
        if (isset($cond['team'])) {
            $query->where('team', $cond['team']);
        }
    });
}

内容的提问来源于stack exchange，提问作者pileup