API返回类似的类别：
[1] => Array
(
[category_id] => 14308
[parent_id] => 14308
[level] => 0
)
[2] => Array
(
[category_id] => 179836
[parent_id] => 14308
[level] => 1
)
[3] => Array
(
[category_id] => 230022
[parent_id] => 179836
[level] => 2
)
我需要将它们插入到DB中,如下所示：
╔═════════════╦═══════════╗ ║ category_id ║ parent_id ║ ╠═════════════╬═══════════╣ ║ 14308 ║ 0 ║ ║ 179836 ║ 14308 ║ ║ 230022 ║ 179836 ║ ╚═════════════╩═══════════╝
…这很容易,但是category_to_path表很棘手,我似乎无法弄明白.它必须是这样的：
╔═════════════╦═════════╦═══════╗ ║ category_id ║ path_id ║ level ║ ╠═════════════╬═════════╬═══════╣ ║ 14308 ║ 14308 ║ 0 ║ ║ 179836 ║ 14308 ║ 0 ║ ║ 179836 ║ 179836 ║ 1 ║ ║ 230022 ║ 14308 ║ 0 ║ ║ 230022 ║ 179836 ║ 1 ║ ║ 230022 ║ 230022 ║ 2 ║ ╚═════════════╩═════════╩═══════╝
这是我到目前为止：
$path_ids;
for ($i=0; $i <= ($category->CategoryLevel-1); $i++) {
// -1 cause the API returns a non zero-based level
$path_ids[$i]['category_id'] = $category->CategoryID;
if ($category->CategoryLevel-1 == $i) {
$path_ids[$i]['path_id'] = $category->CategoryParentID[0];
}
elseif ($category->CategoryLevel-1) {
// ?
}
$path_ids[$i]['level'] = $i;
}
以下查询应该为您提供类别和路径.它不提供级别,因为您的原始数据缺少任何类型的行号.如果要获取级别,可能需要使用一些动态sql.
我使用的策略是交叉连接类别,这会生成所有可能的起点和终点.它还会生成表不支持的一些路径.要删除这些不需要的路径,我会针对子查询检查每个路径,以确保可以通过3个步骤或更少的步骤遍历从类别到路径来创建它.
SELECT t1.category_id AS category_id,t2.category_id AS path_id
FROM yourTable t1
CROSS JOIN yourTable t2
INNER JOIN
(
SELECT t1.category_id AS category_id_1,t2.category_id AS category_id_2,t3.category_id AS category_id_3
FROM yourTable t1
LEFT JOIN yourTable t2
ON t1.parent_id = t2.category_id
LEFT JOIN yourTable t3
ON t2.parent_id = t3.category_id
) t2
ON t1.category_id = t2.category_id_1 AND
t2.category_id IN (t2.category_id_1,t2.category_id_2,t2.category_id_3)
ORDER BY category_id,path_id
