How to Recursively Retrieve All Descendants in a MySQL Tree Structure?

Recursing a Tree Structure in MySQL

Managing hierarchical data in a database can be a challenge. One common approach involves using a parent-child relationship, as demonstrated in the question provided. While querying for immediate descendants is straightforward, retrieving all descendants can be more complex.

The MySQL documentation suggests several methods for handling hierarchical data, including the following:

• Path Enumeration: This method involves storing the path from each node to the root of the tree in a database column. Queries can then use this column for efficient descendant retrieval.
• Adjacent List: This approach stores the parent-child relationships in a single table, with each row representing a node and its direct parent. While simple to implement, it requires multiple queries to retrieve all descendants.
• Nested Sets: This more advanced method uses a pair of columns to store the left and right positions of each node within the tree. It offers efficient descendant retrieval but requires more complex table updates.

For the given example, using the Path Enumeration method, the following query would retrieve all descendants of a parent location:

WITH RECURSIVE descendant_path AS (
  SELECT id, path
  FROM locations
  WHERE id IN (SELECT location_id FROM location_parent WHERE parent_id = '$locationid')
  UNION ALL
  SELECT l.id, CONCAT(dp.path, ',', l.id)
  FROM locations l
  JOIN descendant_path dp ON l.path LIKE CONCAT(dp.path, '%')
)
SELECT id FROM descendant_path;

This query uses a recursive CTE (Common Table Expression) to iterate through the tree structure and build a path for each descendant location. By specifying the starting location in the initial query, all descendants can be retrieved in a single pass.

The above is the detailed content of How to Recursively Retrieve All Descendants in a MySQL Tree Structure?. For more information, please follow other related articles on the PHP Chinese website!