分类与子分类表查询结果数组结构调整技术咨询

Hey there! Let's work through turning your flat category array into the nested tree structure you need. First, let's ground this with clear examples so we're on the same page.

Example Raw Data #

Let’s say your SELECT * query plus while loop gives you a flat array like this:

$rawCategories = [
    ['id' => 1, 'name' => 'Electronics', 'parent_id' => null],
    ['id' => 2, 'name' => 'Smartphones', 'parent_id' => 1],
    ['id' => 3, 'name' => 'Laptops', 'parent_id' => 1],
    ['id' => 4, 'name' => 'Apparel', 'parent_id' => null],
    ['id' => 5, 'name' => 'T-Shirts', 'parent_id' => 4],
    ['id' => 6, 'name' => 'Jeans', 'parent_id' => 4],
];

Target Tree Structure #

Your goal is to restructure this so each parent category has a children array holding its subcategories:

$treeCategories = [
    [
        'id' => 1,
        'name' => 'Electronics',
        'parent_id' => null,
        'children' => [
            ['id' => 2, 'name' => 'Smartphones', 'parent_id' => 1],
            ['id' => 3, 'name' => 'Laptops', 'parent_id' => 1]
        ]
    ],
    [
        'id' => 4,
        'name' => 'Apparel',
        'parent_id' => null,
        'children' => [
            ['id' => 5, 'name' => 'T-Shirts', 'parent_id' => 4],
            ['id' => 6, 'name' => 'Jeans', 'parent_id' => 4]
        ]
    ]
];

PHP Solution (Efficient O(n) Logic) #

Since you mentioned handling SQL results with a while loop, I’ll focus on PHP first. This approach avoids nested loops, so it’s fast even with large datasets:

// Initialize helper arrays to split parents and group children
$parentCategories = [];
$childMap = [];

// First pass: separate parents and group children by their parent ID
foreach ($rawCategories as $category) {
    if ($category['parent_id'] === null) {
        // Add parent category with an empty children array
        $category['children'] = [];
        $parentCategories[$category['id']] = $category;
    } else {
        // Group children by their parent's ID for quick lookup later
        $childMap[$category['parent_id']][] = $category;
    }
}

// Second pass: attach children to their corresponding parent
foreach ($parentCategories as $id => &$parent) {
    if (isset($childMap[$id])) {
        $parent['children'] = $childMap[$id];
    }
}

// Convert to an indexed array (optional, if you don't need parent IDs as keys)
$treeCategories = array_values($parentCategories);

How This Works: #

1. First Loop: We split parent categories (where parent_id is null) into their own array, and group all child categories by their parent’s ID. This lets us quickly grab all children for a parent without searching the entire dataset.
2. Second Loop: We iterate over the parent categories and attach their grouped children to the children field.
3. Final Step: array_values() converts the associative array (keyed by parent ID) into a clean indexed array that matches your target structure.

JavaScript Solution (Bonus!) #

If you need to handle this on the frontend, here’s the equivalent logic:

const rawCategories = [
    {id: 1, name: 'Electronics', parent_id: null},
    {id: 2, name: 'Smartphones', parent_id: 1},
    {id: 3, name: 'Laptops', parent_id: 1},
    {id: 4, name: 'Apparel', parent_id: null},
    {id: 5, name: 'T-Shirts', parent_id: 4},
    {id: 6, name: 'Jeans', parent_id: 4},
];

const parentCategories = {};
const childMap = {};

rawCategories.forEach(category => {
    if (category.parent_id === null) {
        category.children = [];
        parentCategories[category.id] = category;
    } else {
        if (!childMap[category.parent_id]) childMap[category.parent_id] = [];
        childMap[category.parent_id].push(category);
    }
});

Object.values(parentCategories).forEach(parent => {
    if (childMap[parent.id]) parent.children = childMap[parent.id];
});

const treeCategories = Object.values(parentCategories);

Edge Cases to Keep In Mind: #

• If a parent has no children, its children array stays empty (clean and expected).
• If you have orphaned child categories (where their parent_id doesn’t match any parent), this logic will ignore them. If you want to handle these, add a step to collect and store them separately.

内容的提问来源于stack exchange，提问作者John.Doe