-1

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
bobi
  • 169
  • 2
  • 16
  • What version of MySQL? – Rick James Apr 05 '23 at 19:12
  • Are you expecting the `name` in `ORDER BY name` to refer to `cd1.name` or the Group_concat? – Rick James Apr 05 '23 at 19:16
  • Please provide some sample data. I have not clue of the relationship between `cp.path_id` and "Cameras". – Rick James Apr 05 '23 at 19:18
  • @RickJames data was provided in topic: https://www.db-fiddle.com/f/nHh9FX6P16p9BGjvy97wUD/0 – bobi Apr 06 '23 at 06:25
  • 1
    Your commands doesn't appear to match the posted output and won't work due to missing "group by's". Beside that: add cp.path_id to your output (and the group by clause) and you will see, that by forcing path_id to 25 you nailed down the query to just deliver one level. to solve you could add a group_concat for the path_id and check it with having clause to contain the desired id of 25 ... but ths will definitely not be performant in larger scenarios. – Synopsis Apr 06 '23 at 07:38
  • The query can be sped up via getting rid of c2 and cd2 -- they are included in the query but have no impact on the results. – Rick James Apr 06 '23 at 21:17
  • Meanwhile, I checked the indexes (and PKs); they seem optimal. – Rick James Apr 06 '23 at 21:17
  • What condition should be used to prevent the inclusion of "Cameras"? – Rick James Apr 06 '23 at 21:19
  • Have you tried recursive cte (common table expression) ? – prograshid Apr 12 '23 at 14:40

1 Answers1

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.

jepozdemir
  • 260
  • 1
  • 4