CODEWITHSUNDEEP
mysql
Albert
Albert

Reputation: 41

Mysql - How to get all descendants(even grandchildren and so on..) with just the ancestor id?

I do want to get all the descendants(including grandchildren) id from my tree where I'll just input the parent id. I currently use closure table as my approach. I have this table for storing the parent and child id:

CREATE TABLE `treepaths` (
`ancestor` int(11) NOT NULL,
`descendant` int(11) NOT NULL,
 PRIMARY KEY (`ancestor`,`descendant`),
KEY `FK_Descendant_idx` (`descendant`),
CONSTRAINT `FK_Ancestor` FOREIGN KEY (`ancestor`) REFERENCES `organization` 
(`organization_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_Descendant` FOREIGN KEY (`descendant`) REFERENCES 
`organization` (`organization_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Here is my where I insert my data:

CREATE DEFINER=`root`@`localhost` PROCEDURE `CreateChild`(
Ancestor int,
Descendant int,

ParentID int,
ChildID int
)
BEGIN
INSERT INTO treepaths
VALUES (Ancestor, Descendant);
SELECT tree.ancestor, ChildID FROM treepaths tree
WHERE tree.descendant = ParentID
UNION ALL SELECT ChildID, ChildID; 
END

And this where I read the data:

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetOrganizationDescendant`(
Ancestor int
)
BEGIN
SELECT org.* FROM organization org 
JOIN treepaths tree ON org.organization_id = tree.descendant
WHERE tree.ancestor = Ancestor;
END

Currently, it only retrieve the direct child of the parent, not it's grandchildren. Is there any way to do this?

UPDATE I revise my table with adjacency list model using this as reference. Now, I wanted to include the parent in the table. How do I do this? This is the sample fiddle of the reference above

Upvotes: 0

Views: 395

Answers (2)

Albert
Albert

Reputation: 41

Nevermind, I just manipulated it on my source code.

Upvotes: 0

Manse
Manse

Reputation: 38147

I would suggest you switch to a nested set model for hierarchies / trees - makes life a lot easier for the kinds of queries your trying to run and no doubt will want to run in future

Upvotes: 1

Related Questions