How to Efficiently Retrieve All Subdirectories of a Specific Directory in MySQL Using Recursive Queries?

Recursive Queries for Nested Folder Structures in MySQL

Consider a scenario with a table storing a hierarchical folder structure, where each folder has an ID, parent folder ID, and a name. The table is designed as follows:

folders_table
-----------------------
id_folder
id_folder_parent
folder_name

The challenge is to retrieve all subdirectories of a specific directory using a single SELECT query.

Solution: Revising the Database Structure

One approach involves modifying the database structure to facilitate recursive queries. Instead of storing indirect relationships via the id_folder_parent column, consider using the following structure:

folders_table
-----------------------
id_folder
folder_path
folder_name

In this updated structure, the folder_path column stores the complete path of each folder in the hierarchy, starting from the root directory. This allows for efficient traversal through the folder structure using recursion.

Recursive Query

Once the database structure is modified, the following recursive query can be used to retrieve all subdirectories of a specific directory:

WITH RECURSIVE FolderTraversal AS (
    SELECT
        id_folder,
        folder_path,
        folder_name
    FROM
        folders_table
    WHERE
        id_folder = <directory_id>
    UNION ALL
    SELECT
        t.id_folder,
        t.folder_path,
        t.folder_name
    FROM
        folders_table AS t
    JOIN
        FolderTraversal AS p ON t.folder_path LIKE CONCAT(p.folder_path, '%/')
)
SELECT
    id_folder,
    folder_path,
    folder_name
FROM
    FolderTraversal;

In the above query, the folder_path column is used to define the recursion. It iteratively appends the current folder path to the next folder's path, connecting the tree branches. By starting the query at the specified directory_id, it recursively traverses the entire subtree.

The above is the detailed content of How to Efficiently Retrieve All Subdirectories of a Specific Directory in MySQL Using Recursive Queries?. For more information, please follow other related articles on the PHP Chinese website!