How Can I Find All Ancestors of a Node in a MySQL Hierarchical Table Using a Single Query?

Finding all parents in a MySQL table with a single query using a Recursive CTE

In a database with a hierarchical structure, identifying the parents of a specific record can be a complex task. In MySQL, this challenge can be addressed efficiently using a Recursive Common Table Expression (CTE). Let's explore the problem statement and its solution.

Problem Statement:

Given a MySQL table with a hierarchical structure (such as the one in the provided schema), where each row represents a node with an ID, title, parent ID, and other relevant information, the task is to retrieve all the ancestors (parents) of a particular node in the hierarchy with a single query.

Solution:

The provided solution leverages a Recursive CTE to traverse the hierarchy and identify the parents of a node specified by its ID. The following query demonstrates the approach:

SELECT T2.id, T2.title, T2.controller, T2.method, T2.url
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent_id FROM menu WHERE id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 31, @l := 0) vars,
        menu m
    WHERE @r  0) T1
JOIN menu T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC;

Query Explanation:

• The subquery initializes two user-defined variables, @r and @l, and selects the specified record with ID 31 (assuming it's the target node). These variables are used to track the current node ID (@r) and the level (@l) during recursive iteration.
• The recursive part of the CTE repeatedly fetches the parent ID of the current node and increments the level. This process continues until the current node has no parent (i.e., @r is 0).
• The outer query joins the recursive CTE with the main table (menu) based on the _id (record ID) to retrieve the information for each ancestor.
• The ORDER BY clause sorts the results in descending order of the level to display the ancestors in a hierarchical order.

This query efficiently retrieves all the parents of the specified node with a single SQL statement, providing a convenient and performant solution for navigating hierarchical data in MySQL.

The above is the detailed content of How Can I Find All Ancestors of a Node in a MySQL Hierarchical Table Using a Single Query?. For more information, please follow other related articles on the PHP Chinese website!