How can I retrieve all descendant folders in a nested hierarchy using recursive queries in MySQL?

Recursive Query for Nested Folders

Obtaining all child and subchild folders with multiple levels of nesting presents a challenge for MySQL queries. While there are various approaches, this article will focus on an effective and comprehensive solution.

To retrieve all descendant folders from a given parent, we can utilize recursive queries, which allow for nested calculations within a single statement. This method involves an iterative process, where each folder's ID is used as the parameter for the next iteration, expanding the search depth with each cycle.

Consider this scenario: given a database table named Folder with columns idFolder (auto-incremented primary key), FolderName, and idFolderParent (foreign key to Folder.idFolder), we populate it with the following data:

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

This structure allows us to create nested folder hierarchies. For example, if we query for all child folders of ADoc (idFolder=1), we expect to retrieve not only its immediate children (ADoc1, ADoc2) but also all descendant folders beneath them (ADoc3, ADoc4, ADoc5, ADoc6, ADoc7).

The following SQL query achieves this using recursive iteration:

<code class="sql">WITH RECURSIVE FolderHierarchy AS (
  SELECT idFolder, FolderName, idFolderParent
  FROM Folder
  WHERE idFolder = 1
  UNION ALL
  SELECT f.idFolder, f.FolderName, f.idFolderParent
  FROM FolderHierarchy AS h
  JOIN Folder AS f
    ON h.idFolderParent = f.idFolderParent
)
SELECT idFolder, FolderName
FROM FolderHierarchy;</code>

This query employs a common table expression (CTE) named FolderHierarchy that initially selects the root folder (ADoc) and then recursively joins child folders to the hierarchy. It continues this process until no more child folders remain, effectively collecting all descendants. The final result provides a nested list of IDs and folder names, allowing for the retrieval of all child and subchild folders with any level of depth.

The above is the detailed content of How can I retrieve all descendant folders in a nested hierarchy using recursive queries in MySQL?. For more information, please follow other related articles on the PHP Chinese website!