How to Recursively Query a Tree Structure in MySQL to Find Descendants of a Parent Node?

Recursing a Tree Structure in MySQL

Managing hierarchical data can be a challenge in relational databases, especially when recursive querying is required. In MySQL, we have a specific problem: how to efficiently retrieve all descendants of a given parent location, no matter the depth of the hierarchy.

The Problem:

Consider the following database schema for locations:

location (id, ....)
location_parent (location_id, parent_id)

The location table stores location information, while the location_parent table defines the parent-child relationship between locations.

Suppose we have a location with ID 5. We want to retrieve all its descendants, including those that are multiple levels deep.

Initial Solution:

One approach to recursive querying is to use a self-join as follows:

<code class="sql">SELECT DISTINCT l.id
FROM location AS l
LEFT JOIN location_parent AS lp ON l.id = lp.child_id
WHERE lp.parent_id = 5
;</code>

This query will retrieve the direct children of location 5. However, for deeper descendants, we need to repeat the query multiple times, each time using the results of the previous query as the input for the next. This method is inefficient and impractical for large hierarchical structures.

Recommended Solution:

The recommended solution to this problem is to use the recursive Common Table Expression (CTE). A CTE is a temporary table that can be defined and referenced within a single query.

<code class="sql">WITH RECURSIVE descendants AS (
    SELECT id, parent_id
    FROM location_parent
    WHERE parent_id = 5
    UNION ALL
    SELECT lp.child_id, lp.parent_id
    FROM descendants AS d
    JOIN location_parent AS lp ON d.id = lp.parent_id
)
SELECT id
FROM descendants
;</code>

This query creates a recursive CTE called descendants, which starts with the direct children of location 5. The UNION ALL clause then adds the children of the previously selected descendants, effectively expanding the query to include all descendants at any depth.

The second part of the query selects the id column from the descendants CTE, which gives us the list of all descendant locations. This approach is much more efficient than the self-join method and can handle hierarchical structures of any depth.

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