Hierarchical data - How to write mysql query to order list grouped by parent-children using mysql loop

Questions

I have a User table which has the following columns:

• id
• name
• parent_id
• level

https://github.com/stefankroes/ancestry https://github.com/stefankroes/ancestry

  [UPDATE]   

I want to list the users in mysql ordered by temporary column ancestry which should have value for each user hierarchy based on its parent_id .

Answers

Have a look at the following article... I think it is exactly what your looking for:

http://explainextended.com/2009/03/19/hierarchical-queries-in-mysql-adding-ancestry-chains/ http://explainextended.com/2009/03/19/hierarchical-queries-in-mysql-adding-ancestry-chains/

This describes how to make/use a heirarchical query to get the ancestry of a node within a tree structure.

Update

Since you have a limited amount of levels a query like the one below would do the job. The below query is for 7 levels, though it needs optimization as it is a working rough/draft.

select
SUBSTRING_INDEX(SUBSTRING(ancestry, 1, CHAR_LENGTH(ancestry) - 1),  , , -1) as ID,
SUBSTRING(ancestry, 1, CHAR_LENGTH(ancestry) - 1) as Ancestry
from
(
  select 
  concat(
  IF(NOT u1.id IS NULL,concat(u1.id, , ),  ),
  IF(NOT u2.id IS NULL,concat(u2.id, , ),  ),
  IF(NOT u3.id IS NULL,concat(u3.id, , ),  ),
  IF(NOT u4.id IS NULL,concat(u4.id, , ),  ),
  IF(NOT u5.id IS NULL,concat(u5.id, , ),  ),
  IF(NOT u6.id IS NULL,concat(u6.id, , ),  ),
  IF(NOT u7.id IS NULL,concat(u7.id, , ),  ),
  IF(NOT u8.id IS NULL,concat(u8.id, , ),  )
  ) as ancestry
  from User u1 
  left join User u2 on u1.id = u2.parent_id
  left join User u3 on u2.id = u3.parent_id
  left join User u4 on u3.id = u4.parent_id
  left join User u5 on u4.id = u5.parent_id
  left join User u6 on u5.id = u6.parent_id
  left join User u7 on u6.id = u7.parent_id
  left join User u8 on u7.id = u8.parent_id
)
as ancestryTable;

http://sqlfiddle.com/#!2/93c1f/46 http://sqlfiddle.com/#!2/93c1f/46

Update - optimized version and corrected

select ID, ancestry
from
(
  select 
  u8.ID as ID,  
    concat(
    IF(u1.id IS NULL,  , IF(u2.id IS NULL,u1.id,concat(u1.id, , ))),
    IF(u2.id IS NULL,  , IF(u3.id IS NULL,u2.id,concat(u2.id, , ))),
    IF(u3.id IS NULL,  , IF(u4.id IS NULL,u3.id,concat(u3.id, , ))),
    IF(u4.id IS NULL,  , IF(u5.id IS NULL,u4.id,concat(u4.id, , ))),
    IF(u5.id IS NULL,  , IF(u6.id IS NULL,u5.id,concat(u5.id, , ))),
    IF(u6.id IS NULL,  , IF(u7.id IS NULL,u6.id,concat(u6.id, , ))),
    IF(u7.id IS NULL,  , IF(u8.id IS NULL,u7.id,concat(u7.id, , ))),
    IF(u8.id IS NULL,  ,u8.id)
    )as ancestry, 
     u1.id as a1,  u2.id as a2,  u3.id as a3,  u4.id as a4,
     u5.id as a5,  u6.id as a6,  u7.id as a7,  u8.id as a8
    from User u1 
    right join User u2 on  u2.parent_id = u1.id
    right join User u3 on  u3.parent_id = u2.id
    right join User u4 on  u4.parent_id = u3.id
    right join User u5 on  u5.parent_id = u4.id
    right join User u6 on  u6.parent_id = u5.id
    right join User u7 on  u7.parent_id = u6.id
    right join User u8 on  u8.parent_id = u7.id       
) as Ancestry
order by ancestry.ancestry

http://sqlfiddle.com/#!2/93c1f/105 http://sqlfiddle.com/#!2/93c1f/105

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/19114237/how-to-write-mysql-query-to-order-list-grouped-by-parent-children-using-mysql-lo

Related