How to fetch categories and sub-categories in a single query in sql? (mysql)

Question

Hi,

I would like to know if it's possible to extract the categories and sub-categories in a single DB fetch.

My DB table is something similar to that shown below

table

cat_id parent_id
1      0
2      1
3      2
4      3
5      3
6      1

i.e. when the input is 3, then all the rows with parent_id as 3 AND the row 3 itself AND all the parents of row 3 should be fetched.

output

cat_id parent_id
3      2   -> The row 3 itself
4      3   -> Row with parent as 3
5      3   -> Row with parent as 3
2      1   -> 2 is the parent of row 3
1      0   -> 1 is the parent of row 2

Can this be done using stored procedures and loops? If so, will it be a single DB fetch or multiple? Or are there any other better methods?

Thanks!!!

Answer 1

If you asking about "Is there in mysql recursive queries?" answer "NO".

But there is very good approach to handle it.

Create helper table (saying CatHierarchy)

CatHierarchy:
    SuperId, ChildId, Distance
------------------------------ 
     1          1         0
     1          2         1
     2          2         0

This redundant data allows easily in 1 query to select any hierarchy, and in 2 insert support any hierarchy (deletion also performed in 1 query with help of delete cascade integrity).

So what does this mean. You track all path in hierarchy. Each node of Cat must add reference to itself (distance 0), then support duplication by adding redundant data about nodes are linked.

To select category with sub just write:

 SELECT c.* from Category c inner join CatHierarchy ch ON ch.ChildId=c.cat_id
      WHERE ch.SuperId = :someSpecifiedRootOfCat

someSpecifiedRootOfCat - is parameter to specify root of category THATS ALL!

Answer 2

Theres a really good article about this on Sitepoint - look especially at Modified Preorder Tree Traversal