PHP 遞歸獲取無限級分類的所有子孫分類 ID：資料庫單查詢優化與陣列合併實戰

需求

給定一個分類 ID，獲取其所有後代分類 ID（子、孫、曾孫...），返回逗號分隔字串供 SQL IN (...) 使用。

原始遞歸查詢法（N+1 問題）

<?php
// 原文代碼：每層執行一次 SELECT，深度 N 執行 N 次查詢
private function sonCategoryIds(int $categoryID): string {
    $array[] = $categoryID;
    do {
        $ids = '';
        $temp = $this->mysql->select(
            "SELECT `id` FROM `pcb_article_category` WHERE `parentID` IN ($categoryID)"
        );
        foreach ($temp as $v) {
            $array[] = $v['id'];
            $ids .= ',' . $v['id'];
        }
        $ids = substr($ids, 1);
        $categoryID = $ids;
    } while (!empty($temp));

    return implode(',', $array);
}

缺點： 分類層級越深、分支越多，查詢次數線性增長，資料庫壓力大。

優化方案一：單次查詢 + PHP 遞歸構建樹（推薦中小數據量）

<?php
/**
 * 獲取所有後代分類 ID（單查詢版）
 *
 * @param PDO $pdo
 * @param int $rootId
 * @return string 逗號分隔 ID 字串
 */
function getAllDescendantIds(PDO $pdo, int $rootId): string {
    // 1. 單次查詢所有分類（id, parentID）
    $stmt = $pdo->query('SELECT `id`, `parentID` FROM `pcb_article_category`');
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // 2. 建立 parentID => [childIds] 映射
    $childrenMap = [];
    foreach ($rows as $row) {
        $childrenMap[$row['parentID']][] = $row['id'];
    }

    // 3. 遞歸收集後代
    $descendants = [];
    $collect = function (int $pid) use (&$collect, &$childrenMap, &$descendants) {
        if (empty($childrenMap[$pid])) return;
        foreach ($childrenMap[$pid] as $cid) {
            $descendants[] = $cid;
            $collect($cid);
        }
    };
    $collect($rootId);

    // 4. 含根節點自身
    array_unshift($descendants, $rootId);
    return implode(',', $descendants);
}

• 單次 SELECT，資料庫往返 1 次
• PHP 記憶體構建映射，十萬級分類毫秒級完成
• 適合分類表 < 50 萬行、不頻繁變更的場景

優化方案二：資料庫層面遞歸 CTE（MySQL 8.0+ / PostgreSQL / SQL Server）

-- MySQL 8.0+ WITH RECURSIVE CTE
WITH RECURSIVE `descendants` AS (
    SELECT `id`, `parentID` FROM `pcb_article_category` WHERE `id` = :rootId
    UNION ALL
    SELECT c.`id`, c.`parentID`
    FROM `pcb_article_category` c
    INNER JOIN `descendants` d ON c.`parentID` = d.`id`
)
SELECT GROUP_CONCAT(`id` SEPARATOR ',') AS `ids` FROM `descendants`;

<?php
function getDescendantsCte(PDO $pdo, int $rootId): string {
    $sql = <<prepare($sql);
    $stmt->execute([':rootId' => $rootId]);
    return $stmt->fetchColumn() ?: (string)$rootId;
}

• 完全在資料庫層面完成，無網路傳輸全表數據
• 利用索引（parentID 應建索引）極快
• 需 MySQL 8.0+ 或相容資料庫

優化方案三：Closure Table（閉包表） —— 百萬級分類的終極解

額外維護一張表 category_closure：

CREATE TABLE `category_closure` (
    `ancestor` INT NOT NULL,
    `descendant` INT NOT NULL,
    `depth` INT NOT NULL,
    PRIMARY KEY (`ancestor`, `descendant`),
    KEY `idx_descendant` (`descendant`)
);
-- 範例資料：1 是 2、3 的祖先；2 是 4 的祖先
INSERT INTO `category_closure` VALUES
(1,1,0), (1,2,1), (1,3,1), (1,4,2),
(2,2,0), (2,4,1),
(3,3,0),
(4,4,0);

<?php
// 獲取所有後代（含自身）：depth > 0 或 depth >= 0
$stmt = $pdo->prepare('SELECT GROUP_CONCAT(`descendant` SEPARATOR ",") FROM `category_closure` WHERE `ancestor` = :rootId');
$stmt->execute([':rootId' => $rootId]);
$ids = $stmt->fetchColumn(); // 直接拿到 "1,2,3,4"

// 獲取直接子分類：depth = 1
$stmt = $pdo->prepare('SELECT GROUP_CONCAT(`descendant` SEPARATOR ",") FROM `category_closure` WHERE `ancestor` = :rootId AND `depth` = 1');

• 查詢 O(1)，無遞歸、無 CTE 限制
• 寫入時需維護閉包表（觸發器或應用層），讀寫分離架構極適合
• 支援「獲取祖先路徑」、「計算層級」、「移動子樹」等高階操作

方案選型指南

總結

原始代碼的 N+1 查詢是典型效能陷阱。根據業務規模選擇：單查詢映射法最通用、CTE最現代、Closure Table最極致。記得為 parentID 建立索引，並將獲取邏輯封裝為 Repository 方法，上層業務碼保持乾淨。