How to Retrieve All Subdirectories of a Specific Directory in MySQL with a Single Query?

Retrieving Subdirectories in a Single Query with MySQL Recursion

Consider a table resembling a directory structure:

folders_table
-----------------------
- INT id_folder
- INT id_folder_parent
- VARCHAR folder_name

The challenge lies in retrieving all subdirectories of a specific directory using a single SELECT query.

Solution:

Traditional database structures cannot support this query efficiently. To enable it, the database structure must be modified. Specifically, a self-referencing relationship is introduced to represent the hierarchy.

Step-by-Step Instructions:

1. Add a new column, left_index, to track the order of items at the same level.
2. Add a new column, right_index, to track the order of items in the entire hierarchy.
3. Create a new table with these columns: id_folder, id_folder_parent, left_index, right_index, folder_name.

Once the modifications are complete, the following query can be used to retrieve a tree of any depth in one query:

SELECT *
FROM folders_table
WHERE left_index > (SELECT left_index FROM folders_table WHERE id_folder = <parent_folder_id>)
AND right_index < (SELECT right_index FROM folders_table WHERE id_folder = <parent_folder_id>)
ORDER BY left_index

Resources for Further Exploration:

• [Storing Hierarchical Data in a Database (SitePoint)](https://www.sitepoint.com/hierarchical-data-database/)
• [Managing Hierarchical Data in MySQL](https://www.qrypt.co.uk/blog/managing-hierarchical-data-in-mysql)

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