Parent child mysql

Question

Say I have a table like this:

=================================
| ID |  Parent_ID | Page_Name   |
=================================
| 1  |  NULL      |  Home       |
| 2  |  NULL      |  Services   |
| 3  |  2         |  Baking     |
| 4  |  3         |  Cakes      |
| 5  |  3         |  Bread      |
| 6  |  5         |  Flat Bread |
---------------------------------

How can I go about actually ordering the results in this format? I.e. Ordered by the Parent -> Child -> Sub Child, on the basis I would only ever require say a maximum of 5 levels? I have looked into the "Nested Set Model" but it seems too complex for my requirements. What I am unsure about is really understanding a SQL query I can use to display my results like above, or in this situation should I be using a server side language like PHP to do this for me?

Not sure if this is what you meant, but I think this will help: http://stackoverflow.com/questions/6240028/select-parent-and-children-with-mysql — We0, Apr 23 '13 at 14:21
Are you talking about recursively listing a tree by id order? MySQL didn't support that, last I checked. PostgreSQL, Oracle, and SQL Server did through recursive CTE's. The only way I know how to do that with mysql is to make multiple calls to the database through your client, referencing the last result set. — Jeremy Holovacs, Apr 23 '13 at 14:22
Can you show what your actual result should looks like? I don't understand the order you need. — Cyril Gandon, Apr 23 '13 at 14:26
select id,parent_id, page_name from table_name order by pid, id would work for the sample data and required output, but you may need to self join to support ordering parent_of_parent etc — Ian Kenney, Apr 23 '13 at 14:26
Then what Jeremy says is correct. Best idea is then to call while parent_id NOT NULL — We0, Apr 23 '13 at 14:28
http://stackoverflow.com/questions/15584013/how-to-find-the-hierarchy-path-for-a-tree-representation/15861254#15861254 Check this link — Meherzad, Apr 23 '13 at 14:43

shibormot · Answer 1 · 2013-04-23T15:49:35.507

EDIT

working sample addressing Gordons note

Query calculate node path as you have fixed maximum tree depth, and order by it.

SQL Fiddle

MySQL 5.5.30 Schema Setup:

create table mytable(id int, parent_id int, name varchar(100));

insert mytable(id, parent_id, name)
values (1, null, 'Home'),
(2, null, 'Services'),
(3, 2, 'Baking'),
(4, 3, 'Cakes'),
(5, 3, 'Bread'),
(6, 5, 'Flat Bread'),
(7, 1, 'Something');

Query 1:

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)
    )

Results:

| ID | PARENT_ID |       NAME |    PATH |
-----------------------------------------
|  1 |    (null) |       Home |       1 |
|  7 |         1 |  Something |     1\7 |
|  2 |    (null) |   Services |       2 |
|  3 |         2 |     Baking |     2\3 |
|  4 |         3 |      Cakes |   2\3\4 |
|  5 |         3 |      Bread |   2\3\5 |
|  6 |         5 | Flat Bread | 2\3\5\6 |

Your order by clause does not work if you add "(7, 1)" into the data set. — Gordon Linoff, Apr 23 '13 at 14:39
@GordonLinoff, yes, fixed with some improovement of you answer) — shibormot, Apr 23 '13 at 15:35
Thank you for the answer. It is beyond the scope of what I understand in mysql. Could you explain briefly how this works I would really like to understand it rather than just copy and paste. — Michael Ramirez, Apr 23 '13 at 16:16
@MichaelRamirez, query just form string path from root to child nodes, which consists of `ids` of nodes. Look at [this](http://sqlfiddle.com/#!2/23c62/40/0) example. The `path` column just form string from **T0_ID**, **T0_Parent**, **T1_Parent**, etc columns, by eliminating nulls (`coalesce`), converting to char (`cast`) and adding slashes between `ids` while concatenating they by `concat` function. All functions are documented and have examples. — shibormot, Apr 23 '13 at 21:10

Gordon Linoff · Accepted Answer · 2013-04-23T17:13:14.457

You can try this:

select t.*,
       (case when t4.parent_id is not NULL then 5
             when t4.id is not null then 4
             when t3.id is not null then 3
             when t2.id is not null then 2
             when t1.id is not null then 1
             else 0
        end) as level
from t left outer join
     t t1
     on t.parent_id = t1.id left outer join
     t t2
     on t1.parent_id = t2.id left outer join
     t t3
     on t2.parent_id = t3.id left outer join
     t t4
     on t3.parent_id = t4.id
order by coalesce(t4.parent_id, t4.id, t3.id, t2.id, t1.id, t.id),
         coalesce(t4.id, t3.id, t2.id, t1.id, t.id),
         coalesce(t3.id, t2.id, t1.id, t.id),
         coalesce(t1.id, t.id),
         t.id

Recursive queries are not needed if the hierarchy is finite.

The order by clause is the tricky part. It just orders by the levels of the hierarchy, starting at the topmost level.

The original version of this worked on the data in the question. More extensive testing found that it did not always work. I believe this version always works.

How can I integrate the level within this? – Michael Ramirez Apr 23 '13 at 16:31 — Michael Ramirez, Apr 23 '13 at 16:31

Parent child mysql

2 Answers2

Linked