Situation:
I have a mysql table of directories. Each directory has a parent directory (stored as parentID), up to the point where the root directory has a parentID of 0.
E.g.:
rowID: 1, name: Dir1, parentID: 0 (root directory)
rowID: 2, name: Dir2, parentID: 0 (root directory)
rowID: 3, name: Subdir1, parentID: 1 (lives in "Dir1")
rowID: 4, name: Subdir2, parentID: 1 (lives in "Dir1")
rowID: 5, name: Subdir3, parentID: 3 (lives in "Subdir1", which in turn lives in "Dir1")
rowID: 6, name: Subdir4, parentID: 5 (lives in "Subdir3", which lives in "Subdir1", which lives in "Dir1")
So here there is a 3 directory depth structure.
I need to build a statement which joins any directory to its parent and continues to do so until the last directory joined has a parentID of 0 (i.e. found the root directory). You can think of it as if, given any directory, you can find the breadcrumb back to the parent.
I figure that this may require some MySQL looping but for the life of me, I can't get any of the web examples to work. I can't even get some of the examples to run as they seem to have some sort of syntax errors in them. Can anyone help me get started?
I can accept any result format that's easiest and gives best performance to get this done. Either a simple array of row numbers in correct order (e.g. 5, 3, 1, 0, indicating the steps to get to ID of 0), or a full table (best) which will be an ordered list of rows that achieve this, e.g.
rowID: 5, name: Subdir3, parentID: 2;
rowID: 3, name: Subdir1, parentID: 1;
rowId: 1, name: Dir1, parentID: 0;
Help much appreciated!
解决方案
Alright, found the time to actually deploy a simple database with a similar structure as described.
The table is the following:
CREATE TABLE `t_hierarchy` (
`rowID` INT(11) NULL DEFAULT NULL,
`name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`parentID` INT(11) NULL DEFAULT NULL
);
I basicly inserted the exact same stuff as you have given above but used NULL values instead of 0 for root/no parent
What I've done is the quite cryptic example from http://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/ .
and just corrected the column names to fit mine.
Since this only generates you a recursive hierarchy, I just added a stupid join into the example ( ad.rowID = qi.id ):
SELECT qi.id, qi.parent, ad.rowId, ad.name, level
FROM (
SELECT @r AS id,
(
SELECT @r := parentID
FROM t_hierarchy
WHERE rowID = id
) AS parent,
@l := @l + 1 AS level
FROM (
SELECT @r := 5, -- change this 5 to the directory ID you want to resolve
@l := 0,
@cl := 0
) vars,
t_hierarchy h
WHERE @r <> 0
ORDER BY
level DESC
) qi, t_hierarchy ad
WHERE ad.rowID = qi.id
And this generates the follwoing (desired) output:
id parent rowId name level
1 NULL 1 Dir1 3
3 1 3 Subdir1 2
5 3 5 Subdir3 1
Level is a helper column which tells you how "deep" it had to resolve to reach this.
All you will have to do is change the "5" next to @r := to the directory ID from where you wanna iterate down.
If you want to switch the direction (from up to down) simply sort by level column ([...] WHERE ad.rowID = qi.id ORDER BY level ASC )
Hope this helps you out.
Edit: qi.id and ad.rowID are duplicates, just remove one of them ;-)... damn I hate that hierarchy stuff