OpenCart 3：如何通过ID获取完整商品分类层级

我在OpenCart 3中尝试通过分类ID（25）获取该分类及其所有子分类的完整层级路径，但修改后的SQL语句返回的路径不完整，仅显示父级名称。

原可用SQL（获取所有分类完整层级） #

SELECT cp.category_id AS category_id, 
    GROUP_CONCAT(cd1.name ORDER BY cp.level SEPARATOR '  >  ') AS name, 
    c1.parent_id, c1.sort_order 
FROM oc_category_path cp 
LEFT JOIN oc_category c1 ON (cp.category_id = c1.category_id) 
LEFT JOIN oc_category c2 ON (cp.path_id = c2.category_id) 
LEFT JOIN oc_category_description cd1 ON (cp.path_id = cd1.category_id) 
LEFT JOIN oc_category_description cd2 ON (cp.category_id = cd2.category_id) 
WHERE cd1.language_id = '2' 
AND cd2.language_id = '2' 
GROUP BY cp.category_id 
ORDER BY name ASC 
LIMIT 0,20

错误查询SQL（仅返回父级名称） #

SELECT cp.category_id AS category_id, 
    GROUP_CONCAT(cd1.name ORDER BY cp.level SEPARATOR '  >  ') AS name, 
    c1.parent_id, c1.sort_order 
FROM oc_category_path cp 
LEFT JOIN oc_category c1 ON (cp.category_id = c1.category_id) 
LEFT JOIN oc_category c2 ON (cp.path_id = c2.category_id) 
LEFT JOIN oc_category_description cd1 ON (cp.path_id = cd1.category_id) 
LEFT JOIN oc_category_description cd2 ON (cp.category_id = cd2.category_id) 
WHERE cd1.language_id = '2' 
AND cd2.language_id = '2'
AND cp.path_id = 25
GROUP BY cp.category_id 
ORDER BY name ASC

期望结果 #

category_id      name                             parent_id
 25              Components                        0       
 29              Components > Mice and Trackballs  25  
 28              Components > Monitors             25  
 35              Components > Monitors > test 1    28  
 36              Components > Monitors > test 2    28  
 30              Components > Printers             25  
 31              Components > Scanners             25  
 32              Components > Web Cameras          25

问题原因 #

WHERE cp.path_id = 25这个条件直接过滤了oc_category_path表中仅path_id为25的行。而oc_category_path表中每个分类的完整路径由多条记录组成（比如子分类35的路径会包含path_id=0、25、28、35的记录），过滤后每个分类只剩path_id=25的单条记录，导致GROUP_CONCAT只能拼接出这一个节点的名称，无法得到完整层级。

解决方案 #

先筛选出所有属于分类25及其子分类的category_id，再基于这些ID获取每个分类的完整路径记录，推荐两种实现方式：

方式一：子查询筛选目标分类ID #

SELECT cp.category_id AS category_id, 
       GROUP_CONCAT(cd1.name ORDER BY cp.level SEPARATOR '  >  ') AS name, 
       c1.parent_id, 
       c1.sort_order 
FROM oc_category_path cp 
LEFT JOIN oc_category c1 ON cp.category_id = c1.category_id 
LEFT JOIN oc_category_description cd1 ON cp.path_id = cd1.category_id 
WHERE cd1.language_id = '2' 
  AND cp.category_id IN (
    -- 筛选所有以25为祖先的分类ID（含25自身）
    SELECT category_id 
    FROM oc_category_path 
    WHERE path_id = 25
  )
GROUP BY cp.category_id 
ORDER BY name ASC;

方式二：JOIN筛选目标分类ID（性能更优） #

SELECT cp.category_id AS category_id, 
       GROUP_CONCAT(cd1.name ORDER BY cp.level SEPARATOR '  >  ') AS name, 
       c1.parent_id, 
       c1.sort_order 
FROM oc_category_path cp 
LEFT JOIN oc_category c1 ON cp.category_id = c1.category_id 
LEFT JOIN oc_category_description cd1 ON cp.path_id = cd1.category_id 
-- 通过JOIN关联筛选目标分类
INNER JOIN oc_category_path cp_filter ON cp.category_id = cp_filter.category_id 
WHERE cd1.language_id = '2' 
  AND cp_filter.path_id = 25
GROUP BY cp.category_id 
ORDER BY name ASC;

逻辑说明 #

• 子查询/cp_filter表的作用是找出所有分类路径中包含25的category_id，即分类25本身及其所有子分类（无论层级深度）。
• 主查询基于这些category_id，完整获取每个分类在oc_category_path中的所有路径记录，再通过GROUP_CONCAT按level排序拼接出完整层级路径。

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