Create multiple categories with parent, child and grandchild

Question

I want to create multiple categories with parent, child and grandchild. And can order by ordering field.

id  | parent_id |   name    |  ordering
--------------------------------------
1   |     0     |   Men     |   1
2   |     0     |   Women   |   2
3   |     1     |   Shoes   |   3
4   |     2     |   Watches |   4
5   |     1     |   Pants   |   5
6   |     3     |   Sport   |   6
7   |     3     |   Casual  |   7
8   |     2     |   Book    |   8

I want this result

Men
  Shoes
     Sport
     Casual
  Pants
Women
  Watches
  Book

Array out put

Array
(
[0] => Array
    (
        [id] => 1
        [parent_id] => 0
        [name] => Men
        [ordering] => 1
        [level] => parent    //NEEDED
    )
[1] => Array
    (
        [id] => 2
        [parent_id] => 0
        [name] => Women
        [ordering] => 2
        [level] => parent    //NEEDED
    )
[2] => Array
    (
        [id] => 3
        [parent_id] => 1
        [name] => Shoes
        [ordering] => 3
        [level] => child    //NEEDED
    )
[3] => Array
    (
        [id] => 4
        [parent_id] => 2
        [name] => Watches
        [ordering] => 4
        [level] => child    //NEEDED
    )
[4] => Array
    (
        [id] => 5
        [parent_id] => 1
        [name] => Pants
        [ordering] => 5
        [level] => child    //NEEDED
    )
[5] => Array
    (
        [id] => 6
        [parent_id] => 3
        [name] => Sport
        [ordering] => 6
        [level] => grandchild    //NEEDED
    )
[6] => Array
    (
        [id] => 7
        [parent_id] => 3
        [name] => Casual
        [ordering] => 7
        [level] => grandchild    //NEEDED
    )
[7] => Array
    (
        [id] => 8
        [parent_id] => 2
        [name] => Book
        [ordering] => 8
        [level] => child    //NEEDED
    )
)

No shoes for women won't end well. – Alex K. Apr 08 '15 at 13:01 — Alex K., Apr 08 '15 at 13:01
What database system are you using? – Alex K. Apr 08 '15 at 13:01 — Alex K., Apr 08 '15 at 13:01

kiks73 · Answer 1 · 2015-04-08T13:34:29.697

This could be a solution to get your level information:

SELECT CASE WHEN parent_id = 0 THEN 'Parent'
       ELSE CASE WHEN (SELECT T2.parent_id 
                        FROM tab T2
                        WHERE T2.id = T.parent_id) = 0 THEN 'Child'
                  ELSE 'Grandchild' 
             END
   END AS level
FROM tab T

Fiddle here

To put the content of this SQL into an array you need a server side language like PHP, since you're not using mongoDB or similar.

score 0 · Answer 2 · answered Apr 08 '15 at 13:40

Assuming your table is called "items", you could do the following recursive query to generate names containing the parent and the ordering attribute, then order by the generated name and add spaces before the name according to the level:

;WITH itemsHierarchy (id, parent_id, name, generatedname, level)
AS
(
    SELECT 
        i.id, 
        i.parent_id, 
        i.name, 
        CAST(i.name AS varchar(MAX)) as generatedname, 
        1 as level
    FROM items i
    WHERE i.parent_id = 0
    UNION ALL
    SELECT 
        i.id, 
        i.parent_id, 
        i.name,
         CAST(ih.generatedname + '_' + CAST(i.ordering as varchar(2))+ '_' + i.name AS varchar(MAX)), 
         ih.level + 1
    FROM items i
    INNER JOIN itemsHierarchy ih ON i.parent_id = ih.id
)

SELECT REPLICATE('   ', level) + name
FROM itemsHierarchy
ORDER BY generatedname

Result:

   Men
      Shoes
         Sport
         Casual
      Pants
   Women
      Watches
      Book

score 0 · Accepted Answer · edited May 23 '17 at 10:24

Finally i found good answer

ref : Parent child mysql

select t0.*,
  concat(
      case coalesce(t4.parent_id, 0) 
        when 0 then ''
        else concat(cast(t4.parent_id as char), '\\')
      end,
      case coalesce(t3.parent_id, 0) 
        when 0 then ''
        else concat(cast(t3.parent_id as char), '\\')
      end,
      case coalesce(t2.parent_id, 0) 
        when 0 then ''
        else concat(cast(t2.parent_id as char), '\\')
      end,
      case coalesce(t1.parent_id, 0) 
        when 0 then ''
        else concat(cast(t1.parent_id as char), '\\')
      end,
      case coalesce(t0.parent_id, 0) 
        when 0 then ''
        else concat(cast(t0.parent_id as char), '\\')
      end,
      cast(t0.id as char)
    ) as path
from mytable t0
    left join mytable t1 on t0.parent_id = t1.Id
    left join mytable t2 on t1.parent_id = t2.Id
    left join mytable t3 on t2.parent_id = t3.Id
    left join mytable t4 on t3.parent_id = t4.Id
order by 
  concat(
      case coalesce(t4.parent_id, 0) 
        when 0 then ''
        else concat(cast(t4.parent_id as char), '\\')
      end,
      case coalesce(t3.parent_id, 0) 
        when 0 then ''
        else concat(cast(t3.parent_id as char), '\\')
      end,
      case coalesce(t2.parent_id, 0) 
        when 0 then ''
        else concat(cast(t2.parent_id as char), '\\')
      end,
      case coalesce(t1.parent_id, 0) 
        when 0 then ''
        else concat(cast(t1.parent_id as char), '\\')
      end,
      case coalesce(t0.parent_id, 0) 
        when 0 then ''
        else concat(cast(t0.parent_id as char), '\\')
      end,
      cast(t0.id as char)
    )

Create multiple categories with parent, child and grandchild

3 Answers3