How Can I Find All Parent Nodes of a Specific Entry in a MySQL Hierarchy Using a Single Query?

Finding All Parents in a MySQL Table with a Single Query (Recursive Query)

In database management, retrieving hierarchical data can often require complex queries involving recursive algorithms. One such scenario arises when we need to extract all parent nodes of a given entry in a hierarchical table like the one presented above.

Challenge:

Consider the provided table with a hierarchy of menu items, where each node has an ID, title, and parent ID. Our goal is to fetch all parent nodes of the entry with the title "Categories" using a single MySQL query.

Solution:

We can use a recursive query to traverse the table and accumulate the parent nodes at each level. The following query accomplishes this:

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;

Explanation:

• The subquery initializes two user variables @r (id of the current node) and @l (level of the current node) with initial values 31 (id of "Categories") and 0, respectively.
• The outer query iterates through the table, updating @r with the parent ID of the current node and incrementing @l to keep track of the current level.
• The result of the outer query (T1) is joined with the original table (T2) based on the common column _id.
• Finally, the result is sorted in descending order of lvl to present the parent nodes in hierarchical order.

Output:

Running the query will produce the desired output:

id	title	controller	method	url	parent_id
3	Modules	admin	modules	(NULL)	0
17	User Modules	modules	user_module	(NULL)	3
31	Categories	categories	category	(NULL)	17

The above is the detailed content of How Can I Find All Parent Nodes of a Specific Entry in a MySQL Hierarchy Using a Single Query?. For more information, please follow other related articles on the PHP Chinese website!