How to Find All Ancestors of a Record in a MySQL Table Using a Single Recursive Query?

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

Consider the following MySQL table schema with sample data:

| ID |             TITLE | CONTROLLER |            METHOD | PARENT_ID |
|----|-------------------|------------|-------------------|-----------|
|  1 |         Dashboard |      admin |         dashboard |         0 |
|  2 |           Content |      admin |           content |         0 |
|  3 |           Modules |      admin |           modules |         0 |
...

Challenge:

Our goal is to find all parents of a specific record, namely the one with title = 'Categories', using a single SQL query.

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       

Solution:

We employ a recursive common table expression (CTE) to traverse the table hierarchy and identify all ancestors of the desired record:

WITH RECURSIVE Parents AS (
    SELECT id, parent_id
    FROM menu
    WHERE id = 31
    UNION ALL
    SELECT m.id, m.parent_id
    FROM Parents AS p
    JOIN menu AS m ON p.parent_id = m.id
)
SELECT m.id, m.title, m.controller, m.method, m.url, m.parent_id
FROM Parents AS p
JOIN menu AS m ON p.id = m.id
ORDER BY p.id DESC;

Explanation:

• The CTE Parents is initialized with the id of the target record (31).
• The recursive part of the query selects all parents of the current record, continuing the traversal.
• The ORDER BY clause sorts the results in descending order, with the immediate parent appearing first.

By executing this query, we obtain the desired output, listing all parents of the Categories record.

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