Closure table, tree rendering with GUID instead of auto-increment

Hi,

I’ve implemented a closure table system, for a group list (table: company_groups with columns id and name). The references are then stored in a closure table, company_groups_treepaths:

CREATE TABLE `company_groups` (
  `id` char(36) NOT NULL default '',
  `name` varchar(150) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `company_groups_treepaths` (
  `ParentID` char(36) NOT NULL default '',
  `ChildID` char(36) NOT NULL default '',
  `PathLength` int(11) NOT NULL default '0',
  PRIMARY KEY  (`ParentID`,`ChildID`),
  KEY `PathLength` (`PathLength`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And then I am trying to get a tree structure out of it. The problem is that most of the solutions I find is using group_concat on the group id, assuming it’s an INT and auto_increment.

However, I use GUID which makes it harder. I’ve looked through the other examples here and on stack overflow, but can’t really get a hang of it.

For example, this query retrieved the right groups, but the wrong tree:

SELECT SQL_CALC_FOUND_ROWS
                p.`ChildID`, p.ParentID, d.name, CONCAT(REPEAT('-', p.`PathLength`), d.`name`) as path, p.`PathLength` as depth
            FROM 
                `company_groups` AS d
            JOIN `company_groups_treepaths` AS p ON d.`id` = p.`ChildID`
            JOIN `company_groups_treepaths` AS crumbs ON crumbs.`ChildID` = p.`ChildID`

            WHERE 
                p.`ParentID` = 'aa420c70-7050-11e2-b75d-672efc30777e'
              GROUP BY d.id
    ORDER BY GROUP_CONCAT(crumbs.`PathLength`)

SQL Fiddle here: 2/474d4/2

The correct order for that query should be (fetching all children of Swedbank):

Swedbank (aa420c70-7050-11e2-b75d-672efc30777e)
 - hejsan (44b2b680-7f44-11e2-b04d-918fe8c8d065)
 - Östergötland (aa420970-7050-11e2-893a-7f63b55a76db)
 - - Regional1 (a6adc800-7050-11e2-9db0-ad8ff41db08c)
 - - - asd (56fd15a0-7f44-11e2-b10f-55240ef76c28)
 - - - hejsan3 (fc14c320-7f44-11e2-a2bb-ed51f02fd80f)
 - - Under öster (bb6b93a0-80ea-11e2-be1d-fd97d33aad97)
 - Småland (ae5dc150-7050-11e2-9b11-c96b3591816c)
 - - asdasd (534e3f00-80df-11e2-b92e-fd29e414f3fd)
 - - asd (6e640160-80de-11e2-8c41-d135d36c28db)
 - hejsan2 (d95a7060-80be-11e2-8179-0b9231964800)

Anyone got any good ideas?

And the correct link is: here href=”http://sqlfiddle.com/#http://sqlfiddle.com/#" alt="" />2/474d4/1

bit.ly/YFKgjr

I reverted to the basics, http://karwin.blogspot.se/2010/03/rendering-tre…

I ended up with

select group_concat(n.name order by a.PathLength desc separator ' -> ') as fullpath, CONCAT(REPEAT('-', d.`PathLength`), cg.`name`) as path, d.ChildID as group_id, d.PathLength as depth, cg.name
            from company_groups_treepaths d
            join company_groups_treepaths a on (a.ChildID = d.ChildID)
            join company_groups n on (n.id = a.ParentId)
            join company_groups cg on (cg.id = d.ChildID)

            where d.ParentID = 'aa420c70-7050-11e2-b75d-672efc30777e' and cg.deleted = 0 
            group by d.ChildID
            order by fullpath