How to Implement Recursive Folder Retrieval in MySQL?

Recursive Retrieval of Child Folders in MySQL

When working with hierarchical data structures, it becomes necessary to retrieve child elements and their sub-children from the database. In the case of a table representing folders, a query is required that can retrieve all child folders for a given parent folder.

Consider the scenario where we have a Folder table with the following structure:

CREATE TABLE IF NOT EXISTS `Folder` (
    `idFolder` INT(11) NOT NULL AUTO_INCREMENT,
    `FolderName` VARCHAR(150) NOT NULL,
    `idFolderParent` INT(11) NULL,
    PRIMARY KEY (`idFolder`),
    CONSTRAINT `fk_1`
    FOREIGN KEY (`idFolderParent`)
    REFERENCES `Folder` (`idFolder`)
);

We have a sample dataset for this table:

idFolder , FolderName , idFolderParent
   1           ADoc           Null  
   2           ADoc1           1  
   3           ADoc2           2
   4           ADoc3           3
   5           ADoc4           4
   6           ADoc5           5
   7           ADoc6           4

The Challenge

Given an idFolder, the objective is to compose a single query that retrieves all its child folders, along with any sub-child folders recursively. For instance:

• When idFolder is 1, the result should be: 2, 3, 4, 5, 6, 7
• For idFolder 4, the expected result is: 5, 6, 7
• Similarly, for idFolder 3, the output should be: 4, 5, 6, 7

The Solution

The provided solution leverages a combination of MySQL user-defined variables and a subquery to achieve recursive retrieval:

SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
  SELECT @pv:=(SELECT GROUP_CONCAT(idFolder SEPARATOR ',') FROM Folder 
WHERE FIND_IN_SET(idFolderParent, @pv)) AS lv FROM Folder 
JOIN
(SELECT @pv:=5) tmp
) a;

Explanation

The query utilizes the FIND_IN_SET() function to check whether the idFolderParent of the current row exists in the value of the user-defined variable @pv. If it does, the value is appended to the lv variable. The subquery generates multiple rows, each representing a level of the recursive search.

By using the GROUP_CONCAT() function with the SEPARATOR clause, the results from each row are combined and separated by the specified delimiter (in this case, a comma). The final result is a string containing the concatenated child folders.

This solution provides a robust method for retrieving child and sub-child folders recursively, even when the order of parents in the database may not be strictly hierarchical.

The above is the detailed content of How to Implement Recursive Folder Retrieval in MySQL?. For more information, please follow other related articles on the PHP Chinese website!