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
