How to get category hierarchy by id | Opencart 3

Question

Im trying to get full category hierarchy by id but its look impossible for some reason

this code is getting all category hierarchy

Example Database

    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

Results:

    category_id   name      parent_id
    33            Cameras     0         
    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 

Now i like to get results for category_id = 25 only and i rework the code like this one

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

Resutls:

category_id     name          parent_id
25            Components      0     
28            Components      25    
29            Components      25    
30            Components      25    
31            Components      25    
32            Components      25    
35            Components      28

I want to get results like this:

     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

Answer 1

You need to add one more join to get sub-category hierarchies. When you add where condition to "oc_category_path" to get only path_id eq. 25, query returns only 0 levels.

Here check the below query, it produces result as you want:

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

You can try on Example db.

Note: I think, no more need to extra left joins, so I removed ("c2, cd2") them.